Access Last Function

[BS]

In access I am trying to return the last ship date of a product. I have my
product field and date field. How do I do this?

 

[Stefan Hoffmann]

hi,

In access I am trying to return the last ship date of a product. I have my
product field and date field. How do I do this?

Either use a query, add your table and these two fields, activate the
grouping function (press the Greek S, sum symbol) and assign the
function Max to your date field.


mfG
--> stefan <--

 

[Al Campagna]

BS,
Use a Totals query.
In query design, select View/Totals from the menubar.
Then set up the grid as follows... (use your own object names)

Field: Product YourDate
Table: YourTable YourTable
Total: GroupBy Last

That should yield the last date for each Product.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[BruceM]

Wouldn't that return a value from the last record in the recordset, which
would not necessarily be the most recent date?

 

[Ken Sheridan]

That's true. The MAX operator should be used, not the LAST operator. More
often than not, however, its necessary to return more than the two columns,
in which case a subquery can be used to restrict the outer query to the rows
with the latest date per product, e.g.

SELECT *
FROM Orders As O1
WHERE ShippedDate =
(SELECT MAX(ShippedDate)
FROM Orders As O2
WHERE O2.ProductID = O1.ProductID);

Ken Sheridan
Stafford, England

 

[Al Campagna]

Ken & Bruce,
Yes... my bad. It should be Max, not Last.
Thanks for catching that.

 

[John W. Vinson]

BS,
Use a Totals query.
In query design, select View/Totals from the menubar.
Then set up the grid as follows... (use your own object names)

Field: Product YourDate
Table: YourTable YourTable
Total: GroupBy Last

That should yield the last date for each Product.

Actually Last is rather misleading: it refers to the last record *IN DISK
STORAGE ORDER*, which might or might not be the latest chronological date. The
Max operator (in place of Last) would get the latest (not last) date.