Data Entry in Form with 3 Tables

[norcalpsych]

I am a novice to Access; I am presently using Access 2003 with Windows XP (I expect an upgrade to Access 2010 and Windows 7 by the end of April, if that matters).

As part of my doctoral program I am trying to create a form with multiple tables in order to input the test results for various individuals at 1-5 time points from 1-12 tests (each test producing results in a number of areas). My master table (‘Demographics’) holds the primary key as “ID” that is related to a table named ‘Test Sequence’ (holding a primary key “SequenceID” and the foreign key “ID”) that is then related to each of the individual tests each have “ID” and “SequenceID” as foreign keys.

The idea is that a single individual could have each test (with its multiple resulting values) done over a period of time but no more than 5 distinct times. Therefore, Mr. Smith could come in for Test Retake 2 to complete Test F which outputs 12 values. So, Mr. Smith could have Test Retake 1 thru 5 and Test 1 thru 12, if my math is right he could have possible results posted in 60 different combinations and each of those combinations would need to hold the actual 12 test result values.

I have managed to create the form (using the form wizard) that included the 3 tables listed above (Demographics, Test Sequence, Test) but I am stuck with data entry to hold the data to the individual and then to the individual over the various retest time periods with the variety of tests.

Any help would be very much appreciated.

 

[Mimadocken]

I'm NOT one of the experts that hang out here, but one of the fellow users. However, I think I can help you get started in the right direction.

You named three Tables (Demographics, Test Sequence, and Test). You didn't give a primary key for Test. You may wish to add an autonumber TestID field as your primary for that one. I think you may currently use the two foreign keys, but having a unique ID# is a smart move for all these tables.

You will probably need to build a query (or maybe even a series of queries) on which to base your form. It can't be based on multiple tables, but can be based on a query that can combine the information from the 3 forms.

Hope this helps you start in the right direction. I'm sure someone really smart will come along soon. I've done several Access databases, and have experience needing to build queries before forms.