473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Mar 25 '10 #1
4 3124
patjones
931 Expert 512MB
Can you post your table fields and a little bit of sample data?

Pat
Mar 25 '10 #2
Pat,
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 data.zip (5.0 KB, 128 views)
File Type: txt sample data.txt (1.8 KB, 469 views)
Mar 26 '10 #3
patjones
931 Expert 512MB
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
Mar 26 '10 #4
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

8
by: Zlatko Matić | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
15
by: Hi5 | last post by:
Hi, I am designing a database for a client in which It has a client table including the followings: 1-Table Client 2-Table lookupcategory 3-Table Ctegory
36
by: Lindie | last post by:
The more I read the more confused I get. Too much on dates calulations in the groups. I need to know how often a book has been loaned out over the past year- 52 weeks. My table has Book...
6
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
3
by: Christopher Weaver | last post by:
I want to set a value in a specific field in the current row of a DataSet. This seems like the most basic thing to do but I can't find the syntax for identifying the current row. IOW, I can do...
8
by: Remington | last post by:
I am using windows 2000pro with access 2000. I am trying to make a database for our HR department, that would allow our HR Director to type in an employee's ID number into a form and then select...
1
by: roveagh1 | last post by:
Hi I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
10
by: dan | last post by:
Am i breaking any rules when I loop dates like // Determine Memorial Day intFlag = 0; memDayHol = new Date (currentYear, 4, 31); while (intFlag == 0) { if (memDayHol.getDay() == 1) {intFlag...
6
by: lptl | last post by:
I know the title is misleading. I am working on a class project where we are trying to setup a notification system written in PHP and using an MySQL database. We are trying to set-up a notification...
10
tuxalot
by: tuxalot | last post by:
I have a table (TblDateHistory) to record lost days due to injuries: EmployeeID StartDate ReturnDate RestrictedOrLostDays TotalDays 1 ... ... Lost Time...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.