CrossTab Query Format

  • Thread starter Thread starter gumby
  • Start date Start date
G

gumby

TRANSFORM Count([qry_DMC/TOP_CountFY06].ID) AS CountOfID
SELECT [qry_DMC/TOP_CountFY06].Area, Count([qry_DMC/TOP_CountFY06].ID)
AS [Total Of ID]
FROM [qry_DMC/TOP_CountFY06]
GROUP BY [qry_DMC/TOP_CountFY06].Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I would like to have my crosstab query formated bi-weekly instead of
month. Is this possible?

My [opened] field is a Date/Time field.

Thanks - David
 
You could try
PIVOT (DatePart("ww",[opened])-1)\2
This would also change your Column Headings to
0,1,2,3,4,5,6,7,....
 
Something like the following may work for you. Look up help on DatePart to
see what the optional arguments will do for your week calculation.

PIVOT "WEEK" & Int((DatePart("ww",[Opened]) +1) /2)* 2 IN
("Week2","Week4",...,"Week52", "Week54")
 
Back
Top