Crosstab Query by Week with Dates as Headers

  • Thread starter Thread starter ceedge
  • Start date Start date
C

ceedge

Hi

I am trying to write a crosstab query in Access that will give me the totals
by week for each person.

My data contains a list a people with dates and amounts. Here is a small
sample of the data over five weeks for one person. I tried to put it in as a
table but it wouldn't let me so spaces are breaking the columns:

Date Name Supervisor Manager Amt
03/08/2010 Agent1 Supv1 Mgr1 3
03/07/2010 Agent1 Supv1 Mgr1 5
03/04/2010 Agent1 Supv1 Mgr1 8
03/03/2010 Agent1 Supv1 Mgr1 5
03/01/2010 Agent1 Supv1 Mgr1 9
02/28/2010 Agent1 Supv1 Mgr1 1
02/15/2010 Agent1 Supv1 Mgr1 6
02/14/2010 Agent1 Supv1 Mgr1 12

What I’d like to get back as the results is a list of each person with a
total for each week. The columns by week number but have the column header
show the date for Monday of that week. In this sample of data 02/08 is the
7th week of the year, 02/15 is the 8th week, etc. Below is what I want the
results to look like but I’ve only given Agent1 data but the other agents
would have data as well:

Name Supervisor Manager Totals 02/08 02/15 02/22 03/01 03/08
Agent1 Supv1 Mgr1 74 12 6 1 27 28

Just to Clarify the field called “Field3†is Name in my chart above but I
have Access set to display “Name†instead of “Field3.†“Amt†is the same as
“CountOfAgent†and “Total†is the same as “Total Of CountOfAgentâ€

Here is my code in the SQL view and the results I’m currently getting. This
will group by week correctly but the results give the week number as the
header. When I give this report out, I want them to be able to see the week
date (for Monday of the week) as they will not know what the week number
means. I could use this query as is to get the results and then manually go
into the data and put in the Date for the week if I had to for now as that
wouldn’t take much time with only currently at 16 weeks in the year but as
the year continues that will be time consuming.

My Code:
TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

PIVOT "Wk# " & Format(Format([Date],"ww",2),"00");


The results of my code as written currently:

Name Supervisor Manager Totals Wk# 7 Wk# 8 Wk# 9 Wk# 10 Wk# 11
Agent1 Supv1 Mgr1 74 12 6 1 27
28

I’ve looked in Access books, online help, and googled various sites, but
nothing works correctly even though it seems to work for the people they
posted it for so I’m not sure what I’m doing wrong. I’ve tried various
combinations of Format(), DateAdd(), DatePart(), etc, but I’m missing
something. Here are a few things I tried and the results that are not
correct.

I’ve tried this which is something I found looking around but I got a syntax
error. It is from this website and the issue listed is very similar to what
I’m doing: http://www.keyongtech.com/239881-cross-tab-query-group-by

Pivot DateAdd("d",Weekday[Date],2)+1,[Date]);

This one gave me dates in this year but not the correct grouping.
2/16/2010, 2/21/2010, 2/22/2010, etc.

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),[Date] );

This did a grouping of 8 headers with dates of 1/2/1900, 1/3/1900, etc and
I’m not sure how it is grouping the data

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),2);

This gives me the week # then the date but list each date and doesn’t group
by week, but I can see it is putting the correct days with weeks:

PIVOT "Wk#" &Format(DatePart("ww",[Date]),"00 ") &Format([Date]," mm/dd");

I even thought maybe I need to define my formatting by week number but this
gives me a Transform error. I tried not defining it but doing the format in
the Sort By using this but that gives syntax errors:

TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent,
Format(Format([Date],"ww",2),"00") AS WeekNumber

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

SORT BY WeekNumber

PIVOT Format([Date]," mm/dd");

Hopefully this post has all the information you need and not too long. This
is driving me crazy as I know I’m missing something simple or being clueless,
but I've yet to figure it out. Any help would be great.


Thanks in advance

cee
 
Given a date, the following expression returns the Monday of the same week
(starting Sunday)


[Date] -DatePart("w", [Date] ) + 2



or, if you prefer:

TRANSFORM SUM(amt) AS cell
SELECT [name], supervisor, SUM(cell) AS totalForThisAgent
FROM somewhere
GROUP BY [name], supervisor
PIVOT [Date] -DatePart("w", [Date] ) + 2



Vanderghast, Access MVP



ceedge said:
Hi

I am trying to write a crosstab query in Access that will give me the
totals
by week for each person.

My data contains a list a people with dates and amounts. Here is a small
sample of the data over five weeks for one person. I tried to put it in as
a
table but it wouldn't let me so spaces are breaking the columns:

