max function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I wanted to do a query in Access where I can get the latest purchased date
of an item, the last item the customer purchased and the customer's
identifier(which is all on one table). I have a whole list of customers .
It worked when I did the max function with the customer's identifier and the
latest purchase date. But when I did a separate query with the customer and
the latest item that the customer purchased it somehow retrieves the item
alphabetically closest to the letter Z.
An example of item names: Book, Frame, Paper, Staples. Access will
retrieve "Staples" as the max item even though it does not match with the
customer's last purchase date.
Is there a way to solve this problem? Also is there a way to do it at one
time, or do I have to do it in two queries?
Hope to hear from someone soon.
 
Use a coordinated sub-query.


SELECT CustomerID, PurchaseDate, Item
FROM YourTable
WHERE PurchaseDate =
(Select Max(PurchaseDate)
FROM YourTable As Temp
WHERE Temp.CustomerID = YourTable.CustomerID)

This may have some problems for you. If there is more than one item purchased on
the relevant date then you will get multiple records returned for the date and customerid.
 
tsuru said:
Hi.

I wanted to do a query in Access where I can get the latest purchased date
of an item, the last item the customer purchased and the customer's
identifier(which is all on one table). I have a whole list of customers .
It worked when I did the max function with the customer's identifier and the
latest purchase date. But when I did a separate query with the customer and
the latest item that the customer purchased it somehow retrieves the item
alphabetically closest to the letter Z.
An example of item names: Book, Frame, Paper, Staples. Access will
retrieve "Staples" as the max item even though it does not match with the
customer's last purchase date.
Is there a way to solve this problem? Also is there a way to do it at one
time, or do I have to do it in two queries?
Hope to hear from someone soon.
Click to expand...

(Note: By the time I wrote this, I now see there is an early post from
John Spencer that is pretty much the same answer, but here is my post
anyway.)


CREATE TABLE YourTable_02112005_1
(CustomerID LONG NOT NULL
,ItemName TEXT(48)
,PurchaseDate DATETIME
,CONSTRAINT pk_YourTable_02112005_1
PRIMARY KEY (CustomerID, ItemName, PurchaseDate)
)

Sample Data

CustomerID ItemName PurchaseDate
1 Book 01/01/05
1 Book 01/02/05
1 Paper 01/03/05
1 Staples 01/04/05
2 Book 01/01/05
2 Frame 01/01/05
2 Paper 01/01/05
2 Staples 01/05/05


SELECT Y1.CustomerID
,Y1.ItemName
,Y1.PurchaseDate
FROM YourTable_02112005_1 AS Y1
WHERE Y1.PurchaseDate =
(SELECT MAX(Y01.PurchaseDate)
FROM YourTable_02112005_1 AS Y01
WHERE Y1.CustomerID = Y01.CustomerId)

This Returns:

1 Staples 01/04/05
2 Staples 01/05/05

Which, in this case, happens to be the last items purchased by these
Customers.


Sincerely,

Chris O.
 
Back
Top