comparing fields of two tables and output to a third

[salmonman]

It has been a while since I last used Access, recently I've used mySQL and PHP.

First a little description of what I'm trying to accomplish:
I have three tables...

tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025
Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.

tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025.
Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.

tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025.
Note: the fields a00# have the datatype set to "yes/no"

What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .

Here is some dirty pseudocode:
if i001 = 001 then
INSERT 1 INTO tblResult
else
INSERT 0 INTO tblResult


The above example gets a little repetitive since I would have to do that for each question.
Is it possible to put the answer fields of each table into a recordset then compare them?
(I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)

Alternativly, can I accomplish this using only SQL statements?

What would be the best way to attack this?

Any suggestions (or alternate suggestions) would be greatly appreciated.

Thanks,

salmonman

 

[MStef]

Hello salmonman!
I made a short example (with 7 answer).
Look at "DemoTestCompareA97.mdb"
Look at Tables, Run Query1, Look at tblResult.
(MStef alias Štef)

 

[VanRoekel.Clint]

Try the DLookup in Query2 without any coding

I added a structure table to MStef's sample database with numbers 1 through 7 for items. Query2 will read the structure table which must match the number of items in the StudentASR and the Import tables. Using the DLookup may not be the fastest way to query the database, but there wouldn't be any code to write.

Query2 is not an append query but it can be changed easily enough. The Correct column has a -1 for True and a 0 for False depending on if there is a match or not.

Send me an email if you have trouble with the single quotes and the double quotes in the DLookup function or if you are not using Access2003.

Have fun ! ! ! ! ! !

 

[VanRoekel.Clint]

Structured data

None of us are avoiding properly structured data and we know & use the rules for normalizing the data, but we work with what is given to us. Much of the data from which I produce decision support items is in a flat table structure from other systems...and rapid response is usually key to the decision makers.

Salmonman just duplicates the structure from the Student responses in the Correct response table and the Results table; adjust the number of items and the query doesn't need to be modified and hence neither does the report. No adjustments to any queries or code even when there are more or less than 7 items as in the example database that MStef created.

I hope that this helps.

 

[salmonman]

Sweet...

Firstly, thank you Mstef. Not only was your response quick, it was exactly the type of input I was looking for. Thank you for your time, I appreciate the example.
It being so long since I've played around with Access I almost forgot about the expression builder.

Next, Pat I appreciate the criticism on the table structure. I agree my current structure is horrible and far from normalized. However, to be honest, I have no idea what the final structure will look like. Currently what I'm working on is just a vague idea. I just happened to come across an old "scantron" machine and I've been trying to get it to work with access.

Also, thanks go out to VanRoekel. Again, thanks for taking the time to look into this. I played around with your code and made a little append query out of it. Still trying to keep everything simple until I have all my components working together (properly).

Ultimately, the table structure will determine the method (queries) I use.

This being my first post on this forum I am impressed at the speed and quality of the responses. I will defiantly be sticking to this forum when it comes to my Access questions. I will provide my input whenever possible.

Thanks again,

salmonman