Can criteria be simplified?

[BruceM]

After some discussion and very helpful suggestions in this forum I have put
together SQL that finds the highest value in the PO_Rev field for a given
PO_Number.

By way of background, ReqID is the PK. PO_Number is assigned after
approvals are complete. A PO can be revised, in which case a new record is
created with the same PO_Number and a new PO_Rev. In the case of a revision
the number exists before the approvals are complete.

Here is the abbreviated SQL (leaving out fields that are not relevant to the
question at hand):

SELECT P.PO_Number, P.ReqID, P.PO_Rev,
P.SupplierID, P.AdminApp, P.ProdApp, S.SupplierName
FROM tblSupplier AS S
INNER JOIN tblPO AS P
ON S.SupplierID = P.SupplierID
WHERE P.PO_Rev = (SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE PO_Number = P2.PO_Number
AND P.AdminApp Is Null)
OR P.PO_Rev=(SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE P.PO_Number = P2.PO_Number
AND P.ProdApp Is Null)
ORDER BY P.PO_Number, P.ReqID;

My question is about this part of the SQL, which returns only the highest
PO_Rev for a record in which AdminApp is null:

(SELECT Max(P2.PO_Rev) _
FROM tblPO AS P2 _
WHERE PO_Number = P2.PO_Number _
AND P.AdminApp Is Null)

I wanted to test whether AdminApp or ProdApp are null, so I tried this:

(SELECT Max(P2.PO_Rev) _
FROM tblPO AS P2 _
WHERE PO_Number = P2.PO_Number _
AND (P.AdminApp Is Null
OR P.ProdApp Is Null))

However, this introduced into the recordset an earlier revision of one of
the POs. That revision had not been fully approved (i.e. AdminApp was
null). In order to eliminate that record I had to repeat the entire
subquery (if I am using the correct term) after the OR, but with ProdApp
instead of AdminApp. That is the first SQL above, which produces the
intended results.

Can I consolidate the SQL along the lines I hoped, or do I need to repeat
the whole thing?

BTW, I need to stress that this leaves out a lot of details. Questions may
arise about why I am storing the PO_Number in two different records in cases
where there is a revision. I can explain more fully, but I fear it would
unnecessarily complicate the question that I am trying to define as narrowly
as is possible.

 

[Jeff Boyce]

Bruce

Your SQL statement appears to find records where EITHER Admin_App OR
Prod_App is null. Are you looking for records where both are Null (use
AND).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Looking for either. Looking for both would have been simpler, I think, but
unfortunately not what is needed.

 

[Jeff Boyce]

Sorry, that was the extent of what I could see. Perhaps another newsgroup
reader can spot something.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michel Walsh]

You said:

========
I wanted to test whether AdminApp or ProdApp are null, so I tried this:

(SELECT Max(P2.PO_Rev) _
FROM tblPO AS P2 _
WHERE PO_Number = P2.PO_Number _
AND (P.AdminApp Is Null
OR P.ProdApp Is Null))

However, this introduced into the recordset an earlier revision of one of
the POs. That revision had not been fully approved (i.e. AdminApp was
null).
=======


That is what standard "or" does. If you want cases where ONLY one of them is
to be null, use XOR:


(SELECT Max(P2.PO_Rev) _
FROM tblPO AS P2 _
WHERE PO_Number = P2.PO_Number _
AND (P.AdminApp Is Null
XOR P.ProdApp Is Null))





Vanderghast, Access MVP

 

[BruceM]

My question was whether there is a more efficient way to express this
criteria:
WHERE P.PO_Rev = (SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE PO_Number = P2.PO_Number
AND P.AdminApp Is Null)
OR P.PO_Rev=(SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE P.PO_Number = P2.PO_Number
AND P.ProdApp Is Null)

My understanding is that there is not. I have to repeat this part:
SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE PO_Number = P2.PO_Number

AFAIK XOR is not part of Jet SQL language, but I will try it out tomorrow
just to be sure.

 

[Michel Walsh]

The fastest way would be to execute the sub query just once, as you also
wrote:



WHERE P.PO_Rev = (SELECT Max(P2.PO_Rev) _
FROM tblPO AS P2 _
WHERE PO_Number = P2.PO_Number _
AND (P.AdminApp Is Null
OR P.ProdApp Is Null))



but you added that this was not working either for some (obscure, at least
to me) reason. Which lead me asking if you were only seeking records where
ONE of the two fields, AdminApp, PropApp, is null to compute your Max.



XOR and IMP are two non standard conjunctions but are both working fine with
JET.





Vanderghast, Access MVP

 

[BruceM]

Now it works. After some experimentation I concluded that I must have made
an error in this part:
(P.AdminApp Is Null OR P.ProdApp Is Null)

If I do not specify the main table alias (P), or if I use P2 (subquery table
alias) I get extra records. Specifically, it does not limit the selection
to the highest PO_Rev record. It seems I was staring at it for so long that
I was unable to see an error in spelling, bracketing, or something basic.

Thanks for staying with this. It got me to go back and take another look in
the morning.