tables and relationships

[Victoria]

I am having problems with my tables and its relationships.
My database consist of Patients (and their general
details such as demographics, medical hx, etc) and their
admissions, diagnosis, tests done, treatment and follow
up visits.
The problem I have is for example with the tests table as
each investigation has different values and fields but
all of them have a result (normal, abnormal, a date and
then days from onset of sx.

 

[John Vinson]

I am having problems with my tables and its relationships.
My database consist of Patients (and their general
details such as demographics, medical hx, etc) and their
admissions, diagnosis, tests done, treatment and follow
up visits.
The problem I have is for example with the tests table as
each investigation has different values and fields but
all of them have a result (normal, abnormal, a date and
then days from onset of sx.

and...? I don't understand what you're asking.

Please describe your table structures and relationships; and explain
what some of your medical jargon terms (hx, sx) mean.

 

[TC]

I am having problems with my tables and its relationships.
My database consist of Patients (and their general
details such as demographics, medical hx, etc) and their
admissions, diagnosis, tests done, treatment and follow
up visits.
The problem I have is for example with the tests table as
each investigation has different values and fields but
all of them have a result (normal, abnormal, a date and
then days from onset of sx.

Well, since haven't told us much, let me make some guesses.

"tests" are something like:
- "tissue examination", with a long descriptive text result;
- "20-chanel blood analysis", with 20 seperate numeric results,
etc.

- a patient can have many tests;
- a test can have many results;
- a result might be text, a number, or whatever.

I would also assume that a patient might have the same test performed more
than once, on the same day. So probably, no combination of patient and test
details will serve as the primary key of the Tests table.

So:

tblPatient
PatientID (PK) (eg. autonumber)
name, address, social security #, etc.

tblTest
PatientID ( composite )
TestNumber ( PK )
date, requesting physician,
reason for requesting this test, etc.

tblResult
PatientID ( composite )
TestNumber ( P )
ResultNumber ( K )
memo field for a text result
numeric field for a number result
boolean field for a yes/no result
etc.

That is *one, very simple example* of how it might work. But I know from
previous experience, that patient result systems can be quite difficult to
design efficiently. There is no way that anyone can give you more than a few
rough ideas, from the tiny amount of information that you provided in your
question.

HTH,
TC