Question Table client id not in to find out.. (1 Viewer)

[DanJames]

I have a table (ClientCleaner) linking clients and cleaners together with id, clientid, and cleanerid. However, I want to have a query of this table to find out what clients do not have a cleaner.. and another query saying what cleaners do not have clients assigned ect.

the clientid somes from tblclients
and cleanerid comes from tblcleaners

So im thinking something like show whatever client ids aren't on the table in the query?

 

[ajetrumpet]

i am thinking your table structure is not normalized. show us the table structure first, and we'll give you a query solution. hard to tell from this post alone

 

[georgedwilkinson]

select * from tblclients
where clientid not in
(select clientid from ClientCleaner);

is one way to do this.

Usually, "not in" is notoriously slow if the junction table is large. Another alternative is to do an outer join on the 2 tables (tblclients and ClientCleaner) and check for null in ClientCleaner. I'll leave it to you to work out the syntax if you need it (or just use the nifty query builder).

 

[DanJames]

OK, so i make a query with the two tables tblclients and clientcleaner and have the fields clientid from tblclients and clientid from clientcleaner and then for the criteria have =Null. This hasn't worked. Can I have simpler instructions please?

 

[georgedwilkinson]

Create a query in design view, click on SQL view, paste the text I provided above, test it out. Very simple.

Or, using your approach, do what you did but right click on the line between the tables and select all tables in tblclients. Then where you have "=null" replace that with "is null".

 

[DanJames]

Thanks alot Georgedwilkinson. It works Great!!