How to select records not included in relationship 1

[keithinuk]

Hello
I have a simple Access 2003 database with 4 tables in it. Three of them ('distributor', 'category' & 'manufacturer') are related to the other ('linkage') by one to many relationships based on the primary keys.
The theory is that a distributor supplies certain categories of products and said products are supplied by a variety of manufacturers. When all of that info is pulled together a row is created in 'linkage'.
I'm trying to retrieve rows from 'distributor' that are not yet related to 'linkage'. In other words, no categories/manufacturers have been associated with a distributor yet. Making sense ?????
My problem is that I can't manage to get the rows out of 'distributor' which have no rows in 'linkage' yet.
I now have bald patches on my scalp where I've been pulling hair out. I've tried all manner of joins but I'm obviously doing something wrong - HELP please. I'm running out of hair.
Thanks in advance
Keith

In case I forget to say 'Thank you' I'll say it now - thank you for your help !!!

 

[ZmrAbdulla]

Best way to learn this to create a query that includes two tables(Manufacturer, Linkage). And when you are the design view of the query rightclick on the relations line and modify its relations and see the results after every changes. Also see the SQL changes.

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[traingamer]

try:

Select distributor.*, linkage.[distributorid] from distributor 
left join linkage ON distributor.[distributorid] = linkage.[distributorid] 
where (linkage.[distributorid]) is null

OR just create a query with the Find Unmatched Query Wizard

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[keithinuk]

What a star !!!!!!!!!!
It works like a dream - THANK YOU !!!!!!!!!!!

I'll just arrange the hair transplants now and I'll be back to normal.

In case I forget to say 'Thank you' I'll say it now - thank you for your help !!!