Data Entry into a 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.

[June7]

Sounds like you need a form/subform arrangement. Do you want to provide the db for analysis? Don't need real data.

[norcalpsych]

Would appreciate the help. How do I attach the db?

[June7]

The Attachment Manager is below the Advanced post editor. Up to 2mb zip file allowed, other files limited to 500kb.

[norcalpsych]

Sounds like you need a form/subform arrangement. Do you want to provide the db for analysis? Don't need real data.

Attempted upload but it failed.

[June7]

What happened? Did you use Windows Compression to zip?

Or upload to fileshare site such as Box.com and post url link to the file.

[norcalpsych]

Would appreciate the help. How do I attach the db?

Let's try this; done with Winzip.

[June7]

There are no tables named 'Test' or 'Test Sequence'. There is 'Available Tests' and 'Assessment Sequence'. Are those the relevent tables? And the form is 'TRF 1 to 5 yrs Results Entry'? I see you already have the form/subforms set up. But you want the TRF 1 to 5 subform linked to the Assessment Sequence subform. Review this article http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

The default form 'Switchboard' for project start doesn't exist.

What is purpose of the Gender List table? There is a Gender field in Demographics table.

[norcalpsych]

Sorry, you're right. In my original post I was trying to keep it simple. Your translation of the actual tables is correct. The 2 "* Results Entry" forms (including subforms) are my test forms to try to figure out how to make this work. I'll take a look at the url you provided.

I have read mixed reviews about using a switchboard so had it then deleted it. I'm not sure it will serve my purpose here (Demographic data entry and Individual test result data entry). Thoughts?

Gender list was an original idea and can probably be deleted, After investing the time to creat the tables, I have chosen to delete nothing until the db is functional to our purposes.

Thanks for your help, what next?

[June7]

A switchboard is just a main menu form, a 'jumping off' point to various modules of project. I never use the switchboard wizard. I use only VBA, no macros, so I build my own 'main menu'.

Let me know how the subform linking works out.

[norcalpsych]

I am just back in the office today (Tuesday) and will take a look at the url you sent and see if that helps. I'll get back to you either way.

Sorry, you're right. In my original post I was trying to keep it simple. Your translation of the actual tables is correct. The 2 "* Results Entry" forms (including subforms) are my test forms to try to figure out how to make this work. I'll take a look at the url you provided.

I have read mixed reviews about using a switchboard so had it then deleted it. I'm not sure it will serve my purpose here (Demographic data entry and Individual test result data entry). Thoughts?

Gender list was an original idea and can probably be deleted, After investing the time to creat the tables, I have chosen to delete nothing until the db is functional to our purposes.

Thanks for your help, what next?

[norcalpsych]

I appreciate the url and the sample fom however, I am unable to perform this task. On the second page of the url document it an unable to determine how to get to the "Property Sheet" that is shown in the screenshot so was unable to proceed. I'll need a bit more basic help please.

Also, you mentioned about normalizing my tables, how do I do that?

Thanks again.

Sorry, you're right. In my original post I was trying to keep it simple. Your translation of the actual tables is correct. The 2 "* Results Entry" forms (including subforms) are my test forms to try to figure out how to make this work. I'll take a look at the url you provided.

I have read mixed reviews about using a switchboard so had it then deleted it. I'm not sure it will serve my purpose here (Demographic data entry and Individual test result data entry). Thoughts?

Gender list was an original idea and can probably be deleted, After investing the time to creat the tables, I have chosen to delete nothing until the db is functional to our purposes.

Thanks for your help, what next?

[June7]

How have you been able to design anything without the Properties sheet?

Open any object (table, query, form, report) in design view. Click anywhere in the object, then right click and select 'Properties'. That is one way to open the Properties sheet. Should also be a menu item, just don't remember where for A2003.

Where did I mention normalization? Google the topic, it's everywhere. Here is a good tutorial on database principles http://forums.aspfree.com/microsoft-...es-208217.html

[norcalpsych]

No, I have used the properties selection however, it looks nothing like the image on the url you sent and the only option is to hide it. Basically, the instruction on the url don't fit the options available in properties.

Other thoughts?

I appreciate the url and the sample fom however, I am unable to perform this task. On the second page of the url document it an unable to determine how to get to the "Property Sheet" that is shown in the screenshot so was unable to proceed. I'll need a bit more basic help please.

Also, you mentioned about normalizing my tables, how do I do that?

Thanks again.

[June7]

Are you talking about the article about linking two subforms and the middle image called 'Property Sheet'? If I remember right, the Property Sheet and building form/subform is the same in A2003. I don't understand your confusion. Everything in the article makes sense to me and shows technique I would use if I had to link two subforms (never have). It even offers a sample db for download. And what 'second' page?