Query for differences between tables.

  • Thread starter Thread starter Mark Wolven
  • Start date Start date
M

Mark Wolven

I am trying to get Access to compare the list of actual values in a
field in a database to a list of valid codes for that field. The valid
values are stored in separate table.

I have been able to generate a query that tells me when the (valid)
value appears in both and how many records that union represents.

What I have not been able to accomplish is the umber of times a value
is used that does not appear in the valid list. And also, I'm
interested in knowing the number of valid values that do not appear in
the database. Although, I feel that once I have skinned the first cat,
that skinning the second will just be a slight variation.

Thanks
 
SELECT A.AllValues,
Count(A.AllValues) as CountThem,
IIF(V.LinkValue Is Null,"Matched","Not Matched") as ValidValue
FROM TableActual as A LEFT JOIN TableValid as V
ON A.Allvalues = V.LinkValue
GROUP BY A.AllValues, IIF(V.LinkValue Is Null,"Matched","Not Matched")

That will show all values in the Actual table, a count, and whether they are in
table with valid values.

To get all the valid values that are not used will require a second query -
which you should be able to build with the unmatched query wizard.

SELECT V.LinkValue
FROM TableValid As V LEFT JOIN TableActual as A
ON V.LinkValue = A.AllValues
WHERE A.AllValues is Null

Substitute your field and tablenames.
 
Back
Top