Solved

Create a Crosstab report in Access by week

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now