Test Scoring Query Help

  • Thread starter Thread starter tanorris
  • Start date Start date
T

tanorris

I am making a test scoring program which contains 700 test items
(Rows). I have a Table called NA (which stands for Test Item Number
and Test Item Answer).

The NA Table has two columns: N (which stands for Number) and A (which
stands for Answer). The Answers can be either 1 or 0. An
abbreviation of the NA Table looks like this:


NA Table

N A

1 1
2 0
3 0
4 1
5 1
6 0


Now, I want to know how to determine by query whether N 1 is A 1 and N
3 is A 0, (and) N 2 is A 1 and N 4 is A 0, (and) N 3 is A 1 and N 6
is A 0. In other words I need a query that determines whether each
specified pair of rows, e.g., N 1 and N 3 rows, meets the specified
criteria for A.

Any help you can give me would be greatly appreciated.

Thanks!!!

Tom
 
Hi,


Make a temp table with the associations you want:


Assos 'table name
N A ' fields
3 0
2 1
4 0
3 1
6 0


( N 1 is A 1 and N 3 is A 0, (and) N 2 is A 1 and N 4 is A 0, (and) N 3 is
A 1 and N 6 is A 0 )




Then:

SELECT COUNT(na.A)=(SELECT COUNT(*) FROM assos)

FROM na RIGHT JOIN assos
ON na.N=assos.N AND na.A=assos.A



would return true, or false. Note that I assumed there is no duplicated
couple (N, A) in table na. IE, you can have

n a

1 0
1 1


but not

1 0
1 0




The implied principle is that if a match is found, from assos into table na,
then na.A won't be null; otherwise, if there is no match, na.A will be NULL.
If the is no match for a given assos.N, then the COUNT(na.A), which does not
consider the NULL, won't be equal to the initial number of records in assos,
so, in case of a difference, that implies at least one condition, in the
AND, is not satisfied.



It is a variation from http://www.mvps.org/access/queries/qry0016.htm



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top