Subtract dates in current record from last record in relation to another field

[steve75043]

Hello I hope someone can help with this. I need to subtract dates in current record from previous record for the current store.
The key fields in the form are customer, store number and date picked up.
I have used a query to put all records in order of consumer, store number and date picked up and I am using a form to view it. I can use =max([date picked up])-[date picked up] to do the subtraction but I need to only be looking at like store numbers for each max calculation without having to filter. For example if I want to look at all store customer “A” this method does not work because there are many different store number with different last pu dates.

It is a large data base so the simpler this can be made the better. I should be able to tie the max statement to the store number field. I just don’t know how. Also this is access 2003.

Thanks
Steve

[patjones]

Can you post your table fields and a little bit of sample data?

Pat

[steve75043]

copy of headings and some sample data

Pat,
I have attached a copy of headings and some sample data both in txt and a zipped excel
Thanks for your help.

[patjones]

Thanks.

So for instance, for Company D, Store 541, we have

COMPANY D 541 25-Nov-09
COMPANY D 541 07-Dec-09
COMPANY D 541 21-Dec-09
COMPANY D 541 22-Dec-09
COMPANY D 541 04-Jan-10

It sounds like what you want to do is pull the two most recent dates and calculate the difference between them in days. So in this example we would have

COMPANY D 541 13 (days)

Is this what you're looking for?

Pat

[NeoPa]

Using DMax() could be made to work if the correct criteria were applied, however this would not be an efficient approach.

I would recommend linking the table to a subquery where Max([Date Picked Up]) is worked out and grouped by the [Store Number] field or whatever you need to group by. That way, your resulting query will have the [Date Picked Up] value, alongside the Max([Date Picked Up]). All handled efficiently by SQL and easy as pie to use.