B
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?
product field and date field. How do I do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Stefan Hoffmann
hi,
grouping function (press the Greek S, sum symbol) and assign the
function Max to your date field.
mfG
--> stefan <--
Either use a query, add your table and these two fields, activate theIn 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?
grouping function (press the Greek S, sum symbol) and assign the
function Max to your date field.
mfG
--> stefan <--
A
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."
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."
B
BruceM
Wouldn't that return a value from the last record in the recordset, which
would not necessarily be the most recent date?
would not necessarily be the most recent date?
K
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
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
A
Al Campagna
Ken & Bruce,
Yes... my bad. It should be Max, not Last.
Thanks for catching that.
Yes... my bad. It should be Max, not Last.
Thanks for catching that.
J
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.