?
Solved

Create a Crosstab report in Access by week

Posted on 2010-08-26
4
Medium Priority
?
1,122 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
  • 2
4 Comments
 
LVL 14

Assisted Solution

by:pteranodon72
pteranodon72 earned 400 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 1000 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 49

Accepted Solution

by:
Dale Fye earned 600 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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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