Date Name Supervisor Manager Amt
03/08/2010 Agent1 Supv1 Mgr1 3
03/07/2010 Agent1 Supv1 Mgr1 5
03/04/2010 Agent1 Supv1 Mgr1 8
03/03/2010 Agent1 Supv1 Mgr1 5
03/01/2010 Agent1 Supv1 Mgr1 9
02/28/2010 Agent1 Supv1 Mgr1 1
02/15/2010 Agent1 Supv1 Mgr1 6
02/14/2010 Agent1 Supv1 Mgr1 12

What I’d like to get back as the results is a list of each person with a
total for each week. The columns by week number but have the column
header
show the date for Monday of that week. In this sample of data 02/08 is
the
7th week of the year, 02/15 is the 8th week, etc. Below is what I want
the
results to look like but I’ve only given Agent1 data but the other agents
would have data as well:

Name Supervisor Manager Totals 02/08 02/15 02/22 03/01 03/08
Agent1 Supv1 Mgr1 74 12 6 1 27
28

Just to Clarify the field called “Field3†is Name in my chart above but I
have Access set to display “Name†instead of “Field3.†“Amt†is the same
as
“CountOfAgent†and “Total†is the same as “Total Of CountOfAgentâ€

Here is my code in the SQL view and the results I’m currently getting.
This
will group by week correctly but the results give the week number as the
header. When I give this report out, I want them to be able to see the
week
date (for Monday of the week) as they will not know what the week number
means. I could use this query as is to get the results and then manually
go
into the data and put in the Date for the week if I had to for now as that
wouldn’t take much time with only currently at 16 weeks in the year but as
the year continues that will be time consuming.

My Code:
TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of
CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

PIVOT "Wk# " & Format(Format([Date],"ww",2),"00");


The results of my code as written currently:

Name Supervisor Manager Totals Wk# 7 Wk# 8 Wk# 9 Wk# 10 Wk# 11
Agent1 Supv1 Mgr1 74 12 6 1 27
28

I’ve looked in Access books, online help, and googled various sites, but
nothing works correctly even though it seems to work for the people they
posted it for so I’m not sure what I’m doing wrong. I’ve tried various
combinations of Format(), DateAdd(), DatePart(), etc, but I’m missing
something. Here are a few things I tried and the results that are not
correct.

I’ve tried this which is something I found looking around but I got a
syntax
error. It is from this website and the issue listed is very similar to
what
I’m doing: http://www.keyongtech.com/239881-cross-tab-query-group-by

Pivot DateAdd("d",Weekday[Date],2)+1,[Date]);

This one gave me dates in this year but not the correct grouping.
2/16/2010, 2/21/2010, 2/22/2010, etc.

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),[Date] );

This did a grouping of 8 headers with dates of 1/2/1900, 1/3/1900, etc and
I’m not sure how it is grouping the data

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),2);

This gives me the week # then the date but list each date and doesn’t
group
by week, but I can see it is putting the correct days with weeks:

