[AccessD] Query to find records from lookup table NOT inanother table

Excellent solution Stuart! Using an aliased subquery it might be compressed to a single query like this:

SELECT AllSiteCodes.Site, AllSiteCodes.LCode
FROM (SELECT DISTINCT tblSites.Site, tblLookup.LCode
FROM tblSites, tblLookup) AS AllSiteCodes LEFT JOIN tblSites ON AllSiteCodes.Site=tblSites.Site AND AllSiteCodes.LCode=tblSites.LCode
WHERE tblSites.Site Is Null

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 13. januar 2011 23:26
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Query to find records from lookup table NOT inanother 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
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com