I hope you guys can help me out, as 'Step by step Access' and the internet didnt help me out...

I'm about to create a database for monitoring assessment. Several patients will be assessed many a time. The main table will be one with a patientnumer and patient characteristics. For each period in time (T) I'd like to create tables which contain the different tests and dates on which they are administered and processed.

The problem I'm having though, is that the tables T don't have unique values and they need to be related to the patientnumbers.
Should I use autonumbering as primary keys in the tables T to relate to the patientnumber? I'd rather have the patientnumbers automatically appear in the tables T, or at least in the forms to be used by the final users of this database.

I hope this description makes some sense (English is not my primary language)

Can anyone help me out?

I think you have a many to many relationship. You have many patients and many kinds of test. You will need a table for patients, a table for tests and a junction table. Patients and Tests will have their own primary keys. Each record in the junction table will hold the primary key of the patient, the primary key of the test and any unique information that relates to this junction, eg the date of the test.

Do a search in here on many to many and junction tables.

If this is a clinic situation and I am guessing that it is, you have a slightly more complex problem than neileg stated. You also have the chance that a test could be repeated, I'll bet. So your junction table ALSO has a date on it to show that test 21 was done on patient 15123 on thus and such a date, and the same test was done on the same patient two years later. When you make the junction table, its prime key will include the patient number, test number, AND DATE.

But it doesn't invalidate what neil suggested. It just embellishes it.

Hi,
I work in the health service and we have that senario but I've never needed a junction table. A patient may have None, one or more tests.
Only two tables are required.

PatientID must be unique and could be an autonumber. use this as the Foreign Key in you tests table.

Hi,
I work in the health service and we have that senario but I've never needed a junction table. A patient may have None, one or more tests.
Only two tables are required.

PatientID must be unique and could be an autonumber. use this as the Foreign Key in you tests table.
I believe the test table, as suggested above, is not so much as the physical test but the type of test. Thus, when you use the patient and a type of test and date, you need the junction. Otherwise you are duplicating the type of test over and over again in the test table (where it may be a text description) when a long integer key is preferred for space considerations.

Hi,
I work in the health service and we have that senario but I've never needed a junction table. A patient may have None, one or more tests.
Only two tables are required.

PatientID must be unique and could be an autonumber. use this as the Foreign Key in you tests table.
I believe the test table, as suggested above, is not so much as the physical test but the type of test. Thus, when you use the patient and a type of test and date, you need the junction. Otherwise you are duplicating the type of test over and over again in the test table (where it may be a text description) when a long integer key is preferred for space considerations.
I think it depends on the nature of the tests. If you are dealing with a range of standard tests, then having a table of predefined tests and a junction table will work nicely.

If the tests are not standardised and are not capable of predefinition, then I agree that there is no need for a junction table because there is no repetition.

Hi Sj,
You must be referring to a lookup table.

eg
tblTest
---------------
TestID - Primary Key
Test - Test Description.

tblPatientTest
------------
PatientID
TestID
DateOFTest

is this what you are getting at

Hi Sj,
You must be referring to a lookup table.

eg
tblTest
---------------
TestID - Primary Key
Test - Test Description.

tblPatientTest
------------
PatientID
TestID
DateOFTest

is this what you are getting at
Yes, but referring to tblTest as a lookup suggests it only contains a list of IDs and descriptions. This table may contain a lot more data than that.

Hi, thanks so much for helping me!!

I'll try to explain the situation somewhat better:
Let's say we've got 10 patients. We allready decided that all of them have to fill in test A, B and C tomorrow. Next year, they'll all have to fill in A, B and D. The year after it'll be A, C and D.

I was thinking of making a basic table with patient-characteristics (patientnumber being primary key) and for each year additional tables with all the tests (date administered and by who).
Does this make it more clear?

I wouldn't make an additonal table for each year. Instead make one table and add year as a atribute/field.