Last - in a query

[neenmarie]

I have a table of shippers containing: Shipper#, PartNumber, Date, Quantity.
I'm trying to total the quantity for each part number in a query with:
Date - Last
Shipper# - Last
PartNumber - Grouped By
Quantity - Sum
It will not give me the last Date or Shipper Number.
I've even removed all fields except the date and it still will not give me
the last date for the whole table.
Any ideas? It's driving me nuts.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try:

SELECT [Date], Shipper#, PartNumber, Sum(Quantity) As TotalQty
FROM Shippers As S
WHERE [Date] = (SELECT Max([Date]) FROM Shippers WHERE PartNumber =
S.PartNumber)
GROUP BY [Date], Shipper#, PartNumber
HAVING [Date] = Max([Date])

The most recent date is found using MAX(). The earliest date is found
using MIN().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRluJIechKqOuFEgEQIiPgCgzF7PM/jILauykpOiM193KUrNA/IAn1Mg
npI2/cF5p62z5UxhMkhvgB67
=DTq2
-----END PGP SIGNATURE-----

 

[Ken Snell]

Last doesn't necessarily mean "the latest" when it comes to a record. Last
is whichever record is the last one found by the Jet database engine, which
may or may not be what you think is the last one.

For dates, the most recent date ("last date") usually can be obtained by
getting the maximum date.

So, perhaps this query will be closer to what you want:

SELECT PartNumber, Max([Date]),
First([Shipper#]), Sum(Quantity)
FROM TableName
GROUP BY PartNumber;

 

[neenmarie]

Thank you for your help...much appreciated
Janine

Last doesn't necessarily mean "the latest" when it comes to a record. Last
is whichever record is the last one found by the Jet database engine, which
may or may not be what you think is the last one.

For dates, the most recent date ("last date") usually can be obtained by
getting the maximum date.

So, perhaps this query will be closer to what you want:

SELECT PartNumber, Max([Date]),
First([Shipper#]), Sum(Quantity)
FROM TableName
GROUP BY PartNumber;

--

Ken Snell
<MS ACCESS MVP>

I have a table of shippers containing: Shipper#, PartNumber, Date, Quantity.
I'm trying to total the quantity for each part number in a query with:
Date - Last
Shipper# - Last
PartNumber - Grouped By
Quantity - Sum
It will not give me the last Date or Shipper Number.
I've even removed all fields except the date and it still will not give me
the last date for the whole table.
Any ideas? It's driving me nuts.