list of things that do NOT exist

  • Thread starter Thread starter AaronG
  • Start date Start date
A

AaronG

Running Access 2000 on Win2k,

I'm trying to build an invoice system. I have 2 tables.
One has client information (no duplicates) and the other
has the invoice information (duplicate information).

I need to pull up all clients who have not been billed in
a specific year. HOWEVER, I need to take into account
that many clients have an invoice history from past
years. I also need to account for the fact that some
clients (new ones) don't have a corresponding invoice
record yet.

Taking that into account, how do I get a list of clients
who have not yet been billed in a particular year?

Thanks!

Inportant fields:
Tables are linked by "ID" (primary key on Client Info)
Client information table has "Last_Name"
Invoice Information uses "Billing_Year"
 
One way is to use NOT IN or NOT EXISTS, but Access optimizes this very
poorly. Your query might look something like:

SELECT Client.Last_Name
FROM Client
WHERE Client.ID NOT IN
(SELECT ID FROM Invoice
WHERE Billing_Year = 2002)

A faster way is to include the subquery as a SELECT statement in the base
query's FROM clause:

SELECT Client.Last_Name
FROM Client LEFT JOIN
(SELECT ID FROM Invoice WHERE Billing_Year = 2002) As B2002
ON Client.ID = B2002.ID
WHERE B2002.ID IS NULL


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top