HOW TO MARK THE LAST RECORD IN A GROUP

[NEW2003]

Does anyone know how to mark the last record of a group. ex:
john doe made 3 order, i need to indicate the last record for john doe.

customer order Indicator
john doe 125
john doe 245
john doe 524 1
jane doe 536
jane doe 824 1

 

[jason246day]

You could put a date/time attribute with each order, then search for the most current date/time, which would give you the last order placed.

 

[mikevh]

Do order numbers always increment within customer as
shown in your example, i.e., will a more recent order
for John Doe always have a higher order number than an
earlier order for John Doe? If so, you could say:

UPDATE tblCustomers
SET tblCustomers.[indicator] = "1"
WHERE tblCustomers.ORDER
In (select max(order) from tblCustomers group by Customer);

If you're setting this indicator so that you can retrieve
the most recent order for each customer, then perhaps you
don't really need the indicator at all. The following
will retrieve the most recent order without using any
indicator. (Same idea as the first query, but this time
as a Select query rather than an Update.):

SELECT tblCustomers.*
FROM tblCustomers
WHERE tblCustomers.ORDER
In (select max(order) from tblcustomers group by customer);

 

[NEW2003]

Thanks a lot for all your help!