Check boxes?

[Guest]

I am designing a databse with several tables regarding patient information.
One table lists the tests patients have recieved, and I need to count how
many tests each patient has had, as well as how many of the tests are
positve. Right now, I have a table containing each test as a field, with
check boxes. How can I count the total tests per patient, and count the
total positive tests (I assume I will need a second table for results).

 

[tina]

Right now, I have a table containing each test as a field, with

check boxes.

the above design violates data normalization rules. one patient may have
many tests, and one test may be performed on many patients; this is a
many-to-many relationship. in Access, you model such a relationship be
creating a third table to act as a "linking" or "join" table that connects
the first two. correct design would be

tblPatients
PatientID (primary key)
(other fields that describe a patient, such as first and last names, gender,
date of birth, etc)

tblTests
TestID
TestName

tblPatientTests
PatTestID (primary key)
PatientID (foreign key from tblPatients)
TestID (foreign key from tblTests)
TestDate
TestResult (test results may need to be stored in a "child" table of this
table, depending on the complexity of the results data that must be stored)

relationships are
tblPatients.PatientID 1:n tblPatientTests.PatientID
tblTests.TestID 1:n tblPatientTests.TestID

by having one record for each test performed on each patient (rather than
one record for *all* tests performed on each patient), it's easy to write a
Totals query to count the number of tests given to each patient - or to
count the number of patients who take each test. also, if the TestResult
field is a simple "positive or negative" value, it will be easy to break
down the above counts by those values.

before you go any further in your database design, recommend you read up on
data normalization principles and table relationships. you need to
understand *why* the above design is correct, rather than just implementing
it blindly on somebody else's say so. see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth