Access Comparitive Query

[Chris762]

Hi All,



I have two sets of data I want to compare.

In one set I have a reference number, such as 000750 and a state, such as AZ.
I have another table with similar repeating records.

What I need to do is compare the two tables by matching number with number and making sure the states are identical.

For example if I have the following

Table 1
000750 AZ
000750 MI
000751 AZ
000751 AZ

Table 2
000750 MI
000750 MI
000751 AZ
000751 AZ

I would need it to identify that 000750 has different states in the two sheets

Thanks for the Help!

[June7]

Have you tried the Find Unmatched query wizard?

[Chris762]

I have, It is simply identifying that one record 000749 for example is in one table and not the other. I need it to compare the states for each number and if they are the same move on and if different identify that in the query results

[June7]

You need to compare the combined value of number and state but only if the number is in both tables?

Show the sql statement you attempted. Try this:

SELECT Table1.Number, Table1.State
FROM Table2 RIGHT JOIN Table1 ON (Table2.Number = Table1.Number) AND (Table2.State = Table1.State)
WHERE (((Table2.State) Is Null))
GROUP BY Table1.Number, Table1.State
HAVING Table1.Number IN (SELECT Number FROM Table2);

The wizard wasn't much help on this one after all.

[Chris762]

I just tried to use the unmatched query. The same numbers are in both tables but some are repeated. I just need to ensure the states are the same for each number.

[June7]

I was editing my previous post while you were posting yours. Review it again.

[Chris762]

Allright thanks, I will give this a try tommorow! I'll post back and let you know.

Thanks!