cross tab query - group by week

  • Thread starter Thread starter Helen
  • Start date Start date
H

Helen

Hello,

I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");

Any suggestions?

Thanks in advance.

Helen
 
Helen said:
Hello,

I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");
Click to expand...


Try using:

PIVOT "Wk " & Format([OrderDate],"ww", 2)
 
Hi there,

It's works! :-) Follow up question;

Is there a way to have it grouped by week as it does now, but the column
heading is the first date of that week, instead of wk 01, wk 02, it would
say Jan 03, Jan 10 etc...?

And what day is it considering the first day of the week? Sunday or Monday?

Thanks!

Helen


Marshall Barton said:
Helen said:
Hello,

I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");
Click to expand...


Try using:

PIVOT "Wk " & Format([OrderDate],"ww", 2)
Click to expand...
 
The Format function's FirstDayOfWeek argument I used is a 2,
which is Monday.

As for displaying dates instead of week numbers, I may be
kind of foggy today, but I don't see a way to do that.
Maybe someone else knows how to do it??
--
Marsh
MVP [MS Access]

It's works! :-) Follow up question;

Is there a way to have it grouped by week as it does now, but the column
heading is the first date of that week, instead of wk 01, wk 02, it would
say Jan 03, Jan 10 etc...?

And what day is it considering the first day of the week? Sunday or Monday?

Helen said:
I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");
Click to expand...

"Marshall Barton" wrote
Try using:
PIVOT "Wk " & Format([OrderDate],"ww", 2)
Click to expand...
Click to expand...
 
Ah. I understand.

Just came across another question;

Currently it's sorting by the first number in the week; wk 1, wk 11, wk
12... then wk 2, wk 20, wk 21... wk 3, wk 30, wk 31... etc...

Is there a way to sort it by; wk 1, wk 2, wk 3... etc...

Thanks!!

Helen


Marshall Barton said:
The Format function's FirstDayOfWeek argument I used is a 2,
which is Monday.

As for displaying dates instead of week numbers, I may be
kind of foggy today, but I don't see a way to do that.
Maybe someone else knows how to do it??
--
Marsh
MVP [MS Access]

It's works! :-) Follow up question;

Is there a way to have it grouped by week as it does now, but the column
heading is the first date of that week, instead of wk 01, wk 02, it would
say Jan 03, Jan 10 etc...?

And what day is it considering the first day of the week? Sunday or Monday?

Helen wrote:
I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");

"Marshall Barton" wrote
Try using:
PIVOT "Wk " & Format([OrderDate],"ww", 2)
Click to expand...
Click to expand...
Click to expand...
 
How about calculating the first day of the week based on the Order date?

Pivot DateAdd("d",-WeekdayOrderDate,2)+1,OrderDate)

You will have to leave this as a date if you want the dates in order. You
can format it as MM/DD

Marshall Barton said:
The Format function's FirstDayOfWeek argument I used is a 2,
which is Monday.

As for displaying dates instead of week numbers, I may be
kind of foggy today, but I don't see a way to do that.
Maybe someone else knows how to do it??
--
Marsh
MVP [MS Access]

It's works! :-) Follow up question;

Is there a way to have it grouped by week as it does now, but the column
heading is the first date of that week, instead of wk 01, wk 02, it would
say Jan 03, Jan 10 etc...?

And what day is it considering the first day of the week? Sunday or
Monday?

Helen wrote:
I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week. Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");

"Marshall Barton" wrote
Try using:
PIVOT "Wk " & Format([OrderDate],"ww", 2)
Click to expand...
Click to expand...
Click to expand...
 
Great idea John!

I think I have a blind spot for that technique, I blew
another post with a similar issue just a few days ago.

Thanks for jumping in and straightening it out.
--
Marsh
MVP [MS Access]


John said:
How about calculating the first day of the week based on the Order date?

Pivot DateAdd("d",-WeekdayOrderDate,2)+1,OrderDate)

You will have to leave this as a date if you want the dates in order. You
can format it as MM/DD


The Format function's FirstDayOfWeek argument I used is a 2,
which is Monday.

As for displaying dates instead of week numbers, I may be
kind of foggy today, but I don't see a way to do that.
Maybe someone else knows how to do it??

It's works! :-) Follow up question;

Is there a way to have it grouped by week as it does now, but the column
heading is the first date of that week, instead of wk 01, wk 02, it would
say Jan 03, Jan 10 etc...?

And what day is it considering the first day of the week? Sunday or
Monday?


Helen wrote:
I need to create a cross tab query that can group my data by week. The
wizard can do it by Year, by Quarter, by Month etc. but not by Week.
Maybe
it's not possible? My week "starts" on Mondays.

Currently the Column Heading is: "Qtr " & Format([OrderDate],"q")

The SQL code is:

TRANSFORM Sum(qry_totals.MOrderQty) AS SumOfMOrderQty
SELECT qry_totals.MStockCode, Sum(qry_totals.MOrderQty) AS [Total Of
MOrderQty]
FROM qry_totals
GROUP BY qry_totals.MStockCode
PIVOT "Qtr " & Format([OrderDate],"q");


Try using:
PIVOT "Wk " & Format([OrderDate],"ww", 2)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top