Solved

Create a Crosstab report in Access by week

Posted on 2010-08-26
4
946 Views
Last Modified: 2013-11-29
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
Comment
Question by:ianmtl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 100 total points
ID: 33536674
You can change the format to "yy ww" to get a week number following the year number.

HTH,
pT72
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 33538634
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
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 150 total points
ID: 33553557
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 33554689
^ 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question