Highest value for a field

[BruceM]

I have a database that is used for Purchase Order, among other things. It
is a standard set-up, with a Vendor table, a PO table, and a junction table
between the two. There is more, but I don't think the details are relevant
to the question at hand.

When a purchase order is started the PK field ReqID is populated with the
next sequential number. After the PO has been approved it is assigned a
number automatically in the PO_Number field.

Sometimes an approved PO needs to be reworked, perhaps due to a pricing
change. In that case a revision of the PO is created. It has the same
PO_Number value, but another field PO_Rev, which is 0 by default, is now 1.
Here is some raw data:

ReqID PO_Number PO_Rev
12 123 0
46 129 0
60 129 1
70 0
71 0

What I want is to show the highest PO_Rev for a given PO_Number:

ReqID PO_Number PO_Rev
12 123 0
60 129 1
70 0
71 0

I know I could do this in a query along the lines of:

SELECT Max(PO_Rev) as MaxRev
FROM tblPO
GROUP BY PO_Number

except that there will be only one grouping level for all records that have
not been assigned a PO number. How can I get around this limitation?

On a somewhat related question, right now I am loading the PO records, then
applying a filter to limit the initial displayed recordset to records that
do not yet have a PO_Number, and POs that have not been fully approved
(based on a few other fields I have not shown). In the course of using the
database people may filter the records by Customer, Sender, date range,
closed POs, and a few other criteria. I have a series of text boxes and
combo boxes in which the user can select the criteria. This causes a filter
string to be built, then applied.

The question is whether I would do better to load just the default
recordset, then redefine the recordset as the filter criteria are selected.
Essentially I would be using the filter string I am creating now as a WHERE
condition for the recordset SQL, which would be applied in code
(Me.Recordset = strSQL).

It seems to me that the advantage, especially as the number of records
grows, is that a relatively small number of records is being loaded at
first. In many cases it is the only recordset the user will need. If I
understand the advantage correctly, is there a disadvantage?

 

[Danny J. Lesandrini]

Ok, this might sound a litle whacky, but what if you substituted the ReqID for
missing PO_Number values, but set their sign to Negative so there are no
collisions with assigned PO_Numbers?

So if the query reported PO_Number as PO_Num: IIF(PO_Number IS NULL, (-1 * ReqID), PO_Number )

ReqID PO_Number PO_Rev
12 123 0
60 129 1
70 -70 0
71 -71 0

Anytime you see a negative PO_Number, you'll know it's not assigned, but the
substitute value will allow for proper GROUP BY results.

Does that sort of answer your question or did I completely misunderstand?

 

[BruceM]

Thanks for the reply, but I have to say I don't see how that is going to
address the problem at hand. Actually, I don't think grouping will work at
all, since I need fields beside the grouping and max fields. I can group by
the expression along the lines you suggest (no need for negative numbers
that I can see):

SELECT Max(tblPO.PO_Rev) AS MaxRev, _
Val(Nz([PO_Number],[ReqID])) AS MaxNum
FROM tblPO
GROUP BY Val(Nz([PO_Number],[ReqID]))
ORDER BY Val(Nz([PO_Number],[ReqID]));

The problem I have always had with grouping queries is that I don't see how
to bring other fields such as OrderDate into the recordset.

I think I need a subquery to make this work, but I have not been able to
figure out how. I am going to repost in the query group, since I am
convinced the solution to this problem (if indeed there is one) lies in the
query. Since the main query may not be editable (assuming I can build the
query at all) this may not be the solution either. It may be necessary to
add a Boolean OldRevision field to the table so that when a new revision is
created OldRevision is marked as True. The criteria for that field in most
cases will be False. It seems there is enough data in the recordset to limit
the recordset as I need, but as I said I cannot figure out how.