Hello "slotmgr70".

"slotmgr70" wrote:
I have a table that tracks each customers visit to my store. I hold
promotions on occaision that bring customers that haven't visited my
store in awhile. When I hold a promotion, I'm trying to determine
the number of days since the last visit for each customer.
For example, here is a sample customer:

Account_No Visit_Date
1234 4/19/10
1234 4/10/10
1234 3/10/10
1234 2/13/10

This customer generally visits once a month. Assuming that I held a
promotion on 4/19/10, I want to calculate the number of days since
the most recent visit, in this case being 4/10/10 (9 days).

The goal is to see if I have affected their trip pattern by holding
the promotion and if I was able to generate an incremental trip by
holding the promotion.

You probably mean something like this?

SELECT tblVisits.Account_No, tblVisits.Visit_Date, DateDiff("d",
(SELECT Max(tmp.Visit_Date) From tblVisits AS tmp WHERE tmp.Account_No
= tblVisits.Account_No AND tmp.Visit_Date tblVisits.Visit_Date),
tblVisits.Visit_Date) AS Difference
FROM tblVisits
ORDER BY tblVisits.Account_No, tblVisits.Visit_Date DESC;

--
Best regards,
Wolfgang