CrossTab Query Format

[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

 

[Duane Hookom]

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,....

 

[John Spencer]

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")