I have to do a comparison of two tables.  Both tables have the same exact headers.  The first table is the standard rates template which is linked from excel.  The second table is coming from a query off our AS400 system.  So basically we are needing to compare with the standard template vs what was inputted into the system.  Basically auditing the work inputting, looking for typos and mismatches.  After researching, I was advised to use a Union Query.  Now when I do that, it just simply shows all rows and columns, not just the mismatched.  Could you tell me of a simpler way or maybe I'm not completing using the Union Query to the fullest.
Here's the SQL for the union query:
SELECT [AMH Flood Commerical Rates].State, [AMH Flood Commerical Rates].A99, [AMH Flood Commerical Rates].[AR Zone], [AMH Flood Commerical Rates].Tier1, [AMH Flood Commerical Rates].Tier2, [AMH Flood Commerical Rates].Exception, [AMH Flood Commerical Rates].Rate, [AMH Flood Commerical Rates].CHRA, [AMH Flood Commerical Rates].CHRAV, [AMH Flood Commerical Rates].[Non CHRA deductible], [AMH Flood Commerical Rates].[Non CHRA Pct], [AMH Flood Commerical Rates].[CHRA deductible], [AMH Flood Commerical Rates].[CHRA Pct], [AMH Flood Commerical Rates].[Tier deductible], [AMH Flood Commerical Rates].[Tier Pct], [AMH Flood Commerical Rates].[Policy Fee], [AMH Flood Commerical Rates].[Max FP Cov], [AMH Flood Commerical Rates].TreatyE
fROM [AMH Flood Commerical Rates];
UNION SELECT
qAMHFloodRatesRESIDENTIAL.State, qAMHFloodRatesRESIDENTIAL.A99, qAMHFloodRatesRESIDENTIAL.[AR Zone], qAMHFloodRatesRESIDENTIAL.Tier1, qAMHFloodRatesRESIDENTIAL.Tier2, qAMHFloodRatesRESIDENTIAL.Exception, qAMHFloodRatesRESIDENTIAL.Rate, qAMHFloodRatesRESIDENTIAL.CHRA, qAMHFloodRatesRESIDENTIAL.CHRAV, qAMHFloodRatesRESIDENTIAL.[Non CHRA deductible], qAMHFloodRatesRESIDENTIAL.[Non CHRA Pct], qAMHFloodRatesRESIDENTIAL.[CHRA deductible], qAMHFloodRatesRESIDENTIAL.[CHRA Pct], qAMHFloodRatesRESIDENTIAL.[Tier deductible], qAMHFloodRatesRESIDENTIAL.[Tier Pct], qAMHFloodRatesRESIDENTIAL.[Policy Fee], qAMHFloodRatesRESIDENTIAL.[Max FP Cov], qAMHFloodRatesRESIDENTIAL.TreatyE
FROM qAMHFloodRatesRESIDENTIAL;

---

Answer from Richard Rost:

Your UNION query is working correctly. You just shouldn't be using a UNION query to do this. :)

A UNION query joins together all of the rows from two tables. So, for example, if you have a Customers table and an Employees table, and you want to make a mailing list that includes all of the names and addresses from BOTH tables to perform one mailing, you could UNION them together.

If you want to compare two tables like this side by side, you'll need to just JOIN them together in a query using some kind of relationship, and probably with an OUTER JOIN so you can see missing records from the other table.

I can't see your data (only your field names) so I really can't advise on this.