Getting last order for each Product ID and Cust ID

[Ramesh]

Hi,

I use the following query to get the last order of each Cust ID with help
from Allen Browne's tips site. Now my requirement is slightly different in
the sense, i need to have last order for each Cust ID for each Product ID
too. If same customer has ordered 2 products, then i need to get the last
order for each of the products.

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 1 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

Could someone help in including the Product ID too? I am not too familiar
with sql code, just beginning learn.

Thanks very much for any help.
Ramesh

 

[Allen Browne]

Presumably you have an OrderDetail table (where each order's line items go),
and the ProductID is in that table?

1. Create a query that uses both the Orders and OrderID fields.

2. Depress the Total button on the toolbar (upper case Sigma icon.)
Access adds a Total row to the grid.

3. Drag CustomerID into the grid.
Accept Group By in the Total row under this field.

4. Drag ProductID into the grid.
Accept Group By again.

5. Drag OrderDate into the grid.
Choose Max under this field.

That gives you each combination of Customer and Product, with the lastest
order date for that combination.

 

[Ramesh]

Sorry Allen i didnt give the picture right. I was trying to use the Order
scenario to work out another scenario.

The actual situation i have is a training setup. I have a BatchInfo table
with BatchNo, SubjectID, etc. Batchstudents table with JoinNo, StuNo,
BatchNo, JoinDate. Students are likely to shift between batches for the
same subject and also be simultaneously be in 2 batches for 2 different
subjects .

Now I need a query which gives me the StuNo and latest BatchNo. a student
doing one subject will have one batch no, one doing 2 simultaneous subjects
will have 2 batch nos. The solution below works only for the one subject
scenario but not 2 simultaneous subjects.

Sorry for the confusion.

Ramesh

 

[Allen Browne]

Whatever the scenario, you need multiple *records* if a student is enrolled
in more than one subject at a time, not multiple *fields* in the same table.

I'm not clear how you decide which are the current members of the batch.
Perhaps you have a Batch table which identifies which records are current,
and you can get all the current admissions just by setting criteria on that
table.

Or if the latest matching batch number is always the current one, you could
use a subquery to choose that batch number. The subquery would go in the
WHERE clause.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Ramesh]

Thanks for your response Allen.

I do have multiple records for each subject. If student A enrols in Subject
A and B, he would have two records, each with a different batch number. Now
problem is that this student may move from one batch to another for the same
subject .. say from the morning batch to the evening batch. Whenever he
moves, I will have one more record for that with the date of moving. Hence
at any time, if I need to have the current batches each student is in, I
need to get to the record with the latest date for each combination of
Student and Subject. There are no other tables identifying current records.
Am only going by the latest date. I do have another table for Batch info
which says what Subject is taught in that batch.

The fields in the StudentsBatch table are JoinNo (key), StuID, Batch ID,
JoinDate.
The fields in the BatchInfo table are BatchID, SubjectID, TutorID

Hope am being clearer this time.

Thanks for your guidance.

Ramesh

 

[Allen Browne]

The Group By query suggested in the first reply should work. Add whatever
tables you need to the query. Group By StudentID and SubjectID, and choose
Max under BatchDate.

Here's another article explaining 4 more ways to do that:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

I'm not really clear, but perhaps the problem here is that your structure
has redundant data. If the SubjectID is in the BatchInfo table only, that's
fine, but if it is in another table as well, then it seems things could go
wrong. You could end up with a record in another table where for the batch,
but the SubjectID in that table doesn't match the SubjectID in the batch
table. If you don't have this redundancy, just ignore this paragraph.

 

[Ramesh]

DONE IT!!

Got the mistake i was making from your message and the clue from the example
in the article. I just had to take out the batchID from the subquery.
Another thing was joining the JoinDate and MaxJoinDate. I didnt know you
could have 2 parallel joins.

Wow .. this is getting exciting. Am actually not much of a software or
systems person, but I am finding access so awesome and am doing more of
Access than my work.

Thanks
Ramesh

 

[Allen Browne]

Well done. It is very fulfilling when you crack the solution.