Calculate the date interval between all occuring events

[xpsp2-problems]

Hi,
I have an order table with the number of orders by customers vary. Is there
a simple way to generate a table to show all purchase intervals by each
customer and then be able to calculate the average intervals for each
customer?
Your help is much appreciated.
Regards
Cheryl Lee

 

[Allen Browne]

To get the previous OrderDate for the same customer, type a subquery
expression into the Field row of a query, e.g.:
(SELECT Max(OrderDate) AS PriorOrderDate
FROM tblOrder AS Dupe
WHERE Dupe.CustomerID = tblOrder.CustomerID
AND Dupe.OrderDate < tblOrder.OrderDate)

If subqueries are new, here's some more help:
http://allenbrowne.com/subquery-01.html#AnotherRecord

One you get that working, you can use DateDiff() to get the difference
between that and the current order date. Save this query. Then create
another query that calculates the average (by depressing the Total button on
the query design toolbar.)

 

[xpsp2-problems]

Hi,

Thanks for your help. I apply that to my table as the following:

Expr2: (select Max(OrderDate) as PriorOrderDate From 2004-2006order As dupe
Where dupe.customerID=2004-2006order.customerID And
dupe.orderDate<2004-2006order.orderdate)

Yet, I keep getting syntax error. Can you see anything I do wrong here?

I also tested the subquery by playing with the example at the subquery
basics. My data sources can possibly with two orders in the same date. The
Order By cause doesn't seem to get rid of the problem. Any other thought?

 

[Allen Browne]

If your table/field name starts with a number (or contains other characters
such as a space), you must enclose the name in square brackets.

Try:

Expr2: (select Max(OrderDate) as PriorOrderDate
From [2004-2006order] As dupe
Where (dupe.customerID = [2004-2006order].customerID)
And (dupe.orderDate < [2004-2006order].orderdate))

We are assuming your date field really is named OrderDate.

 

[xpsp2-problems]

Strange. It still shows syntax errors, and there's no further information...
Can't see why it won't work.

If your table/field name starts with a number (or contains other characters
such as a space), you must enclose the name in square brackets.

Try:

Expr2: (select Max(OrderDate) as PriorOrderDate
From [2004-2006order] As dupe
Where (dupe.customerID = [2004-2006order].customerID)
And (dupe.orderDate < [2004-2006order].orderdate))

We are assuming your date field really is named OrderDate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Thanks for your help. I apply that to my table as the following:

Expr2: (select Max(OrderDate) as PriorOrderDate
From 2004-2006order As dupe
Where dupe.customerID=2004-2006order.customerID
And dupe.orderDate<2004-2006order.orderdate)

Yet, I keep getting syntax error. Can you see anything I do wrong here?

I also tested the subquery by playing with the example at the subquery
basics. My data sources can possibly with two orders in the same date.
The Order By cause doesn't seem to get rid of the problem. Any other
thought?

 

[John W. Vinson]

Strange. It still shows syntax errors, and there's no further information...
Can't see why it won't work.

Please post the actual SQL view of your entire query.

I have seen strange results when table or fieldnames contain a hyphen
character. I'd avoid using any special characters other than underscore in
names - A to Z, 0 to 9 and _ as the only allowed characters is safe, anything
else requires consistant use of brackets and can make upsizing to SQL/Server a
hassle.