Query problem - record not in criteria 2

[Goondu]

I have a problem with my query that is picking out a record not in the criteria. Or it's a bug in Access?

Here's the SQL

SELECT Inventory.CURBAL, Inventory.[UNIT PRICE], PO.PONUM, POLINE.PONUM, POLINE.STOCKID, PO.STATUS, POLINE.QTY
FROM (PO INNER JOIN POLINE ON PO.PONUM = POLINE.PONUM) INNER JOIN Inventory ON POLINE.STOCKID = Inventory.[STOCK NO]
GROUP BY Inventory.CURBAL, Inventory.[UNIT PRICE], PO.PONUM, POLINE.PONUM, POLINE.STOCKID, PO.STATUS, POLINE.QTY
HAVING (((POLINE.PONUM)=471) AND ((POLINE.STOCKID) Like "XX*") AND ((PO.STATUS)="WAPPR")) OR (((PO.STATUS)="APPR"));

The wierd part is, if I change the criteria to

 AND ((PO.STATUS)="APPR")) OR (((PO.STATUS)="WAPPR"));

it will work.

PO.PONUM = Primary key
Inventory.STOCKID is not PK, index(no Dulplicate)
POLINE.PONUM is not PK, index,(no Dulplicate)

The problem is, it is picking out a record that is not in my criteria "HAVING (((POLINE.PONUM)=471)" but a record in PO.PONUM = 470! The PO.PONUM=470 have a status = "APPR" and had a single record in the table POLINE.

What's wrong? I can't figure it out.
Thanks.

 

[PHV]

Check the parenthesis.
Here a starting point:

SELECT I.CURBAL, I.[UNIT PRICE], P.PONUM, L.PONUM, L.STOCKID, P.STATUS, L.QTY
FROM (PO P
INNER JOIN POLINE L ON P.PONUM = L.PONUM)
INNER JOIN Inventory I ON L.STOCKID = I.[STOCK NO]
WHERE L.PONUM=471 AND L.STOCKID Like 'XX*' AND (P.STATUS='WAPPR' OR P.STATUS='APPR');

I'd use this:

SELECT I.CURBAL, I.[UNIT PRICE], P.PONUM, L.PONUM, L.STOCKID, P.STATUS, L.QTY
FROM (PO P
INNER JOIN POLINE L ON P.PONUM = L.PONUM)
INNER JOIN Inventory I ON L.STOCKID = I.[STOCK NO]
WHERE L.PONUM=471 AND L.STOCKID Like 'XX*' AND P.STATUS In ('WAPPR','APPR');

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Goondu]

Thanks for the input PHV.

It seems like the parenthesis was the problem but that's the output string from the Query Editor. It seems that I can't use the criteria using "Or" on the second line. I have to use it on a single line.

What do you think? Is it a bug or the method?

I'll consider using the "IN clause".

 

[JoeAtWork]

After removing the majority of your parenthesis which aren't needed, the HAVING clause essentially comes to this:

(POLINE.PONUM=471 AND POLINE.STOCKID Like "XX*" AND PO.STATUS="WAPPR")
OR
(PO.STATUS="APPR")

So, no bug, it's doing exactly what you told it to do. Anything with PO.STATUS="APPR" is going to be included in your results.

My guess is what you really want is:

HAVING POLINE.PONUM=471 AND POLINE.STOCKID Like "XX*" AND (PO.STATUS="WAPPR" OR PO.STATUS="APPR")

 

[Goondu]

JoeAtWork,

That's what PHV sugguested. The Having clause was a GroupBy query. Maybe that's not neccessary, was that the problem?

So, no bug, it's doing exactly what you told it to do. Anything with PO.STATUS="APPR" is going to be included in your results.

It was the output string directly copied from the Query Editor. Why the "OR" criteria did not correctly output the string to what you have suggested?

Example:
"((PO.STATUS)="WAPPR")) OR (((PO.STATUS)="APPR"))" is the output if I use the criteria in the second line. Notice the additional parenthesis there?

If I use a single line on the criteria ("WAPPR" Or "APPR"), the output string is "(PO.STATUS="WAPPR" OR PO.STATUS="APPR")" which is the correct string. And the output string is ((PO.STATUS)="WAPPR" Or (PO.STATUS)="APPR")) from the Query Editor.

I'm just puzzled at it. It seems that the Query Editor is having problems translating?

 

[JoeAtWork]

Every line in the Query Editor is another complete OR in your statement. Therefore if you have 3 lines, you will essentially have:

WHERE (all criteria in line 1) OR (all criteria in line 2) OR (all criteria in line 3)

Why the "OR" criteria did not correctly output the string to what you have suggested?

It was not incorrect, it just had more parenthesis than are really necessary. Auto-generated code will always be more verbose than if you write it manually. If I am debugging I clean up the unnecessary parenthesis as it is easier to see how the criteria is grouped.

You would probably benefit from creating your SQL statements, or at least the WHERE or HAVING parts, manually. It sounds like you are guessing where to put your criteria when you use the Editor. If you do it manually, it will force you to think about what your criteria really are.

 

[Goondu]

JoeAtWork,

I think your're right. If I were to include a second column "PO.Status" in the first criteria, the result is

WHERE (criteria in line 1) AND (criteria in line 1)

If the second column is change to the second line in the criteria (or my original of using just one column), the result is

WHERE ((criteria in line 1)) OR (((all criteria in line 2))

which was the error in the string. I'm still puzzle why it works with the additional parenthesis that did not have the closing parenthesis. Why no warning message?

Thanks JoeAtWork, it's now clearer that I can't use the second line "OR" criteria. That's problably the mistake.

 

[JoeAtWork]

Why no warning message?

Why would it give a warning message? The SQL was valid, it just wasn't what you actually wanted. The Query Editor is not a mind reader, it doesn't know what you really want, it only knows what you said you wanted.

 

[Goondu]

JoeAtWork,

So your saying if there is an extra parenthesis in the SQL string it wouldn't cause an error message?

That a weird way to handle this error! Maybe should ask MS.