generate report to point out discrepancies in data

[joy]

I would like to create a database in which two researchers interpret data
independently and enter their values onto into identical forms consisting of
about 100 fields. Where their values do not match, I would like to generate
a report that lists the Record ID#, the name of the field where the
discrepancy exists, and the value that each person entered. Should I set it
up so that each form goes to its own table? And how do I set up the query
and report?

Thanks in advance to anyone who can help. Detailed instructions (or a
reference where I can find them) are greatly appreciated, as I am an Access
beginner.

 

[Allen Browne]

The first thing will be to get your tables right to store this data. At
minimum, you want these 3 tables:

Staff table (one record for each person):
StaffID primary key
Surname
...

DataItem table (one record for each thing being evaluated):
DataItemID primary key
DataItem

Evaluation table:
EvaluationID primary key
DataItemID the item being evaluated
StaffID who evaluated this item.
Result the value this staff member assigned to this item.

Now you can then create a query with a subquery that selects only the items
that you want. The subquery examines other records of the same table, so it
uses an alias ("Dupe" in the example below.) The WHERE clause in this
subquery returns items only where:
- it's a different record than the one in the main query;
- it's the same item as the one in the main query;
- it's a different result than the one in the main query.

Here's the query and subquery, assuming that the Evaluation table has the
fields above:
SELECT Evaluation.* FROM Evaluation
WHERE EXISTS
(SELECT EvaluationID FROM Evaluation AS Dupe
WHERE Dupe.EvaluationID <> Evaluation.EvaluationID
AND Dupe.DataItemID = Evaluation.DataItemID
AND Dupe.Result <> Evaluation.Result);

You can then create a report based on this query, to display the results the
way you want.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066