Prev: Help on muliple table queries
Next: Joining 3 Queries That Count
From: slotmgr70 on 28 May 2010 07:45
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.
From: Allen Browne on 28 May 2010 09:33
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" <slotmgr70(a)discussions.microsoft.com> wrote in message
news:1FFB50BC-9FBC-4A05-8503-4F1B5D45E9DF(a)microsoft.com...
> 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.

From: Wolfgang Kais on 28 May 2010 09:58
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


 | 
Pages: 1
Prev: Help on muliple table queries
Next: Joining 3 Queries That Count