Query for finding errors in input

[udigold1]

Hi, I'm pretty novice in query design,
I'm trying to figure out how to get a query that returns me error in data input, by that I mean the following (for example):
I Have a table with the fields "Car maker", "Car model", "Car plate number" and "Car color".
Basically the Car model should hold a code that also represent the color of it, but Car Color is independently selected, so I could have mistakes.
meaning that if for car plate number 11-22-33 which is model SZK (K for black), the color Blue was selected by mistake.

I want a query that will return me every discrepancy between car model and car color. ( I mean that if there's one SZK blue and one SZK that is black, I want both to appear in the query)

Thanks in advance!

 

[Joe4]

You will need a table that shows each model, and then what color that model should be.
Then, you can link this table to your other table on the model field, and return records where the color from the main data table does not match the color from the model/color table.

 

[udigold1]

I only have one table, and I’m only looking for inconsistencies...

 

[Joe4]

Create a query that extract the model and color fields only, and group them, so that it weeds out all duplicates.

Then create a new query based on that first query, that has the same two fields, but groups on the model field, and counts the color field.
If you set this query to only return records where the color count is greater than one, it will list out all the models that have discrepancies.

If you want to then see all the colors in the discrepancies, you can link the two queries together based on the model field, and return the model and color fields.