Question designing a form: Sums, queries, formats and updates

Sorry for the bunch of questions here, and I dont know if this is the right forum to post it, but as I am designing all this in a form, I figured this would be the place. I am a total newbie to Access, so I appologize if these questions seem simple, but I am unsure how to do this.

I am designing a form. It is populating one table:

Table1_FirstName
Table1_LastName
Table1_Item1
...
Table1_Item30
Table1_Number_Correct

Table2_Item_Number
Table2_Item_Name
Table2_Correct_Answer


--------------
What I am trying to do is design a form where the user enters the names for Table 1, and the 30 items. When the items are entered in, they will be checked acinst the correct answer in Table2, and I want the letter to be changed to displayed as RED (I think this will take a query and a formatting change. I am unsure how to do this.)

Also, and more importantly:

When the user is done entering the whole record (a total of 32 fields), I want to check the total number of "correct" answers that were entered, and store this in Table1_Number_Correct.

In psuedocode, I am guessing this would be something like:

For x = 1 to 30
If Table1_Item_x = Table2_Item_Number(x).Correct_Answer
Then ++Number_Correct

Table1_Number_Correct = Number_Correct


Would this be done in VB script (which I am unfamiliar with)?

Has anyone tried this before? Any examples?

Any help with these two problems would be greatly appreciated.

Thanks!

Before you go any further, you will need to change your table structure. You have a 1-to-many relationship between people and Items. Therefore you need to create a separate table to hold the item information.

Table1_PersonID (autonumber, primary key)
Table1_FirstName
Table1_LastName
...

Table2_Item_Number (Long Integer, primary key)
Table2_Item_Name
Table2_Correct_Answer

Table3_PersonID (Long Integer, primary key field1)
Table3_ItemNumber (Long Integer, primary key field2)
Table3_Answer

This is a very simplistic structure and will only work for a single test or whatever it is you are trying to capture. With some changes it could be expanded. But this will get you going in the right direction.

To enter the answers, you'll need a main form for the Person information and a subform for the answers. The two are linked on PersonID. You will need to create a query to use as the recordsource of the subform.

Select Table3.PersonID, Table2.ItemNumber, Table2.Correct_Answer, Table3.Answer
From Table2 Right Join Table3 on Table2.ItemNumber = Table3.ItemNumber
Order by Table2.ItemNumber;

Once you have restructured, a simple query will give you your answer. NO coding will be required.

Select Table3.PersonID, Table2.ItemNumber, Table2.Correct_Answer, Table3.Answer, Sum(IIf(Table2.Correct_Answer = Table3.Answer, 1, 0)) as SumOfCorrect
From Table2 Right Join Table3 on Table2.ItemNumber = Table3.ItemNumber
Group by TTable3.PersonID, Table2.ItemNumber;

If you don't restructure your table, you'll need to learn VBA (not VB Script) in a hurry.

Another question...

Pat -

Thank you so much....Thats a huge help!!! Sorry I didnt get back to you sooner, but I was busy and just now got to sit down to look at this stuff.

The problem I am having now is that when I impliment this, the subform doesn't populate will all the items from Table2. It has all the FIELDS, but I cant even specify an item # to input data for.

However, if the data is already entered, it shows up correctly.

Would you know what Im doing wrong? Should I post the db that I have so far so you can take a gander at it?


Once again, thank you so much for all your help. I truly appreciate it!

---------------------
Edit:
I think one of the problems is that there is no link back to Table_1 in this query. I am trying to figure out the correct way to do it. Unfortunately, my SQL skills are (noticably) VERY rusty

I am posting a database that shows how to work with a many-to-many relationship. It should help you.