• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1307
  • Last Modified:

Create a Crosstab report in Access by week

I would like to create a crosstab query by week. Presently I am doing it by month and changing the date format to "mmm yy". Is there a way to setup the crosstab query by week?
0
ianmtl
Asked:
ianmtl
  • 2
4 Solutions
 
pteranodon72Commented:
You can change the format to "yy ww" to get a week number following the year number.

HTH,
pT72
0
 
Jeffrey CoachmanMIS LiasonCommented:
Or create a query than contains the Week number and use that as the source for the Crosstab report.

SELECT tblDates.EmployeeID, tblDates.OrderDate, tblDates.Freight, Format([OrderDate],"ww") AS TheWeek
FROM tblDates;

TRANSFORM Sum(qryXtabSRC.Freight) AS SumOfFreight
SELECT qryXtabSRC.EmployeeID
FROM qryXtabSRC
GROUP BY qryXtabSRC.EmployeeID
PIVOT "Week " & [TheWeek];


Here is a basic sample:

;-)

JeffCoachman
Access-EEQ26432757-CrossTabQuery.mdb
0
 
Dale FyeCommented:
Your challenge will be getting the columns in the correct sequence.

Either of the methods mentioned above will work if you are limiting the results to a single year, but only pteranodon72's method will work if your data crosses multiple years.

Another way to do this would be to use another table to generate dates in a query, and join that query to your query.  To do this, start with a table (tbl_Numbers) with a single field (intNumber) and 10 records (0, 1, 2, ...9).

You, depending on how many weeks you want to generate, you would create a query (qry_Numbers) which generates a sequence of numbers from 0 to N.  In the case below, the query creates 1000 values (0 -999).

SELECT Hundreds.intNumber * 100 + Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

The next step is to generate a series of dates, this query will return dates in a range from [StartDate] to [EndDate].

Parameters [Start] date, [End] date;
SELECT DateAdd("d", intNumber * 7, [Start]) as WeekStart,
             DateAdd("d", intNumber * 7 + 6, [Start]) as WeekEnd
FROM qry_Numbers
WHERE DateAdd("d", intNumber * 7, [Start]) BETWEEN [Start] AND [End]

Once you have this query working, post back with the name of your table and fields, and I'll provide guidance on the next step.
0
 
Jeffrey CoachmanMIS LiasonCommented:
^ Yes, my presumption was that the data would be filtered down to one year. (to avoid ending up with a query that contains more than 53 columns)

If the month and year would be needed, then yes, the year would have to be taken into consideration.
;-)

Another alternative might be to use a Pivot Table.
Here you would pull the Year and Month out of the raw data:
(converting them to integers so that they sort correctly)

SELECT tblDates.EmployeeID, tblDates.OrderDate, tblDates.Freight, CInt(Year([OrderDate])) AS TheYear, CInt(Format([OrderDate],"ww")) AS TheWeek
FROM tblDates
ORDER BY tblDates.OrderDate;

Then change the query View to "Pivot Table"

Then simply drag and drop Year and Month from the field list box into the Column headings, and then drag and drop your Other field in the Row headings.
Then drop your "Summary" Field in between them

Right click a value in the Pivot Table and move to "Auto Calc", then select your summary (sum, Average, ...etc)  

Now with a Pivot table you you will get not only the grand totals, but yearly totals as well.
You also have the option to expand items in the row or column headings.
For example: you can see all the months, or simply "roll them up" into their respective years.

A little History:
Pivot tables were originally only available in Excel, to give Excel users an easy (Interactive, Drag and drop) way to replicate Crosstab queries in SQL/Access.
Since then, they have been included with MS Access.
So using them if you need multiple levels in your column (or Row) headings is a common suggestion by the Experts here.

One interesting note is that a Crosstab query has a max output of 255 columns.
A Pivot table (at least in Excel) can output a maximum of 32,000 columns
Not that either of those Maximums are even applicable here...
;-)
...just something interesting I looked up
;-)

As always, it is best to know more than one way to solve an issues.

New sample is attached.

;-)

JeffCoachman

Access-EEQ26432757-CrossTabQuery.mdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now