Show differences between tables

[rustyg]

G'day,

I am using ACCESS to compare roles for users on our SAP system before and after a QAS refresh. I have two tables, one listing roles before and one listing roles after the refresh, how do I have to relate these two tables and what type of query should I run to present me with a list of roles that appear in one table and not the other, since I have two tables there may be roles in table a that arent in table b, as well as roles in table b that arent in table a. I need to see both these. I also dont want to see any roles that appear in both tables.

any help?

Russ

 

[neileg]

The unmatched query wizard will do some of the work for you. You'll have to have two queries, though, to catch both sides. You could then union these queries to produce one set of data.

 

[rustyg]

Hi,

Tried that but it jus seems to loop out. The query takes over an hour to run (there are 180,000 entries in one table, and 60,000 in the other) and comes back with no data.

To clarify, I want to check if a user+role (combination of the two fields) appears in the other table. Both tables have only these two fields. One user can have many roles, and many users have the same roles, but a role can only be assigned to a user once.

Am I making myself understandable? Im even confusing myself at times.

Russ

 

[neileg]

Try joining your two tables on these two fields. Don't use an inner join otherwise that will only return matching records, you need to use an outer join, and I suspect you need to use a left and a right join in separate queries to get all the data you want.

 

[sonny]

Here is a sample that I use to compare 5 fields between 2 tables.
I put my table on the left and a contractors table on the right and my 5 compare statements between them in order to view the results clearly....

SELECT HURRICANE_ROE_DATA.ROENumber, HURRICANE_ROE_DATA.Plastic, HURRICANE_ROE_DATA.Panels, HURRICANE_ROE_DATA.Lumber, HURRICANE_ROE_DATA.Metal, HURRICANE_ROE_DATA.Small_Repairs, IIf([Plastic]=[PlasticSF],"Match","No") AS PlasticMatch, IIf([Panels]=[PlywoodSF],"Match","No") AS PlyMatch, IIf([Lumber]=[LumberLF],"Match","No") AS LumberMatch, IIf([Metal]=[MetalRoofSF],"Match","No") AS MetalMatch, IIf([Small_Repairs]=[SMallRepairEA],"Match","No") AS SmallMatch, [payXL-LJC-4oct].ROENumber, [payXL-LJC-4oct].PlasticSF, [payXL-LJC-4oct].PlywoodSF, [payXL-LJC-4oct].LumberLF, [payXL-LJC-4oct].MetalRoofSF, [payXL-LJC-4oct].SMallRepairEA
FROM HURRICANE_ROE_DATA INNER JOIN [payXL-LJC-4oct] ON HURRICANE_ROE_DATA.ROENumber = [payXL-LJC-4oct].ROENumber
ORDER BY HURRICANE_ROE_DATA.ROENumber;

Notice the "IIf([Plastic]=[PlasticSF],"Match","No") AS PlasticMatch," in the sql