Get Data for Multiple Dates in Access Query

Question

Here's hoping I can explain this problem and someone can help me....

So I am trying to pull a query in access for a subform on a report, and this is what I want to be able to do (whether or not it is possible is an entirely different story...) See below for a chart with the column names

Market	CouponName	Date	Tactic	Type	Segment	WeekPrior	CurrentWeek	FollowingWeek	WeekAfterThat
ABC	123-456	5/2/17	RPW	Agency...	Dinner	0	98	202	100

What I am having problems with is in the bold and italicized columns. In these columns are the total coupon redemptions for each week. I can easily pull the data for the CurrentWeek column, but the other three are where it gets tricky. I want to be able to get the redemption data from the week before and the two weeks after the week in question for this tactic in this market. Currently, the query is pulling from a single table which looks similar to this: 

Market	CouponName	Tactic	CouponType	Segment	Date	TotalRedeemed
ABC	123-456	RPW	Agency...	Dinner	5/2/17	98
ABC	123-456	RPW	Agency...	Dinner	4/25/17	0
ABC	123-456	RPW	Agency...	Dinner	5/9/17	202

Is there any way for me to display the data on a query the way I want to? 

If there are any questions or if my explanation was unclear, please let me know so I can clarify. 

Thanks in advance!

Answer 1

I assume all the dates are Tuesdays, and you wish to correlate the previous, following and week-after-that values on Market, CouponName, Tactic, Type and Segment.  Try this:

SELECT Market, CouponName, [Date], Tactic, [Type], Segment,

    (SELECT TotalRedeemed

      FROM TheTable As T2

      WHERE T2.Market = T1.Market

      AND T2.CouponName = T1.CouponName

      AND T2.Tactic = T1.Tactic

      AND T2.[Type] = T1.[Type]

      AND T2.Segment = T1.Segment

      AND T2.[Date] = T1.[Date]-7) AS WeekPrior,

TotalRedeemed AS CurrentWeek,

    (SELECT TotalRedeemed

      FROM TheTable As T2

      WHERE T2.Market = T1.Market

      AND T2.CouponName = T1.CouponName

      AND T2.Tactic = T1.Tactic

      AND T2.[Type] = T1.[Type]

      AND T2.Segment = T1.Segment

      AND T2.[Date] = T1.[Date]+7) AS FollowingWeek,

    (SELECT TotalRedeemed

      FROM TheTable As T3

      WHERE T3.Market = T1.Market

      AND T3.CouponName = T1.CouponName

      AND T3.Tactic = T1.Tactic

      AND T3.[Type] = T1.[Type]

      AND T3.Segment = T1.Segment

      AND T3.[Date] = T1.[Date]+14) AS WeekAfterThat

FROM TheTable As T2;

If the dates are not all Tuesdays, then you can return a week-starting date for any Date with the following function:

Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)

    ' Returns 'week starting' Date for any Date

    ' Arguments:

    ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)

    ' 2. varDate - optional Date value for which week starting

    '   Date to be returned.  Defaults to current date

    If IsMissing(varDate) Then varDate = VBA.Date

    If Not IsNull(varDate) Then

        WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1

    End If

End Function

and correlate the subqueries with the outer query on the return value of the function, e.g.

      AND WeekStart(1,T3.[Date]) = WeekStart(1,T1.[Date]+14)) AS WeekAfterThat

I would recommend against using Date and Type as column names.  These are the names of a function and statement respectively in Access and as such, are 'reserved words'.  This is why I've enclosed them in square brackets in the SQL statement.  However they are best avoided as object names.

Answer 2

Mea culpa!  The final line of the query should have been:

    FROM tblDinnerRedemptions As T1;

Answer 3

How would I filter that, in the criteria line? Because when I tried that, nothing showed up...

Answer 4

So when I tried this, I got popups asking me to enter a parameter value...when I clicked OK on them all, it took me to the query, but the WeekPrior, Following Week, and WeekAfterThat fields were all blank...when I tried to fill in the parameters, I got an error message telling me the query was too complex. 

For this particular company, the week end is always on tuesday, so the date will always be a tuesday date. 

And you are right, the naming thing totally slipped my mind! Ive renamed the Date field IHW (In Home Week) and the Type field CouponType

Below is the code as I have it...

SELECT Market, Name, IHW, Tactic, CouponType, Segment,

    (SELECT TotalRedeemed

      FROM tblDinnerRedemptions As T2

      WHERE T2.Market = T1.Market

      AND T2.Name = T1.Name

      AND T2.Tactic = T1.Tactic

      AND T2.CouponType = T1.CouponType

      AND T2.Segment = T1.Segment

      AND T2.IHW = T1.IHW-7) AS WeekPrior,

TotalRedeemed AS CurrentWeek,

    (SELECT TotalRedeemed

      FROM tblDinnerRedemptions As T2

      WHERE T2.Market = T1.Market

      AND T2.Name = T1.Name

      AND T2.Tactic = T1.Tactic

      AND T2.CouponType = T1.CouponType

      AND T2.Segment = T1.Segment

      AND T2.IHW = T1.IHW+7) AS FollowingWeek,

    (SELECT TotalRedeemed

      FROM tblDinnerRedemptions As T3

      WHERE T3.Market = T1.Market

      AND T3.Name = T1.Name

      AND T3.Tactic = T1.Tactic

      AND T3.CouponType = T1.CouponType

      AND T3.Segment = T1.Segment

      AND T3.IHW = T1.IHW+14) AS WeekAfterThat

FROM tblDinnerRedemptions As T2;

Answer 5

Create a query that filters the date range for BETWEEN Date()-7 AND Date()+14

Run that through a Crosstab query with the Market and coupon Name as the rows and  The date as the columns.