Use of Max is quite working

B

Ben

Hi all,

I have Access 2003 on WinXP. I have a table with:

PartID
TransactionID (runs in incremental order by 1)
DateOfSales
TotalCost
NumOfUnit
UnitPrice (which is a calculated field for TotalCost/NumOfUnit)

Each day we can have multiple sales transactions on a particular PartID,
and UnitPrice may be different based on the bulk volume. But what I
would like to do is to list all the PartID transactions, take the
largest TransactionID of that particular PartID. But it doesn't seem to
work, I still see multiple instances of the same PartID with their
TransactionIDs.

When there's multiple transaction on a PartID on a particular day, I
only want the record with the max TransactionID. Can you see what I
might be doing wrong?


SELECT
T.PartID,
Max(T.TransactionID) AS MaxOfTransactionID,
T.SalesDate,
T.TotalCost,
T.NumOfUnit,
T.UnitPrice
FROM Transaction AS T
GROUP BY
T.PartID,
T.SalesDate,
T.TotalCost,
T.NumOfUnit,
T.TotalCost/T.NumOfUnit,
HAVING (T.SalesDate=20091102) AND (T.TranType="Bulk")
ORDER BY T.PartID, Max(T.TransactionID)



Thanks in advance,

Ben
 
V

vanderghast

You would have a different record in the result as soon as ONE of the value
in the fields you supplied in the GROUP BY clause differ. So, for the same
PartID, same SalesDate, if you have two different TotalCost, you will have
two different rows in the result, for the same PartID and same SalesDate.
Note that if PartID is a string, then a trailing space make a string
distinct than the trimmed one, ie "Hello " is distinct to "Hello" even
if, visually, they both look the same. A Null is not distinct from another
Null, but is distinct from anything else (although a NULL is not equal to
another NULL, it is not distinct either).



Vanderghast, Access MVP
 
K

KARL DEWEY

Try this --
SELECT
T.PartID,
T.TransactionID,
T.SalesDate,
T.TotalCost,
T.NumOfUnit,
T.UnitPrice
FROM Transaction AS T
WHERE T.TransactionID = (SELECT Max([XX].TransactionID) FROM Transaction AS
[XX] WHERE [xx].SalesDate=20091102 AND [XX].TranType="Bulk" AND [XX].PartID =
T.PartID)
ORDER BY T.PartID, T.TransactionID;
 
K

KenSheridan via AccessMonster.com

Try this:

SELECT *
FROM Transaction AS T1
WHERE SalesDate=20091102
AND TranType="Bulk"
AND TransactionID =
(SELECT MAX(TransactionID)
FROM Transaction AS T2
WHERE T2.PartID = T1.PartID
AND T2.TranType = T1.TranType
AND T2.SalesDate = T1.SalesDate)
ORDER BY PartID;

You could of course make it more flexible by using a parameter for the sales
date:

SELECT *
FROM Transaction AS T1
WHERE SalesDate=[Enter sales date as yyyymmdd:]
AND TranType="Bulk"
AND TransactionID =
(SELECT MAX(TransactionID)
FROM Transaction AS T2
WHERE T2.PartID = T1.PartID
AND T2.TranType = T1.TranType
AND T2.SalesDate = T1.SalesDate)
ORDER BY PartID;

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top