Use of Max is quite working

[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

 

[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

 

[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;

 

[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

 

[Ben]

Thank you everyone, it worked really well.

Ben