[AccessD] Query to find records from lookup table NOT in another table

One way is with two queries:

QryAllSiteCodes:
SELECT tblSites.Site, tblLookup.LCode
FROM tblSites, tblLookup
GROUP BY tblSites.Site, tblLookup.LCode;

QryMissing:
SELECT qryAllSiteCodes.Site, qryAllSiteCodes.LCode
FROM qryAllSiteCodes LEFT JOIN tblSites ON (qryAllSiteCodes.LCode = tblSites.code) 
AND (qryAllSiteCodes.Site = tblSites.Site)
WHERE (((tblSites.Site) Is Null));

-- 
Stuart


On 13 Jan 2011 at 21:58, Chris Swann wrote:

> Hi All,
> 
> I've really got my stupid head on today and can't get anything I try
> to return the records I need.
> 
> I have a table which has a list of codes and another table that holds
> details of codes that are in use at various sites.
> 
> What I need to find is those sites that DON'T have any of the codes
> from the lookup table.
> 
> I thought a find unmatched query would work but it doesn't return the
> correct records.
> 
> As an example of what I need
> 
> Lookup table has for example
> A1
> A2
> A3
> 
> Sites table has
> a    A1
> a    A2
> b    A3
> c    A1
> c    A2
> c    A3
> 
> so I need the query to return
> a    A3
> b    A1
> b    A2
> 
> Can anyone help on this one  ?
> 
> Many thanks in advance,
> 
> Chris Swann
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>