odd failure for compare query

[nadsys]

hello,

trying to compare two tables from diff db's, i have linked the table from one db into the other db.

query is as follows:

SELECT [Clients1].[ID], [Clients1].[name], [Clients1].[surname], [Clients1].[address], [Clients1].[hobbies], [Clients1].[age] FROM Clients1 LEFT JOIN Clients ON [Clients1].[ID] = [Clients].[ID] WHERE ([Clients].[ID] Is Null);

query was done using "Find Unmatched Query Wizard" so its not a syntax error. i have made obvious differences between the two tables data yet i get no results when i run the query, just an empty table shows.

can anyone advise as too what im doing wrong, read help file and far as i can see i done it correctly (yet, thats obviously not the case hehe).

i bow to your superior knowledge.

 

[FoFa]

OK, I assume Clients1 is the linked table (since it ads a number when the table name is already used).
I do not see anything wrong with the SQL, so it must be telling you you have a one to one match with all the ID's in the linked table (clients1) or no records that are in clients1 that are NOT in Clients.
Try reversing the tables and see if you get the results you want like this:
SELECT [C1].[ID], [C1].[name], [C1].[surname], [C1].[address], [C1].[hobbies], [C1].[age] FROM Clients as C1 LEFT JOIN Clients1 as C2 ON [C1].[ID] = [C2].[ID] WHERE ([C2].[ID] Is Null);

 

[nadsys]

hello again!!

ok, i have run several tests with small sample db's. i have found out what is and isn't happening now.

the "find unmatched" ONLY shows you results of records that are completely missing from the other table.

what i require is that it checks every field within the record and displays the results of any of those records that have differences.

example below

table1:
neil stewart 22 mexico
james matthew 33 egypt
laura ashley 24 tasmania

table2:
neil stewart 24 mexico
james matthew 33 egypt
laura reeves 24 tasmania

results should be:
neil stewart 22 mexico (or 24 if i told it to show results of table 2's diff's)
laura ashley 24 tasmania ("as above")

any advice on how to achieve this, maybe the find unmatched query is not what i need to be using.

 

[FoFa]

Is there a key that ties these two tables together?
A query would not know that Laura Reeves and Laura Ashley are the same person unless that 24 is unique to tie the two tables together. How ever you show the 24 on Neil Stewart also, so how would a query know (with the information you supplied) that Neil Stewart is not now Luara Ashley (as an example). You have to something in common to tie the records together other than (any columns don't match), or you would return all the rows because they don't match. I think I am rambling now...
I understand what you are looking for, just not sure with the data you supplied how to tell you to go about it.

 

[nadsys]

the key that would tie these two is the ID field, an autonumber created for each field.

the ID field is the same in both tables for each record. so in theory i need to say "compare table1's IDField to Table2's IDField and all fields attached to them records to be compared"

So (in theory again) "compare all fields contained within record (table1 IDField 1) to all fields contained within record (table2 IDField 1), then its just a case of setting a loop up until null so that it checks through all the IDFields until EOF.

correct? and if so, can someone gimme a heads up on the code used to create such a complex query.

 

[FoFa]

Select T1.[Col1],T2.[Col1],T1.[Col2],T2.[Col2] etc
FROM Table1 as T1
INNER JOIN Table2 as T2 on T1.ID = T2.ID
WHERE T1.[Col1] <> T2.[Col1]
OR T1.[Col2] <> T2.[Col2]

Supply Col1 etc with your column names

See if this gets you closer