Caregivers without clients

[Arnau]

I have a database used by an NGO where we have 1900 caregivers taking care of 5500 clients in Swaziland. Among others I have a table for the caregivers and another table for the clients and these two are connected in a query via the ID number of the caregivers. However, I realise that some client information have not been submitted and now it seems as if there are some caregivers without any clients. I would like to create a query consisting of the two tables but which indicate which of the caregivers DO NOT have any clients so that I can start working on obtaining the outstanding information. It might work if I test for caregiver IDs which exist in the caregiver table but do NOT exist in the caregiver/client query. But I cannot think of a way to do this. Any solutions will be highly appreciated.

[andy49]

Create a query of who has clients then link this to a list of caregivers with a left or right join and pull off the null values. It can be done using a subquery

[CJ_London]

these two are connected in a query via the ID number of the caregivers.

just make it a left join - in the query design window, double click on the join line and select the option 'select all records from caregivers....'

when you run the query, filter or sort the results where the client is null to find the caregivers without clients. Or back in query design, put the criteria 'is null' for one of the client fields

[Arnau]

Thanks for all your help. It is so simple once you know how to do it. I appreciate your input.