Prev: Date Query
Next: Date expression in Query
From: vanderghast on 15 Apr 2010 08:35
SELECT ( MAX(newDate)-MIN(newDate) ) / (COUNT(*) - 1) , id
FROM table
GROUP BY id


Indeed, the number of date between purchases would be like: (a_2 - a_1)
+ (a_3 - a_2) + ... + (a_n - a_n-1) / ( n-1 )
which become, after simplification: ( a_n - a_1 ) / (n-1)


Vanderghast, Access MVP



"thomasDrew" <thomasDrew(a)discussions.microsoft.com> wrote in message
news:DF02259C-847E-49C1-97E7-5C0820402CDE(a)microsoft.com...
> 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

First  |  Prev  | 
Pages: 1 2
Prev: Date Query
Next: Date expression in Query