Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2007

Tip: Looking for answers? Try searching our database.

Query Help Please

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ES - 29 Mar 2007 20:32 GMT
Can someone help me with a query.

I have a table like below

ItemNo    CostType
12345        a
12345        b   
54321        a
54321        c
09876        b
09876        c

I need a query that will pull the items that do not have a CostType b record.

In the above example the query would only return 54321.  

When I try to write it, I get each record that is not a b (ie all of them in
the above example)

Thanks!
John Spencer - 29 Mar 2007 20:50 GMT
SELECT Distinct ItemNo
FROM [Your Table] as YT
WHERE NOT EXISTS
   (SELECT *
    FROM [Your Table] as T
    WHERE T.ItemNo = YT.IemNo and CostType = "B")

The only problem with the above is that it could be slow with a large table.
Not exists is quite slow.

Two query solution is to build first query that gets all the ITemNo records
that do have a costtype of B and then use the unmatched query wizard to
return those that don't.

** IF** your table and field names don't have spaces or other special
characters you can do this all in one query.

SELECT Distinct ItemNo
FROM YourTable LEFT JOIN
 (SELECT ItemNo
  FROM YourTable
  WHERE CostType = "B") as T
ON YourTable.ItemNo = T.ItemNo
WHERE T.ItemNo is Null

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Can someone help me with a query.
>
[quoted text clipped - 18 lines]
>
> Thanks!
Jerry Whittle - 29 Mar 2007 21:04 GMT
SELECT DISTINCT ItemNo
FROM YourTable
WHERE ItemNo NOT IN
  (SELECT ItemNo
  FROM YourTable
  WHERE CostType = "b")
ORDER BY 1;

Change YourTable  to the actual table name.

If you have a lot of records, this could be slow. Indexing the ItemNo and
CostType fields could speed things up.
Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Can someone help me with a query.
>
[quoted text clipped - 16 lines]
>
> Thanks!
Jerry Whittle - 29 Mar 2007 21:14 GMT
I forgot an old rule: If an IN statement is slow, try an EXISTS and visa versa.

SELECT DISTINCT A.ItemNo
FROM YourTable AS A
WHERE A.ItemNo NOT EXISTS
  (SELECT "X"
  FROM YourTable as CT
  WHERE CT.ItemNo = A.ItemNo
  AND CT.CostType = "b")
ORDER BY 1;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Can someone help me with a query.
>
[quoted text clipped - 16 lines]
>
> Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.