Mulitple criteria selection query

  • Thread starter Thread starter Roland Greve
  • Start date Start date
R

Roland Greve

Hi all,

Been working on this problem for several hours now but somehow I'm
overlooking something important and probably very obvious.

tblCPPT: Table with all past and future payments for different companies.
(PaymentDate, Amount, Currency, ExRate, Description, Category, CompanyFrom,
CompanyTo, PaymentNumber)
qryCompaniesConsIDNo: Query with company name(s) and matching company ID('s)
selected from a form with unbound checkboxes.

What I need is a query which selects those payments made by the selected
companies (tblCPPT.CompanyFrom) but excluding those payments made between
the selected companies (tblCPPT.CompanyTo); a consolidated payment plan for
the selected companies.

The SQL I have so far:
SELECT tblCPPT.*
FROM tblCPPT, qryCompaniesConsIDNo
WHERE (((tblCPPT.CompanyFrom)=[qryCompaniesConsIDNo]![CompanyID])
AND ((tblCPPT.CompanyTo)<>[qryCompaniesConsIDNo]![CompanyID]));

which selects the payments made by the selected companies allright, but
refuses to exclude those payments made between the selected companies. The
Data Types for both CompanyFrom and CompanyTo in tblCPPT are the same.

Hope this is clear.

Regards,
Roland
 
Roland,

You will need to add 2 instances of the companies list to the query,
one of which to link to each of the CompanyTo and CompanyFrom fields.
Something like this...

SELECT tblCPPT.*
FROM (tblCPPT INNER JOIN qryCompaniesConsIDNo ON tblCPPT.CompanyFrom =
qryCompaniesConsIDNo.CompanyID) LEFT JOIN qryCompaniesConsIDNo AS
qryCompaniesConsIDNo_1 ON tblCPPT.ComapnyTo =
qryCompaniesConsIDNo_1.CompanyID
WHERE qryCompaniesConsIDNo_1.CompanyID Is Null

- Steve Schapel, Microsoft Access MVP
 
Works perfect, thanks Steve.

Can you explain why a second instance of the companies list is necessary in
this query, I don't quite see that logic yet.

Thanks again.

Regards,
Roland

Steve Schapel said:
Roland,

You will need to add 2 instances of the companies list to the query,
one of which to link to each of the CompanyTo and CompanyFrom fields.
Something like this...

SELECT tblCPPT.*
FROM (tblCPPT INNER JOIN qryCompaniesConsIDNo ON tblCPPT.CompanyFrom =
qryCompaniesConsIDNo.CompanyID) LEFT JOIN qryCompaniesConsIDNo AS
qryCompaniesConsIDNo_1 ON tblCPPT.ComapnyTo =
qryCompaniesConsIDNo_1.CompanyID
WHERE qryCompaniesConsIDNo_1.CompanyID Is Null

- Steve Schapel, Microsoft Access MVP


Hi all,

Been working on this problem for several hours now but somehow I'm
overlooking something important and probably very obvious.

tblCPPT: Table with all past and future payments for different companies.
(PaymentDate, Amount, Currency, ExRate, Description, Category, CompanyFrom,
CompanyTo, PaymentNumber)
qryCompaniesConsIDNo: Query with company name(s) and matching company ID('s)
selected from a form with unbound checkboxes.

What I need is a query which selects those payments made by the selected
companies (tblCPPT.CompanyFrom) but excluding those payments made between
the selected companies (tblCPPT.CompanyTo); a consolidated payment plan for
the selected companies.

The SQL I have so far:
SELECT tblCPPT.*
FROM tblCPPT, qryCompaniesConsIDNo
WHERE (((tblCPPT.CompanyFrom)=[qryCompaniesConsIDNo]![CompanyID])
AND ((tblCPPT.CompanyTo)<>[qryCompaniesConsIDNo]![CompanyID]));

which selects the payments made by the selected companies allright, but
refuses to exclude those payments made between the selected companies. The
Data Types for both CompanyFrom and CompanyTo in tblCPPT are the same.

Hope this is clear.

Regards,
Roland
Click to expand...
Click to expand...
 
Roland,

Your initial idea was faulty for a start, because there were no joins
between the CPPT and the Companies, so it was actually returning a
Cartesian product, until your WHERE clause was applied. But leaving
that aside, the main point is this... You have two fields in CPPT,
namely CompanyTo and CompanyFrom, which you need to relate to the
Companies as selected by your query, and to which you want to apply
constraints to. If you try to do this on the basis of one
relationship between the CPPT and the selected Companies, all you will
get are those records where the company did not pay itself. I mean,
the CompanyTo and CompanyFrom will be getting compared always to the
same record in the Company list as defined by the query. The only way
it will work is to relate the CompanyTo field to the query, and the
CompanyFrom field separately and independently to the query, which
means a separate instance of the query.

Hope that sheds a glimmer of light.

- Steve Schapel, Microsoft Access MVP
 
To my own defense: I had an INNER JOIN in there originally, but since that
didn't seem to help matters much, I tried another approach without joins.

Thanks for the lesson, I think it slowly reaches my brain now.

Regards,
Roland
 
Back
Top