Tom Wickrath/John Vinson - where are you?

[c02homer]

I desperately need help and if there is any further information I can
give you, please let me know.

I am running Access 2003 (XP2-Pro). I have two tables:

Table 1 - Quiz takers make choices
Table 2 - contains correct answers

EXAMPLE:

TABLE 1

Name Answer1 Answer2 Answer3
ID no. Key Field
Mary Sue Yes No Yes
Tommy Joe Yes No Yes
Billy DeWayne No Yes No
Bobby Joe No No Yes

TABLE 2 Answer1 Answer2 Answer3
Id No Key Field
Correct Choice No No Yes

I have the correct answers to each question in Table 2 and I want to
compare the answers, my thought being if the two items match,
return a 1, otherwise return a zero.

I created a Parent Report using Table 1 and a subreport using Table 2.
I dragged the subreport onto the Parent report. Then I created an
unbound field with the following formula which does not give me error,
but also does not return anything:

If([Table1]![Answer1]=[Table2]!­[Answer1],"1","0")

If you or someone can give me any insight as to how to make this work,
I surely would appreciate it!!

 

[John Vinson]

I desperately need help and if there is any further information I can
give you, please let me know.

Answers inline.

I am running Access 2003 (XP2-Pro). I have two tables:

Table 1 - Quiz takers make choices
Table 2 - contains correct answers

EXAMPLE:

TABLE 1

Name Answer1 Answer2 Answer3
ID no. Key Field
Mary Sue Yes No Yes
Tommy Joe Yes No Yes
Billy DeWayne No Yes No
Bobby Joe No No Yes

I think word wrap caught you here. What is ID No? The primary key of
this table?

TABLE 2 Answer1 Answer2 Answer3
Id No Key Field
Correct Choice No No Yes

And what is ID No in this table? Does this table contain multiple
records, or just one?

I have the correct answers to each question in Table 2 and I want to
compare the answers, my thought being if the two items match,
return a 1, otherwise return a zero.

I created a Parent Report using Table 1 and a subreport using Table 2.
I dragged the subreport onto the Parent report. Then I created an
unbound field with the following formula which does not give me error,
but also does not return anything:

If([Table1]![Answer1]=[Table2]!­[Answer1],"1","0")

Well, you can't just refer to Table1 in this way. That's not how you
can refer to the fields! Also, If() is not an Access function: the
function you want is IIF (Immediate If).

Instead, create a Query by adding Table1 and Table2 to the query grid.
If - as I hope - Table2 has only the one record, don't use ANY join
line (and if Access automatically creates a join on ID No, select that
line and delete it).

Then add three (or as many as you have questions) calculated fields:

Right1: IIF([Table1].[Answer1] = [Table2].[Answer1], 1, 0)

and similarly for the other questions.

If you or someone can give me any insight as to how to make this work,
I surely would appreciate it!!

Note that this table structure is probably a VERY BAD IDEA. What if
you add another question? Do you redesign your tables, your forms, all
your queries?

See Duane Hookum's excellent At Your Survey database sample for an
alternative approach which will be much more flexible:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'


John W. Vinson[MVP]

 

[c02homer]

Thank you - Thank you - Thank you!!! I have worked on this all day and
it works!!! I started out with your query suggestion and built on it
and through trial and error was able to get what I need. I don't know
if what I have done is the smartest or stupidest thing one can do, but
I'm very happy with it at the moment. Not only can I compare the
answers, get the number of right answers, but I can also calculate the
percentage correct to wrong answers. I did this by building a series
of queries and finally ending up with a "combo query" that gives me all
the data I need in one query. Amazing!!!!! And my reports are
awesome!

Thank you again!!! I hope Tom will forgive that I left the 'e' out of
his name. You and he have been fabulous help, which is why I just flat
cried out for you. No one responded to an earlier post and I was so
stuck.

And YOU, Dude, You ROCK!!

Thanks again!!