PIVOT "Wk#" &Format(DatePart("ww",[Date]),"00 ") &Format([Date],"
mm/dd");

I even thought maybe I need to define my formatting by week number but
this
gives me a Transform error. I tried not defining it but doing the format
in
the Sort By using this but that gives syntax errors:

TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent,
Format(Format([Date],"ww",2),"00") AS WeekNumber

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of
CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

SORT BY WeekNumber

PIVOT Format([Date]," mm/dd");

Hopefully this post has all the information you need and not too long.
This
is driving me crazy as I know I’m missing something simple or being
clueless,
but I've yet to figure it out. Any help would be great.


Thanks in advance

cee
Click to expand...
 
You can use an expression to calculate Monday of the week.

DateAdd("d",1-Weekday([DateField],2),[DateField])

That expression will return the date of the Monday before Sunday. So weeks
run from Monday to Sunday. If you need Sundays to be accounted for on the
basis of the week running from Sunday To Saturday, post back. I will try to
come up with a simple modification to the above that will work. Probably
calculate the Sunday of the week and then add one to that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi

I am trying to write a crosstab query in Access that will give me the totals
by week for each person.

My data contains a list a people with dates and amounts. Here is a small
sample of the data over five weeks for one person. I tried to put it in as a
table but it wouldn't let me so spaces are breaking the columns:

Date Name Supervisor Manager Amt
03/08/2010 Agent1 Supv1 Mgr1 3
03/07/2010 Agent1 Supv1 Mgr1 5
03/04/2010 Agent1 Supv1 Mgr1 8
03/03/2010 Agent1 Supv1 Mgr1 5
03/01/2010 Agent1 Supv1 Mgr1 9
02/28/2010 Agent1 Supv1 Mgr1 1
02/15/2010 Agent1 Supv1 Mgr1 6
02/14/2010 Agent1 Supv1 Mgr1 12

What I’d like to get back as the results is a list of each person with a
total for each week. The columns by week number but have the column header
show the date for Monday of that week. In this sample of data 02/08 is the
7th week of the year, 02/15 is the 8th week, etc. Below is what I want the
results to look like but I’ve only given Agent1 data but the other agents
would have data as well:

Name Supervisor Manager Totals 02/08 02/15 02/22 03/01 03/08
Agent1 Supv1 Mgr1 74 12 6 1 27 28

Just to Clarify the field called “Field3†is Name in my chart above but I
have Access set to display “Name†instead of “Field3.†“Amt†is the same as
“CountOfAgent†and “Total†is the same as “Total Of CountOfAgentâ€

Here is my code in the SQL view and the results I’m currently getting. This
will group by week correctly but the results give the week number as the
header. When I give this report out, I want them to be able to see the week
date (for Monday of the week) as they will not know what the week number
means. I could use this query as is to get the results and then manually go
into the data and put in the Date for the week if I had to for now as that
wouldn’t take much time with only currently at 16 weeks in the year but as
the year continues that will be time consuming.

My Code:
TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

PIVOT "Wk# " & Format(Format([Date],"ww",2),"00");


The results of my code as written currently:

Name Supervisor Manager Totals Wk# 7 Wk# 8 Wk# 9 Wk# 10 Wk# 11
Agent1 Supv1 Mgr1 74 12 6 1 27
28

I’ve looked in Access books, online help, and googled various sites, but
nothing works correctly even though it seems to work for the people they
posted it for so I’m not sure what I’m doing wrong. I’ve tried various
combinations of Format(), DateAdd(), DatePart(), etc, but I’m missing
something. Here are a few things I tried and the results that are not
correct.

I’ve tried this which is something I found looking around but I got a syntax
error. It is from this website and the issue listed is very similar to what
I’m doing: http://www.keyongtech.com/239881-cross-tab-query-group-by

Pivot DateAdd("d",Weekday[Date],2)+1,[Date]);

This one gave me dates in this year but not the correct grouping.
2/16/2010, 2/21/2010, 2/22/2010, etc.

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),[Date] );

This did a grouping of 8 headers with dates of 1/2/1900, 1/3/1900, etc and
I’m not sure how it is grouping the data

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),2);

This gives me the week # then the date but list each date and doesn’t group
by week, but I can see it is putting the correct days with weeks:

PIVOT "Wk#" &Format(DatePart("ww",[Date]),"00 ") &Format([Date]," mm/dd");

I even thought maybe I need to define my formatting by week number but this
gives me a Transform error. I tried not defining it but doing the format in
the Sort By using this but that gives syntax errors:

TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent,
Format(Format([Date],"ww",2),"00") AS WeekNumber

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

SORT BY WeekNumber

PIVOT Format([Date]," mm/dd");

Hopefully this post has all the information you need and not too long. This
is driving me crazy as I know I’m missing something simple or being clueless,
but I've yet to figure it out. Any help would be great.


Thanks in advance

cee
Click to expand...
 
Thanks so much that works.



John Spencer said:
You can use an expression to calculate Monday of the week.

DateAdd("d",1-Weekday([DateField],2),[DateField])

That expression will return the date of the Monday before Sunday. So weeks
run from Monday to Sunday. If you need Sundays to be accounted for on the
basis of the week running from Sunday To Saturday, post back. I will try to
come up with a simple modification to the above that will work. Probably
calculate the Sunday of the week and then add one to that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi

I am trying to write a crosstab query in Access that will give me the totals
by week for each person.

My data contains a list a people with dates and amounts. Here is a small
sample of the data over five weeks for one person. I tried to put it in as a
table but it wouldn't let me so spaces are breaking the columns:

Date Name Supervisor Manager Amt
03/08/2010 Agent1 Supv1 Mgr1 3
03/07/2010 Agent1 Supv1 Mgr1 5
03/04/2010 Agent1 Supv1 Mgr1 8
03/03/2010 Agent1 Supv1 Mgr1 5
03/01/2010 Agent1 Supv1 Mgr1 9
02/28/2010 Agent1 Supv1 Mgr1 1
02/15/2010 Agent1 Supv1 Mgr1 6
02/14/2010 Agent1 Supv1 Mgr1 12

What I’d like to get back as the results is a list of each person with a
total for each week. The columns by week number but have the column header
show the date for Monday of that week. In this sample of data 02/08 is the
7th week of the year, 02/15 is the 8th week, etc. Below is what I want the
results to look like but I’ve only given Agent1 data but the other agents
would have data as well:

