Max Value In Query

[Andy Bailey]

I have a subform that uses a query to pull in quotation info.
The query only pulls in the info relevant to a revision number that the user
has selected on another form. In this way the user can call up the first
([RevNo] = 0) submission, the second ([RevNo] = 1) and so on.

What I'm trying to do is display on the subform the highest revision number
relevant to that quotation so that the user can see if he's working with the
latest info.

I've tried using a Totals query with Max and I've tried DMax as shown below.
The problem is that it always returns the highest revision present in the
table tblProposalDetails rather than the highest revision number for the
quotation in question.

The SQL is:-

SELECT tblProposalDetails.ItemNo, tblProducts.Code, tblProducts.ProdName,
tblProposalDetails.UnitPrice, tblProposalDetails.Quantity,
tblProposalMaster.ProjectID, tblProposalDetails.PropSectID,
tblProposalDetails.ProductID, tblProposalDetails.ID,
tblProposalDetails.ItemRevNo, tblProposalMaster.RevNo,
DMax("ItemRevNo","tblProposalDetails") AS MaxRevNo
FROM (tblProposalMaster INNER JOIN tblProposalSections ON
tblProposalMaster.ProjectID = tblProposalSections.ProjectID) INNER JOIN
((tblSupply RIGHT JOIN (tblProducts INNER JOIN tblProposalDetails ON
tblProducts.ProductID = tblProposalDetails.ProductID) ON tblSupply.SupplyID =
tblProposalDetails.Supply) LEFT JOIN tblProductPriceValidity ON
tblProducts.PriceValidity = tblProductPriceValidity.PriceValidID) ON
tblProposalSections.PropSectID = tblProposalDetails.PropSectID
WHERE (((tblProposalDetails.ItemRevNo)=[RevNo]))
ORDER BY tblProposalDetails.ItemNo, tblProposalDetails.ID;

I can see that DMax("ItemRevNo","tblProposalDetails") isn't helping. How can
I restrict what it's looking at?

Any help gratefully received. Thanks.

Andy

 

[Allen Browne]

You need to use the Criteria in the DMax() to match it to the record in the
query

The line:
DMax("ItemRevNo","tblProposalDetails") AS MaxRevNo
will become something like this:
DMax("ItemRevNo","tblProposalDetails", "ItemNo = " &
tblProposalDetails.ItemNo) AS MaxRevNo

It may need extra quotes if ItemNo is a Text field (not a Number field.)

If you do not need the query to be editable, a subquery may be faster than
DMax(). If that sounds useful but subqueries are new, here's a starting
point:
http://allenbrowne.com/subquery-01.html

 

[Ken Sheridan]

Andy:

I'm guessing ProductID is the key per quotation. If so try:

DMax("ItemRevNo","tblProposalDetails", "ProductID = " &
tblProposalDetails.ProductID) AS MaxRevNo

This assumes ProductID is a number data type. It its text use:

DMax("ItemRevNo","tblProposalDetails", "ProductID = '" &
tblProposalDetails.ProductID & "'") AS MaxRevNo

If I've misread your query and its not ProductID that's the key I'm sure
you'll see what's needed here; its simply a case of restricting the DMax
function call to the subset of rows in the tblProposalDetails table from
which you want to return the highest value by referencing a value in another
column returned by the query.

Ken Sheridan
Stafford, England

 

[Andy Bailey]

Ken,

Many thanks - your advice was spot on and I really appreciate it. Your
explanation was helpful too. I'm not only chuffed that it works but I think
(I hope) that I have learnt something that I can apply elsewhere.

Kind regards


Andy

 

[Andy Bailey]

Allen,

That worked perfectly; thank you ever so much for your help. Having looked
at your solution it helped me make sense of the Office Help for DMax; I'd
completely missed the criteria element.

Thanks again.

Kind regards

Andy