Find records that are present in one table, but are not present in other tables.

[mattcdse]

Hi all

Ok, let's say I have 3 tables with the following data

T1 T2 T3
1 , 1 , 1
2 , 2 , 3
3 , 4 , 4
, , 5

I'd like to be able to create a query that has the only record as its result as 5 as it is the only data item the does not exist in Tables 1 and 2. I don't just want to search for 5, I can do that I would like to be able to write a query which finds all the records that are present in one table, but are not present in x number (in this case 2) of other tables.

I don't seem to be able to find any simple way of doin this and there doesn't seem to be a definitive answer on any forums. Anyone got any ideas??

Cheers,

Matt

 

[RuralGuy]

Look at the Unmatched Query Wizard.

 

[KeithG]

You need an outer join between the tables.

 

[john471]

This might work

1) A Union Query (tantamount to appending all the records into one (temporary) table)

2) A totals query based on the Union Query; grouping on the value, and only showing results whose count = 1

Just a thought.

Regards

John.

 

[mattcdse]

Thanks for the replies

The Unmatched Query Wizard worked a treat. Thanks Rural

I have a new and very frustrating problem now. For some reason, when i type Me. into the code builder for controls on this from, a list of all the available methods does not appear. It used to, but for some reason is has just stopped. I have tested the code in other forms and it works absolutely fine. What's happened?? My form is now acting all wierd and a lot of hard work has gone into it.

Any ideas??

Matt

 

[mattcdse]

Tada!!!

Problem Solved!

So this is the deal.....I had 2 Private Subs with the same name and the same code. Not sure how I did it, but somehow the duplicate occured. Deleting either of them immediately returned Me. to full workin order.

Madness, total madness!

Matt

 

[RuralGuy]

Glad to hear you got it sorted.