is there a "does not include" query in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000 and have a database with a Clients table (client # is
primary key, then includes name, address, phone...) and a Work In table
(keeps track of when we receive each months work for each client). I need to
create a report that shows me which clients we have not received work for.
Is there someway I can setup a query to find all clients that do not include
Work In for a specified month??

Any help is appreciated.

Thank you!!

Cheryl W.
 
yes.. standard SQL..

Write the query that is the criteria for Not included.. Go to the sql window
and copy the sql.

you have to have the criteria just select the key of records you don't want
included.

In criteria section of the real query you are writing, on the id field write,

Not in <paste SQL here>

Example criteria


NOT in (Select myID from tblTest where tblTest.month = 6 )

hth
 
You can use the (Not) In operator or the (Not) EXISTS Clause.
Alternatively, you can simple use a Left Outer Join like:

****Untested****
SELECT C.ClientID, ....
FROM tblClient AS C LEFT JOIN
(
SELECT DISTINCT Sub.frg_ClientID
FROM tblWorkIn
WHERE {Your Date Criteria}
) AS W
ON C.ClientID = W.frg_ClientID
WHERE W.frg_ClientID Is Null
****

Check the JET Reference on the In operator and the EXISTS cluase also.
 
Van

Thank you for your help, I think I understood part of what you said - with
the Left Outer Join - but got a little lost after that. You use C. and W and
Sub.frg terms I do not understand.

I created a query using the Left Outer Join and included the following fields:

Client #
HA Name (not used in search, just make it easier than looking at Client #'s)
Month (entered as numbers 1 thru 12)

Do I now create a new query and use the rest of your instructions (which I
believe are an SQL stmt)?? And where do I plug in what field name??

Hope this gives you the informaiton you need.

Thank you for your continued help.

Cheryl W.
 
C (Client), W (WorkIn) and Sub (Table in SubQuery) are Aliases I use to
shorten the SQL String and to avoid the many instances of the Table
References clouding the SQL String.

In the Query Design Toolbar, simply click the down arrow of the first
Command ComboBox and select SQL (View) and you can type the SQL String
directly (replacing with appropriate Field names / Table names from your
database).

Alternatively, you can use the Menu View / SQL View to switch the query
design to SQL view.
 
Back
Top