Sub Query Question

[ironwood9 via AccessMonster.com]

I think a sub query is the answer to my challenge, but I don't know how..

My Sales table looks like this

CustNo....SalesCode
=====.....======
03392............ A4
09393............ B5
03392............ IJ
09393............ U7
09393............ H3

My Tax table looks like:

CustNo........Tax Code
=====.....======
03392............ CT
33333.............CU

The same custNum will be in the first table several times - for each
indiviidual customer, I need to know which ones (cust Num) do NOT
have Tax Code CT - if a customer has a record in the first table
(ID'd by CustNo), there should be one corresponding record with CT as the tax
code. Can someone give me an example of how my query would look ?

 

[Joshua A. Booker]

Hi,

It sounds like you need to use two queries and a Left Join.

Start with a query that returns all Customers with 'CT' and name is
qryCustomersCT for example.

SELECT CustID FROM tblTAX
WHERE TaxCode = 'CT'

Then try the 'Find Unmatched Query Wizard'.

The Query should look something like:

SELECT *
FROM tblSALES LEFT JOIN qryCustomersCT ON tblSALES.CustID =
qryCustomersCT.CustID
WHERE qryCustomersCT.CustID IS NULL

A sub query is another way, but you would still need to join to the tax
table to get the tax code in your case.

I have found subqueries to be slower in most cases because Access
(supposedly) optimises it's execution plan on saved queries and doesn't know
how to optimize a subquery in the criteria.

HTH,
Josh

 

[ironwood9 via AccessMonster.com]

that worked great - thanks !

Hi,

It sounds like you need to use two queries and a Left Join.

Start with a query that returns all Customers with 'CT' and name is
qryCustomersCT for example.

SELECT CustID FROM tblTAX
WHERE TaxCode = 'CT'

Then try the 'Find Unmatched Query Wizard'.

The Query should look something like:

SELECT *
FROM tblSALES LEFT JOIN qryCustomersCT ON tblSALES.CustID =
qryCustomersCT.CustID
WHERE qryCustomersCT.CustID IS NULL

A sub query is another way, but you would still need to join to the tax
table to get the tax code in your case.

I have found subqueries to be slower in most cases because Access
(supposedly) optimises it's execution plan on saved queries and doesn't know
how to optimize a subquery in the criteria.

HTH,
Josh

I think a sub query is the answer to my challenge, but I don't know how..

[quoted text clipped - 21 lines]

tax
code. Can someone give me an example of how my query would look ?