Finding the difference between the dates of two records

[jamesborne]

Dear Access buddies,



I have a query consisting of the following:
-Date
-Customer
-OrderAmount

I am trying to have another field called "average days between order". This field should basically find the difference between each customer's orderdates.
i.e the difference in days between the last order date and the one before (for each customer).

To make things clearer:
1. Find the difference between the dates of order [X] and order [X+1] from each customer.

Thanks in advance,
James Borne

[Robeen]

The previous order and the one before that - will be on two different rows in the Table - right?

[jamesborne]

Robeen, that's correct.

[Robeen]

Google 'Top N Values per Group Query'.

What this lets you do is isolate one [or more] record per customer - depending on how you sort the records.

Also . . .

You can also look at this query example [that I haven't tested] to pull the second highest value per CustomerID.

select CustomerID
, max(Amount) as SecondMaxAmount
from Sales S
where Amount <
( select max(AMount)
from Sales
where CustomerID = S.CustomerID )
group
by CustomerID

You will have to play with these two methods.

What I would do is create a query that gives you the Max(DateField) and then another query that gives you the SecondMax [like example above].
Now create a third query that uses the data from both these queries and you will get the Max for each Customer from the first query and the SecondMax for each Costomer from the second query - and bot of them will be in the same row of data [join the CustomerID fields in the two queries in design view].
Then - in your third query - you can create a new field that uses the 'DateDiff()' function to find the difference between the two dates.

[jamesborne]

Quite frankly, you're not helping at all. Let me be make it easier:

I have 2 fields.
A: Date
B: Customer

Now I want to create a third field [datedifference], that will have the difference between the dates for each customer. Do you get that?
Example:
ID Date Customer DateDifference
1 1/1/2012 Einstein --
2 3/1/2012 Davinci --
3 5/1/2012 Einstein 4
4 9/1/2012 Davinci 6


Thanks for your help in advance,
James Borne

[ssanfu]

Quite frankly, James, you're being rude. Robeen was trying to be helpful. Maybe he misunderstood, but he was still trying. Maybe your explanation was unintelligible??

It sounds like what you want to do is akin to calculating gas milage. Here are 3 sites to check out:

http://www.rogersaccesslibrary.com/forum/topic445.html

http://www.rogersaccesslibrary.com/forum/topic516.html

http://bytes.com/topic/access/answer...lc-gas-mileage

http://www.utteraccess.com/forum/Gas...-t1211144.html


Good luck