Integrity Query - Finding variations

[kit_sune]

I have a table of data where an instance of a job is recorded each day (a kind of snapshot, if you will). Each record has a serial number recorded, as well as a few other fields. In most cases all the fields are identical. What I'm trying to create is a query that displays records where they are not identical. I only want these records - it generally means someone caused a change on one of the records, and I need to check if it was an accurate change or not.

Here's an example:
If I have a table like this:
Serial | Work Code | Date
A | AAA | 1 Jan
A | AAA | 2 Jan
A | AAB | 3 Jan
B | BBB | 1 Jan
B | BBB | 2 Jan
B | BBB | 3 Jan

I want it do only show me this:
Serial | Work Code | Count
A | AAA | 2
A | AAB | 1

Since all of Serial "B" were identical (except the date) I don't care to check it, but "A" has a mismatch and I need to check if it's correct or not.

How can I go about doing this?

Thank you,
~Kit

 

[plog]

This is going to take 2 sub queries. Because you didn't specify a table name, I used YourTableNameHere. This is the SQL for the first subquery which gets all unique Serial/Work Code permutations and their frequency:

SELECT YourTableNameHere.Serial, YourTableNameHere.[Work Code], Count(YourTableNameHere.Serial) AS PermCount
FROM YourTableNameHere
GROUP BY YourTableNameHere.Serial, YourTableNameHere.[Work Code];

Save it as 'sub1'. Then you build another query using it to determine which Serial values have duplicates:

SELECT sub1.Serial
FROM sub1
GROUP BY sub1.Serial
HAVING (((Count(sub1.Serial))>1));

Name that 'sub2'. Then to get your data, you link those 2 sub queries together and get all the ones with duplicate Serial values:

SELECT sub1.*
FROM sub1 INNER JOIN sub2 ON sub1.Serial = sub2.Serial;

Lastly, you've used some poor field names. 'Date' is a reserved word and will cause issues when writing code. [Work Code] contains a space which will also cause issues when writing code. You should only use alphanumeric characters and underscores in naming objects.

 

[Minty]

Use the query designer and press the totals button under the fields you want to see (Serial and Work code) Select group by - if you need to select specific dates put the dates filed in with a where in the designer.

edit - See Plog's answer

 

[kit_sune]

I'm impressed, that worked really well! I usually try to find any way I can to do as much as possible in one query - Having so many queries makes me feel overwhelmed. But I know that sometimes you just can't avoid it. I found a naming convention that helps to make things blocked together.

Thanks for the help on that one, all the steps make sense and were easy to follow.

Cheers, Kit