Solved

Create a Crosstab report in Access by week

Posted on 2010-08-26
4
932 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 47

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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