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?
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