If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Next to Last Date
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. |
#2
|
|||
|
|||
Next to Last Date
Time to learn about subqueries. Here's an intro:
http://allenbrowne.com/subquery-01.html You will end up typing an expression like this into a fresh column in the Field row in query design: (SELECT Max([Visit_Date]) AS PriorVisit FROM [Table1] AS Dupe WHERE (Dupe.[Account_No] = [Table1].[Account_No]) AND (Dupe.[Visit_Date] [Table1].[Visit_Date])) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "slotmgr70" wrote in message ... 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. |
#3
|
|||
|
|||
Next to Last Date
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 |
Thread Tools | |
Display Modes | |
|
|