Help with Table Structure

[medtech2]

I'm trying to set up a database that lists laboratory test results. One particular test we perform detects almost 200 different analytes. A patient can have any particular number of these analytes present in his/her blood at one time, and our patients are tested repeatedly over time. I have a couple related tables: one with all of my patient identifiers, another one with specimen specific info, and a third one specific to this test, with fields that include the PK of Screen_ID, Sample_ID (relates back to the specimen table), and Test_Date (each sample can be tested multiple times). I'll eventually need to be able to run a query to see which patients had a particular analyte present (or a query to look for a bunch of analytes at once). So what is the best way to structure my database to include the info for this test? Do I set each analyte as a separate field within the third table, with the option of choosing "yes" or "no", it is present? Or does this need to be set up a fourth table somehow?
Thanks in advance!

[orange]

Here is a document on database design.
http://forums.aspfree.com/attachment...achmentid=4712

[TheShabz]

I would create a table, tblAnalytes, that hold all the analytes and an identifier (PK) for them. So 200 records of 2 fields. Then I would create a table, tblTestAnalyte, that facilitates a many-to-many relationship between the test table and tblAnalytes. The test table holds data unique to the test. the tblTestanalyte has a record for each analyte present in the test. If Analyte A, C, and D are present in test 1246, your entry in tblTestAnalyte would look like:
TestAnalyteID, TestID, AnalyteID
1, 1246, A
2, 1246, C
3, 1246, D

You can then query this table to find how many analytes are found per test by,
SELECT TestID, Count(AnalyteID) AS CountOfAnalytes
FROM tblTestAnalyte
GROUP BY TestID

you can also have a WHERE clause that links to a form that will give you the count for just a single test.

[June7]

A major concept you should glean from Shabz suggestion is that the multiple yes/no fields is not a good idea.

Here is another reference to supplement orange's http://forums.aspfree.com/microsoft-...es-208217.html

[medtech2]

Thanks for your responses. I'm not sure they've given me what I needed though (but I'm somewhat new to Access so maybe my solution is very simple and I just am overthinking it). If this test only checked for a handful of things, I would just put them in a list box under the Screen_ID table, but that seems inappropriate when there are 200 possibilities. That is the part that is confusing me. But I need to build it so that people can just check off what is present (it would be a disaster if people had to type the results in for every sample).

[June7]

I see two options for entering the relevent Analytes.

1. A Continuous view form bound to tblTestAnalyte (possibly a subform, synchronized to main form bound to tblTests) with a combobox to select an Analyte for each record for as many records needed for each test.

2. An unbound form with 200 checkboxes, one for each analyte. VBA code would save record for each checked box. Forms have a limit on how many controls they can have. I have some rather densely populated forms and reports, so the limit is high, but I have hit it. My workaround was to use subform/subreport.

Tables and queries have a limit of 255 fields. This is only one reason multiple similar fields (like the yes/no's) is impractical. Another is the difficulty of doing the type of queries you desire.