Name Supervisor Manager Totals 02/08 02/15 02/22 03/01 03/08
Agent1 Supv1 Mgr1 74 12 6 1 27 28

Just to Clarify the field called “Field3†is Name in my chart above but I
have Access set to display “Name†instead of “Field3.†“Amt†is the same as
“CountOfAgent†and “Total†is the same as “Total Of CountOfAgentâ€

Here is my code in the SQL view and the results I’m currently getting. This
will group by week correctly but the results give the week number as the
header. When I give this report out, I want them to be able to see the week
date (for Monday of the week) as they will not know what the week number
means. I could use this query as is to get the results and then manually go
into the data and put in the Date for the week if I had to for now as that
wouldn’t take much time with only currently at 16 weeks in the year but as
the year continues that will be time consuming.

My Code:
TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

PIVOT "Wk# " & Format(Format([Date],"ww",2),"00");


The results of my code as written currently:

Name Supervisor Manager Totals Wk# 7 Wk# 8 Wk# 9 Wk# 10 Wk# 11
Agent1 Supv1 Mgr1 74 12 6 1 27
28

I’ve looked in Access books, online help, and googled various sites, but
nothing works correctly even though it seems to work for the people they
posted it for so I’m not sure what I’m doing wrong. I’ve tried various
combinations of Format(), DateAdd(), DatePart(), etc, but I’m missing
something. Here are a few things I tried and the results that are not
correct.

I’ve tried this which is something I found looking around but I got a syntax
error. It is from this website and the issue listed is very similar to what
I’m doing: http://www.keyongtech.com/239881-cross-tab-query-group-by

Pivot DateAdd("d",Weekday[Date],2)+1,[Date]);

This one gave me dates in this year but not the correct grouping.
2/16/2010, 2/21/2010, 2/22/2010, etc.

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),[Date] );

This did a grouping of 8 headers with dates of 1/2/1900, 1/3/1900, etc and
I’m not sure how it is grouping the data

PIVOT DateAdd("d",Weekday(Format([Date],"ww",2)),2);

This gives me the week # then the date but list each date and doesn’t group
by week, but I can see it is putting the correct days with weeks:

PIVOT "Wk#" &Format(DatePart("ww",[Date]),"00 ") &Format([Date]," mm/dd");

I even thought maybe I need to define my formatting by week number but this
gives me a Transform error. I tried not defining it but doing the format in
the Sort By using this but that gives syntax errors:

TRANSFORM Sum([Repeats Table].CountOfAgent) AS SumOfCountOfAgent,
Format(Format([Date],"ww",2),"00") AS WeekNumber

SELECT [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager, Sum([Repeats Table].CountOfAgent) AS [Total Of CountOfAgent]

FROM [Repeats Table]

GROUP BY [Repeats Table].Field3, [Repeats Table].Supervisor, [Repeats
Table].Manager

SORT BY WeekNumber

PIVOT Format([Date]," mm/dd");

Hopefully this post has all the information you need and not too long. This
is driving me crazy as I know I’m missing something simple or being clueless,
but I've yet to figure it out. Any help would be great.


Thanks in advance

cee
Click to expand...
.
Click to expand...
 
Hi John,
Thank you so much for posting this solution. I have the exact same problem and this works for me as well except for one thing - how do I make the weekrun from Monday to Friday? I am trying to sum by week for weekdays only the # of claims done by process by supervisor and manager. For example, I know the week of 5/13/13 (Monday) thru 5/17/13 (Friday) a processor completed40 claims, however, using this function, it shows that the processor completed 50 claims for the week of 5/13/13. Others processors show 60 per week, when it should be no more than 50. Thanks in advance for any help!!
 
Hi John,
Thank you so much for posting this solution. I have the exact same problem and this
Click to expand...
works for me as well except for one thing - how do I make the week run from
Monday to Friday? I am trying to sum by week for weekdays only the # of claims
done by process by supervisor and manager. For example, I know the week of
5/13/13 (Monday) thru 5/17/13 (Friday) a processor completed 40 claims,
however, using this function, it shows that the processor completed 50 claims
for the week of 5/13/13. Others processors show 60 per week, when it should
be no more than 50. Thanks in advance for any help!!


I have no idea if I'm the "John" mentioned, but since you did not quote the
previous message to which you're responding and since it's (apparently)
expired from my news feed, it's very hard indeed to provide any useful advice.

Could you perhaps post a description of your table structure, the SQL of the
query you're using, and the VBA code of the function (if it is a function)?

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top