[SOLVED] Access - How to calculate average days between orders (without a cursor)

Access - How to calculate average days between orders (without a cursor)

I want to create a query in access returning the average days between orders. The table structure is: Table name:

 Orders

table columns:

idOrder (integer)
 idClient (integer)
 OrderDate (date in format dd/mm/yyyy)
 OrderAmount (money)

A simple query to the table would return this:

1 1131 01/01/2015 $235
2 1145 02/01/2015 $11
3 1131 03/01/2015 $256
4 1131 04/01/2015 $300
5 1146 05/01/2015 $25
6 1145 10/01/2015 $15
7 1145 20/01/2015 $4
8 1131 21/01/2015 $45

in this example,

client 1131 has orders on the days: 01/01/2015; 03/01/2015; 04/01/2015; 21/01/2015
client 1145 has orders on the days: 02/01/2015; 10/01/2015; 20/01/2015
client 1146 has orders on days: 05/01/2015

i would like to create a query returning for each client the average days between orders:

1131 5
1145 6
1146 null

If it would be sql server, I would use a cursor, but in Access, i dont know how to do it.

Any help?

Solution

You wouldn't need a cursor in SQL Server. Having said that, try the below query. If a customer placed one order, logically the result should be 0 (zero), not null. Null should reflect any customers that did no place any orders.

SELECT Orders.idClient, (Max(Orders.OrderDate)-Min(Orders.OrderDate))/Count(*) AS [Days between orders]
FROM Orders
GROUP BY Orders.idClient