Compare tables: Trace diff records

B

Boss

Hi,
I have prepared a application for online test for employees.
I have two diff tables. one is answer master and employee answers

below is the answer master

Answer no Option A Option B Option C Option D
1 FALSE FALSE TRUE FALSE
2 FALSE TRUE FALSE FALSE
3 TRUE FALSE FALSE FALSE
4 TRUE FALSE FALSE FALSE
5 FALSE TRUE FALSE FALSE
6 FALSE TRUE FALSE FALSE
7 FALSE FALSE FALSE TRUE
8 FALSE FALSE TRUE FALSE
9 FALSE TRUE FALSE FALSE

Second is the emp answer

Emp Emp Option A Emp Option B Emp Option C Emp ption D
1 FALSE TRUE FALSE FALSE
2 TRUE FALSE FALSE FALSE
3 FALSE TRUE FALSE FALSE
4 FALSE TRUE FALSE FALSE
5 FALSE TRUE FALSE FALSE
6 FALSE FALSE TRUE FALSE
7 FALSE FALSE TRUE FALSE
8 FALSE TRUE FALSE FALSE
9 TRUE FALSE FALSE FALSE

I wish to compare both and wish to understand how many answers are coded
correctly by the emp and which are those.

I tried with matched & unmatched query but didn't worked properly!

Hope the situation is clear. Thanking lots for the help.

Boss
 
L

Lance

You're going to need to use a big, ugly nested iif statement like this..

Expr1: IIf([TEST KEY]![Option A]="FALSE",IIf([TEST KEY]![Option
B]="FALSE",IIf([TEST KEY]![Option C]="FALSE",IIf([TEST KEY]![Option
D]="FALSE","OTHER",IIf([ANSWERS]![Option
D]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
C]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
B]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
A]="TRUE","CORRECT","INCORRECT"))
 
J

Jeff Boyce

I may be reading too much into the sample data you provided, but ...

It appears that only ONE of the Options (A, B, C, D) is true for each row.
If so, why have 3 FALSE fields?

The data you provided looks like ... a spreadsheet!

In a relational database, you'd use a "narrow and deep" table structure
instead, with "A", "B", "C", or "D" in a single field, to indicate which
option was chosen. This way, you can get better use of Access'
relationally-oriented features and functions.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
B

Boss

Thanks for ur attention to the situation. Your comments are great.

Its false that one one option of a question is true. For most of teh case
only one option is true.
Eoether the field can be true or false because all fields are Yes/No
"FieldType" and users update the table using checkbox which is connected to
the form and the table.

I have 25 user each user has its own form and table. I can export one table
to excel and use Advance filter to compare tables. But access is not allowing
me to export 25 table usaing query as it becomes too complex.

I am a new user and its tuff for me to solve the prob. loking forward for ur
reply..
Thanking you

Boss
 
L

Lance

Why does each user have their own form and table? That's not necessary and,
as you've probably experienced, is a bear to maintain.

You only need a single table with an employee ID field.
 
B

Boss

Its important for me because i have secured each form with password. So each
emp can access his or her form only.

I cannot use user level securiy because if use then i cannot share the
application. As we use a different sharing system using citrix.

Hope u understand.
issue now is can an emp know his/her score just after the exam.

Thanks for the reply
Boss
 
L

Lance

You can accomplish the same level of security without individual forms and
tables, and save yourself a TON of time and effort. But that's your call.

The answer to your question is: you are going to have to use nested IIF
statements in your query, something like:

IIf([TEST KEY]![Option A]="FALSE",IIf([TEST KEY]![Option
B]="FALSE",IIf([TEST KEY]![Option C]="FALSE",IIf([TEST KEY]![Option
D]="FALSE","OTHER",IIf([ANSWERS]![Option
D]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
C]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
B]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
A]="TRUE","CORRECT","INCORRECT"))

With your current setup, you are going to need to generate your SQL
statements via code.. or create a query for each employee.
 
B

Boss

thanks for teh help.. I iwll try the way u said it. hope it works best..

Best wishes
Boss

Lance said:
You can accomplish the same level of security without individual forms and
tables, and save yourself a TON of time and effort. But that's your call.

The answer to your question is: you are going to have to use nested IIF
statements in your query, something like:

IIf([TEST KEY]![Option A]="FALSE",IIf([TEST KEY]![Option
B]="FALSE",IIf([TEST KEY]![Option C]="FALSE",IIf([TEST KEY]![Option
D]="FALSE","OTHER",IIf([ANSWERS]![Option
D]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
C]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
B]="TRUE","CORRECT","INCORRECT")),IIf([ANSWERS]![Option
A]="TRUE","CORRECT","INCORRECT"))

With your current setup, you are going to need to generate your SQL
statements via code.. or create a query for each employee.

Boss said:
Its important for me because i have secured each form with password. So each
emp can access his or her form only.

I cannot use user level securiy because if use then i cannot share the
application. As we use a different sharing system using citrix.

Hope u understand.
issue now is can an emp know his/her score just after the exam.

Thanks for the reply
Boss
Click to expand...
Click to expand...
 
J

Jeff Boyce

Consider an alternative... you can create a single form and validate each
user on that form so that s/he sees only his/her data.

Instead of continually maintaining forms as the number of employees changes,
you have a single form and a table of employee info, much easier to
maintain.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
B

Boss

Hi Jeff,

how can i vaidate users on a single form. I perpared seperate forms for
every user. Its was very time consuming. help me get rid of that.

Thanks
Boss
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel importing data 16
Remove a criteria from countif based on user selection 5
Error with calling Format sub 7
type mismatch error in macro in excel 0
Permutations andText to column problem 6
How to split a blob of data into an array? 5
Selecting an unmatched value 1
This Code does not work 8

Top