Date Range Report

[seth.murphine]

With the attached db, how would I create a report that puts each dlDate across the page header if I put in a date range while cross tabbing visually dlTBAA, dlTBAC and dlTBNC down the side with each of their date totals for the dates across the top?reports.accdb

[June7]

Would have been nice to test with data, even dummy values.

See if this gets you somewhere:

Query1: yp_UNION, there is no wizard or designer for UNION, must type in the SQL View of query designer.
SELECT ID, "TBAA" As Type, dlDate, dlTBAA As dlData FROM yp_team_dl
UNION SELECT ID, "TBAC", dlDate, dlTBAC FROM yp_team_dl
UNION SELECT ID, "TBNC", dlDate, dlTBNC FROM yp_team_dl;

Query2:
TRANSFORM Sum(yp_UNION.dlData) AS SumOfdlData
SELECT yp_UNION.Type
FROM yp_UNION
WHERE (((yp_UNION.dlDate) Between #1/1/2012# And #1/3/2012#))
GROUP BY yp_UNION.Type
PIVOT yp_UNION.dlDate;

[seth.murphine]

So theoretically this should give me the query to pull a report that looks like the following?http://prntscr.com/8fv6k

[June7]

Try:
SELECT ID, "TBAA" AS Type, "Week" & " " & Format(DatePart("ww",[dlDate]),"00") & " " & Year([dlDate]) AS WkYr, dlDate, dlTBAA AS dlData FROM yp_team_dl
UNION SELECT ID, "TBAC", "Week" & " " & Format(DatePart("ww",[dlDate]),"00") & " " & Year([dlDate]) AS WkYr, dlDate, dlTBAC FROM yp_team_dl
UNION SELECT ID, "TBNC", "Week" & " " & Format(DatePart("ww",[dlDate]),"00") & " " & Year([dlDate]) AS WkYr, dlDate, dlTBNC FROM yp_team_dl;

TRANSFORM Sum(yp_UNION.dlData) AS SumOfdlData
SELECT yp_UNION.Type
FROM yp_UNION
WHERE (((yp_UNION.dlDate) Between #1/1/2012# And #1/3/2012#))
GROUP BY yp_UNION.Type
PIVOT yp_UNION.WkYr;