Prev: Date Query
Next: Date expression in Query
From: thomasDrew on 14 Apr 2010 11:33
Table is organized like this:

Date Purchase Quantity
01/02/2010 12
01/23/2010 45
05/04/2010 10
07/06/2010 5
....

How do I calculate the average time between purchase?

Thank you for any help you can provide.

Drew Yallop

--
Drew Yallop
From: Jerry Whittle on 14 Apr 2010 11:56
SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
FROM Yallop;

BTW: Date is a very bad name for a field or table. It's a reserved word and
can cause problems if you forget to put the [ ] around it. Read more about
reserved words at:

http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"thomasDrew" wrote:

> Table is organized like this:
>
> Date Purchase Quantity
> 01/02/2010 12
> 01/23/2010 45
> 05/04/2010 10
> 07/06/2010 5
> ...
>
> How do I calculate the average time between purchase?
>
> Thank you for any help you can provide.
>
> Drew Yallop
>
> --
> Drew Yallop
From: Stefan Hoffmann on 14 Apr 2010 12:02
hi Thomas,

On 14.04.2010 17:33, thomasDrew wrote:
> Table is organized like this:
>
> Date Purchase Quantity
> 01/02/2010 12
> 01/23/2010 45
> 05/04/2010 10
> 07/06/2010 5
> ...
>
> How do I calculate the average time between purchase?
Use this helper query to calculate the previous date:

SELECT
O.[Date],
(SELECT TOP 1 Max(I.[Date])
FROM yourTable I
WHERE I.[Date] < O.[Date]
ORDER BY Max(I.[Date])
) AS PreviousDate
FROM yourTable O
ORDER BY O.[Date];

You should consider renaming your [Date] column.


mfG
--> stefan <--
From: KenSheridan via AccessMonster.com on 14 Apr 2010 13:50
Try this:

SELECT AVG(Interval)
AS AverageInterval
FROM
(SELECT P1.Date, P1.Date -
(SELECT MAX(P2.Date)
FROM Purchases as P2
WHERE P2.Date < P1.Date) AS Interval
FROM Purchases AS P1);

where Purchases is the table name.

Note the caveats the others have expressed regarding the use of Date as a
column name. PurchaseDate would be better.

Ken Sheridan
Stafford, England

thomasDrew wrote:
>Table is organized like this:
>
>Date Purchase Quantity
>01/02/2010 12
>01/23/2010 45
>05/04/2010 10
>07/06/2010 5
>...
>
>How do I calculate the average time between purchase?
>
>Thank you for any help you can provide.
>
>Drew Yallop
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: thomasDrew on 15 Apr 2010 01:29
Thank you for your quick reply Unfortunately my question was ill-formed.

The table has an additional field - ID. So the table looks like this:

ID NewDate Purchase Quantity
1 01/02/2010 12
1 01/23/2010 45
1 05/04/2010 10
1 07/06/2010 5
2 03/020/2010 6
2 05/01/2010 8

I want to calculate iaverage nterpurchase times for each ID.

Best,
Drew Yallop

1
--
Drew Yallop


"Jerry Whittle" wrote:

> SELECT Avg([Date]-DMin("[Date]","Yallop")) AS AvgDaysBetweenPurchases
> FROM Yallop;
>
> BTW: Date is a very bad name for a field or table. It's a reserved word and
> can cause problems if you forget to put the [ ] around it. Read more about
> reserved words at:
>
> http://support.microsoft.com/kb/286335/
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "thomasDrew" wrote:
>
> > Table is organized like this:
> >
> > Date Purchase Quantity
> > 01/02/2010 12
> > 01/23/2010 45
> > 05/04/2010 10
> > 07/06/2010 5
> > ...
> >
> > How do I calculate the average time between purchase?
> >
> > Thank you for any help you can provide.
> >
> > Drew Yallop
> >
> > --
> > Drew Yallop
 |  Next  |  Last
Pages: 1 2
Prev: Date Query
Next: Date expression in Query