Query to display data that does not match

phillsheen

Registered User.
Local time
Today, 18:47
Joined
Jun 12, 2006
Messages
86
Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.

I hope that makes sense?

Any help would be fantastic!

Cheers
Phill
 
I am doing something similar at the moment - although it's still work-in-progress. Could you not Right Join 'memberdata' and 'bankdata', or in design view > join properties choose option 3: "Include ALL records from 'bankdata' and only those records form 'memberdata' where the joined fields are equal". Then in the criteria field for the 'left' field (in memberdata) type "Is Null".

I hope I understood correctly!

Bobadopolis
 
Left joins and Right joins are the to go
 
That kind of works.
When i join the field and use the option 3 in the proterties it gives me a list of all the fields with or without a match. When I just in the "Is Null" criteria it shows no fields what so ever. Is there another criteria I can use to filter the information or have I just missed something?
 
Left and right joins are the type of joins you want to use for this type of query. You probably have the Is Null criteria in the wrong field. Can you post your SQL?
 
this is what coems up in my SQL veiw

SELECT Members.description AS Members_description, download20060602.Description AS download20060602_Description
FROM Members RIGHT JOIN download20060602 ON Members.description = download20060602.Description;


Membersdata = members
bankdata = download20060602
 
Try this.

SELECT Members.description AS Members_description, download20060602.Description AS download20060602_Description
FROM Members RIGHT JOIN download20060602 ON Members.description = download20060602.Description
Where ((IsNull(Members.description)=True));
 

Users who are viewing this thread

Back
Top Bottom