Query with subqueries doesn't select as expected 3

[THWatson]

Using Access 2000

The SQL below is used to populate a report, and it has subqueries.

The SQL is supposed to select first by a criteria selection (a name) typed into a form, but regardless of what is typed in the query provides records for all names.

The line that is supposed to select only individual records is
WHERE (((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*")

SELECT DISTINCTROW tblCamper.CamperLast, tblCamper.CamperID, tblCamper.SiteNbr, tblCamper.CamperFirst, tblCamper.CamperLast2, tblCamper.CamperFirst2, IIf(IsNull([CamperFirst2]) And IsNull([CamperLast2]),[CamperFirst],[CamperFirst] & " and " & [CamperFirst2]) AS CamperFirstNames, IIf(IsNull([CamperLast2]),[CamperFirstNames] & " " & [CamperLast],[CamperFirst] & " " & [CamperLast] & " and " & [CamperFirst2] & " " & [CamperLast2]) AS CamperNames
FROM tblCamper
WHERE (((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*") AND ((tblCamper.CamperID) In (Select CamperID from qunInvoicesAndCredits))) OR (((tblCamper.CamperID) In (Select CamperID from qryCamperInterestSummary))) OR (((tblCamper.CamperID) In (Select CamperID from qryCamperPaymentsTotal)));

Any suggestions as to how to fix this, so that only the record for a specific name shows up?

Thanks.

Tom

 

[lespaul]

but you are selecting ALL the camperIDs from qryInvoices&Credits and ALL the camperIDs from qryCamperInterestSummary and ALL the camperIDs from qryCamperPaymentsTotal PLUS the camper name that was entered.

SELECT DISTINCTROW tblCamper.CamperLast, tblCamper.CamperID, tblCamper.SiteNbr, tblCamper.CamperFirst, tblCamper.CamperLast2, tblCamper.CamperFirst2, IIf(IsNull([CamperFirst2]) And IsNull([CamperLast2]),[CamperFirst],[CamperFirst] & " and " & [CamperFirst2]) AS CamperFirstNames, IIf(IsNull([CamperLast2]),[CamperFirstNames] & " " & [CamperLast],[CamperFirst] & " " & [CamperLast] & " and " & [CamperFirst2] & " " & [CamperLast2]) AS CamperNames
FROM tblCamper
WHERE (((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*")

This doesn't do it?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[THWatson]

Leslie
Thanks for the suggestion but no, that (removing the subqueries) doesn't do it.

I may need to break this out into more than one query and then put them together.

Tom

 

[lespaul]

This can't be too hard, it's only one table. What is your table structure, some raw data and what do you expect your results to be?

leslie

 

[JonFer]

My guess is that [txtNameCheck] is empty which would result in a LIKE "*" criteria and give you all records. Double check that your control name is the correct one.

In your sub-queries, I would use "Select Distinct CamperID ..." if you have more than one record with the same CamperID.


John

http://www.interautomateconsulting.com

 

[THWatson]

Leslie
Well, here's something interesting, and I think I have solved it, even though I don't quite understand why. Below are 2 Where clauses from the SQL. The first (my original version) fails, but the second one does the trick.

This one doesn’t work
WHERE (((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*") AND ((tblCamper.CamperID) In (Select CamperID from qunInvoicesAndCredits))) OR (((tblCamper.CamperID) In (Select CamperID from qryCamperInterestSummary))) OR (((tblCamper.CamperID) In (Select CamperID from qryCamperPaymentsTotal)));


This one does work
WHERE (((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*") AND ((tblCamper.CamperID) In (Select CamperID from qunInvoicesAndCredits) Or (tblCamper.CamperID) In (Select CamperID from qryCamperInterestSummary) Or (tblCamper.CamperID) In (Select CamperID from qryCamperPaymentsTotal)));

Can you see any difference in these? I can't.

But both are actually prepared in Design view rather than in SQL view. In the first (the failure), the 3 criteria clauses were on 3 separate lines under the CamperID column. In other words, X on one line, Or X2 on a second line, Or X3 on a third line.
In the second (the successful one), the criteria clauses are all on the same line. In other words X Or X2 Or X3.

The SQL versions look the same to me, but the results are not the same. I'm sure there is an explanation, but it's not self-evident to me. Maybe it's in the bracketing, but Access put those in, not I.

John
The [txtNameCheck] is correct. Using Distinct in the subqueries doesn't work in this case.

Tom

 

[PHV]

When mixing AND & OR operators you have to carefully pay attention to the parenthesis.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JonFer]

Tom - When you wrote that DISTINCT doesn't work, do you mean it isn't necessary because the queries only have one row per CamperID?

PHV - good eye on those parentheses. Although it can be annoying, the feature in Excel which shows you the opening parentheses for each closing one can be helpful.

 

[THWatson]

PHV
As I indicated above, both query formations were made in Query Design View, not SQL View. Access put the brackets in.

JOHN
The reason for the process is to prepare a "Balances Owing" report. The report is based on the query, the SQL of which I posted in the initial post. This query gives basic information as to a Camper's name etc. But the report has 3 subreports (one that pulls invoices and credits totals, one that pulls payments totals, one that pulls interest charges if any) and these 3 subreports are all populated from the queries which are indicated in the subqueries in the SQL.

What I meant when I said that DISTINCT in the subqueries doesn't work is that using DISTINCT doesn't make any difference to the result.

For whatever reason, and it must be the bracketing, the second Where clause formation gives the result I want. Even though I still say it surely isn't intuitive.

Thanks.

Tom

 

[RoyVidar]

For the fun of it, just doing some indentations based on the parenthesis - see any difference?

First statement
[tt]WHERE
(
(
(
tblCamper.CamperLast
)
Like [Forms]![frmNameSelector]![txtNameCheck] & "*"
)
AND
(
(
tblCamper.CamperID
)
In
(
Select CamperID from qunInvoicesAndCredits
)
)
)
OR
(
(
(
tblCamper.CamperID
)
In
(
Select CamperID from qryCamperInterestSummary
)
)
)
OR
(
(
(
tblCamper.CamperID
)
In
(
Select CamperID from qryCamperPaymentsTotal
)
)
);[/tt]


Second statement
[tt]WHERE
(
(
(
tblCamper.CamperLast
)
Like [Forms]![frmNameSelector]![txtNameCheck] & "*"
)
AND
(
(
tblCamper.CamperID
)
In
(
Select CamperID from qunInvoicesAndCredits
)
Or
(
tblCamper.CamperID
)
In
(
Select CamperID from qryCamperInterestSummary
)
Or
(
tblCamper.CamperID
)
In
(
Select CamperID from qryCamperPaymentsTotal
)
)
);[/tt]

I think the QBE grid thingie get's pretty confusing when working with subqueries, mixing AND and OR... a beautiful sample can be found here thread702-1065642 - such isn't exactly encouraging to work with, if you ask me;-)

I also think The QBE creates such a mess out of the SQL that it's hard to work with. I'd usually start with stripping off excess paranthesis (or all) in the where clause, and build it from scratch (which I seldom get right on the first try, but that's another story;-)).

[tt]WHERE
tblCamper.CamperLast Like [Forms]![frmNameSelector]![txtNameCheck] & "*" AND
(
tblCamper.CamperID In (Select CamperID from qunInvoicesAndCredits) Or
tblCamper.CamperID In (Select CamperID from qryCamperInterestSummary) Or
tblCamper.CamperID In (Select CamperID from qryCamperPaymentsTotal)
);[/tt]

In the first statement, you're evaluating
[tt](
camperlast against the form reference
AND
camperid against qunInvoicesAndCredits
)
OR
camperid against qryCamperInterestSummary
OR
camperid against qryCamperPaymentsTotal[/tt]

In the second statement

[tt] camperlast against the form reference
AND (
camperid against qunInvoicesAndCredits
OR
camperid against qryCamperInterestSummary
OR
camperid against qryCamperPaymentsTotal
)[/tt]

Wouldn't it be nice if the SQL-view stored the format/structure/indentations you inputted, and allowed comments...

Roy-Vidar

 

[PHV]

In the query grid:[tt]
Field: CamperLast CamperID
Table: tblCamper tblCamper
Criteria: Like [.... In (Select ... qunInvoicesAndCredits)
Or: Like [.... In (Select ... qryCamperInterestSummary)
Like [.... In (Select ... qryCamperPaymentsTotal)[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

Roy
Thanks for taking the time for the lengthy discourse on brackets! And the thread to which you pointed me really makes the point quite dramatically. It's not clear to me why Access adds those extra brackets when they just muddy things up.

Next time I encounter this I will start by stripping out the extra brackets.

PHV
Your solution does the trick too. Seems like a little extra but it works!

Thanks once again to both of you for the assistance. I learned something.

Tom

 

[JonFer]

Tom - DISTINCT in the sub-queries wasn't a fix for your problem but it should make the query run more efficiently (even though you get the same results with or without it).


John

http://www.interautomateconsulting.com

 

[THWatson]

John
Oh, I see. Thanks for the clarification.

Tom

 

[cmmrfrds]

Just to throw in another way for future consideration.
On small tables subqueries are okay, even through less efficient than joins. With large tables subqueries can be a performance problem. If CamperID is indexed in all tables then the join should be very fast.

FROM tblCamper
Left Join qunInvoicesAndCredits
On tblCamper.CamperID = qunInvoicesAndCredits.CamperID
Left Join qryCamperInterestSummary
On tblCamper.CamperID = qryCamperInterestSummary.CamperID
Left Join qryCamperPaymentsTotal
On tblCamper.CamperID = qryCamperPaymentsTotal.CamperID

WHERE ((tblCamper.CamperLast) Like [Forms]![frmNameSelector]![txtNameCheck] & "*")
AND Not
(qunInvoicesAndCredits.CamperID is Null
and qryCamperInterestSummary.CamperID is Null
and qryCamperPaymentsTotal.CamperID is Null))