Table structure

[Dave]

I am preparing a database that will include tracking of patient
antibodies. There are six classes of antibodies:A, B, C, DR, DQ and
DW. In each class there are about 20 specificities. For each patient
record I want to include both the specificity and its strength and be
able to sort on each parameter. I need to know the most efficient
table structure as with 100's of patients and each patient with a
dozen antibodies or more, this table could get large and unwieldy.
I'm thinking of having a Lookup table for each antibody class and a
table for the antibodies in the patient record. Am I on the right
track here?

Thanks everyone,

Dave B

 

[Guest]

Dave:

I'd have thought you'd want tables Patients, Antibodies and another,
PatientAntibodies say, modelling the relationship between them, with columns
PatientID and AntibodyID. The PatientAntibodies table would also have
columns for the strength and specificity.

If you are recording each antibody only once for each patient, then
PatientID and AntibodyID would be the key of the PatientAntibodies table. If
you are recording the same antibody per patient at different dates, however,
then you'd need a column for the date, TestDate say, and this would also be
part of the key. The patients current data would be in the row with the
latest TestDate for that patient.

If the strengths and specificity values are fixed set per antibody then you
could also have two tables for these, each including an AntibodyID foreign
key so that when an antibody is selected for a patient in a form the value
for strength and specificity values could then be selected from combo boxes,
each listing only the values relevant to the selected antibody.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I am preparing a database that will include tracking of patient
antibodies. There are six classes of antibodies:A, B, C, DR, DQ and
DW. In each class there are about 20 specificities. For each patient
record I want to include both the specificity and its strength and be
able to sort on each parameter. I need to know the most efficient
table structure as with 100's of patients and each patient with a
dozen antibodies or more, this table could get large and unwieldy.
I'm thinking of having a Lookup table for each antibody class and a
table for the antibodies in the patient record. Am I on the right
track here?

nope... way off track and careening down the hillside I fear!

You can do this with THREE tables:

Patients
PatientID
LastName
FirstName
<other biographical data>

Antibodies
Class <Text>
Specificity <text>

PatientAntibodies
Class <Text> <link to Antibodies>
Specificity <link to Antibodies>
Strength <number, I'm guessing>

The Antibodies table would have some 120 records, one for each
combination of class and specificity.

You'ld use a Form based on Patients to enter data (you should
certainly NOT use table datasheets for anything but debugging). On
this Form you'ld have a Subform based on the PatientAntibodies table;
the subform would have two combo boxes, one for the class and one for
the specificity; and a textbox for the strength (or maybe a combo if
there are just a few selectable strength values).

The Specificity combo box can be made dependent on the Class combo box
so only those specificities relevant to that class are available. If
your Form is named frmPatients, with a subform subAntibodies, and a
combo box named cboClass, you would create a Query

SELECT Antibodies.Specificity
FROM Antibodies
WHERE Class = Forms!frmPatients!subAntibodies.Form!cboClass
ORDER BY Specificity;

and use it as the row source for cboSpecificity. You'll need to put an
Event Procedure in the AfterUpdate event of cboClass:

Private Sub cboClass_AfterUpdate()
Me.cboSpecificity.Requery
End Sub

John W. Vinson [MVP]

 

[Dave]

nope... way off track and careening down the hillside I fear!

You can do this with THREE tables:

Patients
PatientID
LastName
FirstName
<other biographical data>

Antibodies
Class <Text>
Specificity <text>

PatientAntibodies
Class <Text> <link to Antibodies>
Specificity <link to Antibodies>
Strength <number, I'm guessing>

The Antibodies table would have some 120 records, one for each
combination of class and specificity.

You'ld use a Form based on Patients to enter data (you should
certainly NOT use table datasheets for anything but debugging). On
this Form you'ld have a Subform based on the PatientAntibodies table;
the subform would have two combo boxes, one for the class and one for
the specificity; and a textbox for the strength (or maybe a combo if
there are just a few selectable strength values).

The Specificity combo box can be made dependent on the Class combo box
so only those specificities relevant to that class are available. If
your Form is named frmPatients, with a subform subAntibodies, and a
combo box named cboClass, you would create a Query

SELECT Antibodies.Specificity
FROM Antibodies
WHERE Class = Forms!frmPatients!subAntibodies.Form!cboClass
ORDER BY Specificity;

and use it as the row source for cboSpecificity. You'll need to put an
Event Procedure in the AfterUpdate event of cboClass:

Private Sub cboClass_AfterUpdate()
Me.cboSpecificity.Requery
End Sub

John W. Vinson [MVP]

Thank you John. You've been a great help. I only have one question on
this then.For PatientAntibodies table when you say <link to
Antibodies> do you mean as a Lookup or Relationship?

Thanks,

Dave B

 

[John W. Vinson]

Thank you John. You've been a great help. I only have one question on
this then.For PatientAntibodies table when you say <link to
Antibodies> do you mean as a Lookup or Relationship?

I'd avoid Lookup fields altogether:

http://www.mvps.com/access/lookupfields.htm

They don't get you anything that a Relationship doesn't get you
(except one or two mouseclicks saving on adding a combo box to a
form).

John W. Vinson [MVP]

 

[Dave]

I'd avoid Lookup fields altogether:

http://www.mvps.com/access/lookupfields.htm

They don't get you anything that a Relationship doesn't get you
(except one or two mouseclicks saving on adding a combo box to a
form).

John W. Vinson [MVP]

But when I join the fields, I get an indeterminant join. Should
certain fields be primary keys?

Dave B

 

[John W. Vinson]

But when I join the fields, I get an indeterminant join. Should
certain fields be primary keys?

Typically you would join the Primary Key in the "one" side table (the
lookup table, if you're using it on a form to look up a value) to a
foreign key of matching datatype.

If your lookup table has no primary key - IT SHOULD.

John W. Vinson [MVP]