By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

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

P: 2
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.

Mar 25 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 931
Can you post your table fields and a little bit of sample data?

Mar 25 '10 #2

P: 2
I have attached a copy of headings and some sample data both in txt and a zipped excel
Thanks for your help.
Attached Files
File Type: zip example (5.0 KB, 95 views)
File Type: txt sample data.txt (1.8 KB, 356 views)
Mar 26 '10 #3

Expert 100+
P: 931

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?

Mar 26 '10 #4

Expert Mod 15k+
P: 31,489
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.
Mar 26 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.