Crosstab Qry, column heading, by week number, show "Mar 9-16"

[Todd]

Below is the query that returns everything I want to see but I wish to have
the column headings to be like "Mar 9-16" as that would allow me to copy and
paste into Excel with greater efficiency and integrity. Currenty the column
headings are 1,2,3,etc so I would need to format the format in the PIVOT to
return each week number as "MMM D - MMM D".

TRANSFORM Count([07517 m_eggers 1Q08].[Submitted Date Data Type]) AS
[CountOfSubmitted Date Data Type]
SELECT Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11) AS [Current Status],
Count([07517 m_eggers 1Q08].[Submitted Date]) AS Total
FROM [07517 m_eggers 1Q08]
GROUP BY Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11)
PIVOT Format([Submitted Date Data Type],"ww") In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20");

 

[Ken Sheridan]

Add the following function, which returns a string in the format you want, to
a standard module:

Public Function GetWeek(dtmDate As Date) As String

Dim strStartOfWeek As String, strEndOfWeek As String

' get dates for start of week (Sunday) and end of week (Saturday)
strStartOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 1, "mmm d")
strEndOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 7, "mmm d")

' if both in same month remove month from end of week
If Left(strEndOfWeek, 3) = Left(strStartOfWeek, 3) Then
strEndOfWeek = Mid(strEndOfWeek, 5)
End If

GetWeek = strStartOfWeek & " - " & strEndOfWeek

End Function

Then call it in the PIVOT clause:

PIVOT GetWeek([Submitted Date Data Type])

You'll the need to include all possible weeks in the IN clause's value list,
e.g. for all 2008:

IN("Dec 30 - Jan 5","Jan 6 – 12", "Jan 13 – 19",…….."Dec 21 – 27","Dec 28 -
Jan 3")

filling in the weeks between Jan 19 and Dec 21 of course.

Ken Sheridan
Stafford, England

 

[Todd]

This must be why functions are used in the first place. Thank you for the
assistance but I don't understand everything you wrote and will try to learn
about it before I use it. I was hoping the fix would be to just format the
pivot section one more time with something like if 1 is returned then use
"Dec 30 - Jan 5" if 2 is returned then "Jan 6 - Jan 12" etc. I know there
are limitations in each clause of the code and I need to learn much more.
Thank you for your quick response.

Add the following function, which returns a string in the format you want, to
a standard module:

Public Function GetWeek(dtmDate As Date) As String

Dim strStartOfWeek As String, strEndOfWeek As String

' get dates for start of week (Sunday) and end of week (Saturday)
strStartOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 1, "mmm d")
strEndOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 7, "mmm d")

' if both in same month remove month from end of week
If Left(strEndOfWeek, 3) = Left(strStartOfWeek, 3) Then
strEndOfWeek = Mid(strEndOfWeek, 5)
End If

GetWeek = strStartOfWeek & " - " & strEndOfWeek

End Function

Then call it in the PIVOT clause:

PIVOT GetWeek([Submitted Date Data Type])

You'll the need to include all possible weeks in the IN clause's value list,
e.g. for all 2008:

IN("Dec 30 - Jan 5","Jan 6 – 12", "Jan 13 – 19",…….."Dec 21 – 27","Dec 28 -
Jan 3")

filling in the weeks between Jan 19 and Dec 21 of course.

Ken Sheridan
Stafford, England

Below is the query that returns everything I want to see but I wish to have
the column headings to be like "Mar 9-16" as that would allow me to copy and
paste into Excel with greater efficiency and integrity. Currenty the column
headings are 1,2,3,etc so I would need to format the format in the PIVOT to
return each week number as "MMM D - MMM D".

TRANSFORM Count([07517 m_eggers 1Q08].[Submitted Date Data Type]) AS
[CountOfSubmitted Date Data Type]
SELECT Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11) AS [Current Status],
Count([07517 m_eggers 1Q08].[Submitted Date]) AS Total
FROM [07517 m_eggers 1Q08]
GROUP BY Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11)
PIVOT Format([Submitted Date Data Type],"ww") In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20");

 

[Ken Sheridan]

You could do that with a function too, but the trouble with that sort of
approach is that it would only work for the one year. By computing the start
and end dates for the week in which each date falls from the date itself it
will work for any year. Calculating the start and end of the week is done in
the function by using the Weekday function, which returns the number of the
day, 1 for Sunday, 2 for Monday etc, and then subtracting this from the date.
This gives the date of the Saturday immediately before the week starts, so
its then simply a case of adding 1 or 7 to get the start and end dates of the
week. The code then checks to see if both dates are in the same month by
comparing the first 3 characters of each; if they are the same the name of
the month is not repeated, so you get "Dec 30 - Jan 5", but "Jan 6 – 12" for
instance.

Ken Sheridan
Stafford, England

This must be why functions are used in the first place. Thank you for the
assistance but I don't understand everything you wrote and will try to learn
about it before I use it. I was hoping the fix would be to just format the
pivot section one more time with something like if 1 is returned then use
"Dec 30 - Jan 5" if 2 is returned then "Jan 6 - Jan 12" etc. I know there
are limitations in each clause of the code and I need to learn much more.
Thank you for your quick response.

Add the following function, which returns a string in the format you want, to
a standard module:

Public Function GetWeek(dtmDate As Date) As String

Dim strStartOfWeek As String, strEndOfWeek As String

' get dates for start of week (Sunday) and end of week (Saturday)
strStartOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 1, "mmm d")
strEndOfWeek = Format(dtmDate - Weekday(dtmDate, 1) + 7, "mmm d")

' if both in same month remove month from end of week
If Left(strEndOfWeek, 3) = Left(strStartOfWeek, 3) Then
strEndOfWeek = Mid(strEndOfWeek, 5)
End If

GetWeek = strStartOfWeek & " - " & strEndOfWeek

End Function

Then call it in the PIVOT clause:

PIVOT GetWeek([Submitted Date Data Type])

You'll the need to include all possible weeks in the IN clause's value list,
e.g. for all 2008:

IN("Dec 30 - Jan 5","Jan 6 – 12", "Jan 13 – 19",…….."Dec 21 – 27","Dec 28 -
Jan 3")

filling in the weeks between Jan 19 and Dec 21 of course.

Ken Sheridan
Stafford, England

Below is the query that returns everything I want to see but I wish to have
the column headings to be like "Mar 9-16" as that would allow me to copy and
paste into Excel with greater efficiency and integrity. Currenty the column
headings are 1,2,3,etc so I would need to format the format in the PIVOT to
return each week number as "MMM D - MMM D".

TRANSFORM Count([07517 m_eggers 1Q08].[Submitted Date Data Type]) AS
[CountOfSubmitted Date Data Type]
SELECT Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11) AS [Current Status],
Count([07517 m_eggers 1Q08].[Submitted Date]) AS Total
FROM [07517 m_eggers 1Q08]
GROUP BY Left(IIf([status] Is Null Or [status]="needs review" Or
[status]="want order number","Pending",[Status]),11)
PIVOT Format([Submitted Date Data Type],"ww") In
("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20");