Calculation of dates within the same column, Access 2010

I need to be able to calculate the difference between two dates within the same column as shown below. I need the results to be returned as a number so that I can show instances where it took longer than 7 days to go from one ID to another.
Example.xlsx
SheriAMorganProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
You will need this
DateDifference :DateDiff("d",DMin("AUDIT_TIMESTAMP","YourtableName","ID < " & [ID]),[AUDIT_TIMESTAMP]) AS DifferenceIndates

Open in new window

In order for this to work you will need to order your dates ascending and also add a column that will have consecutive numbers e.g ID
So your table would be like this

ID      AUDIT_TIMESTAMP      INVESTIGATOR_ID
1           4/8/2015                           BIG00
2           5/8/2015                           V0000
3           7/8/2015                           A2076
4         11/8/2015                          A2076

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Using a domain function in a query is a very expensive proposition.  The suggested expression will not work if there are multiple entries for a single day.  

If the data is input in order, you could calculate the difference as each new record is entered.  However, this would be a problem in a very busy application with multiple simultaneous users adding records.  It would also be a problem if a mistake was made and the data was entered out of order.  There must be some other column that you are not telling us about that defines a set of data.

A report is a better place to calculate the difference or using a recordset to loop through all records.
John TsioumprisSoftware & Systems EngineerCommented:
Multiple Entries For a single day will result to 0 days difference which is normal...there was not indication that there was need for time difference....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.