Query issue - recommended services vs. used 1

[BearTrap3]

Hello All,

I have programmed a fairly simple database for a nonprofit senior services organization. They offer services based on six major components (a couple of which have sub-types underneath). They need a report showing clients who have been recommended for specific services but have NOT used them. I have already created a saved query which lists each client and the services recommended for them (the query has the following fields : client_id, fname, lname, component_id and subtype_id). This query works great, but I need to compare the contents of this query with a table called svc_log, where all the USED services, by client, are logged. I need to extract any component types and subtypes, for each client, NOT contained in the svc_log table, thereby denoting services recommended but not used. Take a look at this query:

SELECT DISTINCT ClientRecommended.client_id, ClientRecommended.fname, ClientRecommended.lname, ClientRecommended.component_id, ClientRecommended.subtype_id
FROM ClientRecommended INNER JOIN svc_log ON ClientRecommended.component_id = svc_log.component_id OR ClientRecommended.subtype_id = svc_log.subtype_id
WHERE ClientRecommended.client_id = svc_log.client_id
ORDER BY ClientRecommended.client_id;

The above query displays services recommended AND USED by each client, and I need services recommended but NOT used. I've been trying left outer joins (all records from ClientRecommended but only matching from svc_log) with no luck. I have been struggling with ambiguous join HELL and could use any suggestions.

Thanks so much in advance!

Fred

 

[Crowley16]

try using something like:

WHERE someID NOT IN (Select someOtherIDs FROM someTable WHERE blah...)

--------------------
Procrastinate Now!

 

[lupins46]

I haven't studied your fields in detail but this sounds like a straightforward unmatched query which you can create with the new query wizard.

 

[BearTrap3]

Thanks Crowley16!! DUH I actually used that technique with another query in this database! WHY I didn't think of it here I'll never know. Thanks again!

Fred