Tricky Query

[Scott Reed]

I have data like:

Name Company 1 2 3 4 5 6...
John Cox ProLiddy 1 0 1 0 1 1

Where 1 is yes and 0 is no, is there a way I can get the computer to check
against a set of correct answers with the yes and no's and give me a report
with the percentages and totals?

Any Help would be appreciated!
Scott

 

[Duane Hookom]

I would normalize the data so that a comparison becomes easy. If you can't
change your table structure then you might be able to use a union query
like:

SELECT [Name], Company, 1 as TheField, [1] as TheValue
FROM [Data like]
UNION ALL
SELECT [Name], Company, 2 , [2]
FROM [Data like]
UNION ALL
SELECT [Name], Company, 3 , [3]
FROM [Data like]
UNION ALL
SELECT [Name], Company, 4 , [4]
FROM [Data like]
UNION ALL
.... etc...

You then create an answer key with similar structure where TheField is the
number and TheValue is the correct answer. A query with the normalized data
and the answer key can provide the number of matching records, percentages,
etc.