Calculate days between purchases

[aalves]

Hi,
I have a database which each entry is one purchase per one client. Now I want to understand how long each client takes between one purchase and another. Can anyone help me?

Example of the table:

Client ID / Date of purchase
Client 1 / 01/jan
Client 1 / 15/jan
Client 1 / 20/jan
Client 2 / 03/mar

In this example I wanted to know that Client 1 took 15 days from the first purchase and 5 days from the second to come back.

The idea is to make an histogram telling me how many clients take from 0-5 days, from 5-10 days, and so on.
Any ideas?

Thanks a lot!!

 

[apr pillai]

If you have an Autonumber field in this table then we can solve this issue. Go through the following link for details:
Finding Difference between Dates in rows of a Column | LEARN MS-ACCESS TIPS AND TRICKS

 

[Micron]

I figured there must be a way to do this with a subquery, but I confess I do struggle with those as they're often quite complicated for what I get into (for me, anyway).
If I put the sample data into a table called tblPurchDate and use the following, I get the first order date for a client versus the other order dates WHERE a client has placed more than one order. Thus client2 returns nothing. Perhaps it is a start. The result is below the sql statement.

SELECT tblDateSpan.ClientID, tblDateSpan.PurchDate, T.PurchDate
FROM tblDateSpan INNER JOIN tblDateSpan AS T ON tblDateSpan.ClientID = T.ClientID
WHERE (((T.PurchDate)>(SELECT Min([PurchDate]) FROM tblDateSpan AS PD WHERE tblDateSpan.PurchDate<(T.PurchDate))));

ClientID	tblDateSpan.PurchDate	T.PurchDate
Client 1	1/1/2016	1/15/2016
Client 1	1/1/2016	1/20/2016
Client 1	1/15/2016	1/20/2016

<tbody>
</tbody>

This is about as far as I can take this. Maybe the initial results can feed a crosstab or totals query to refine it.

 

[james_lankford]

yeah
I'm doing something similar for work

look at all patients and med orders over the last 6 months and find all patients who were prescribed 3 or more orders of a group of drugs within a 2 hour period

don't see anyway to do it except pull the entire recordset in and loop over it with vba