Adding Expressions into a Crosstab Query

[hoggbottom59]

I am writing a Cross-tab in which the monthly value needs to be (Number of Logs / Sites Open). It is to be used to show the Number of Call Logs per month proportionate to the number of sites being worked on.

To clarify a bit more the SQL for a similar query may help:

TRANSFORM Count(tblNEWHelpdeskMain.LogNumber) AS [The Value]
SELECT tblNEWHelpdeskMain.CallType, Count(tblNEWHelpdeskMain.LogNumber) AS [Call Type Total]
FROM tblNEWHelpdeskMain
WHERE (((tblNEWHelpdeskMain.Opened) #01/05/01 And #31/01/02#))
GROUP BY tblNEWHelpdeskMain.CallType
PIVOT Format([Opened],"yyyy mm"

This query groups the number of call logs per type for each month. My new query needs to group all call logs/open sites by month. I assuming at the moment that the 'open sites' will be a new field added to the table. I'll worry about that later.

Any ideas?

Leon.

 

[hoggbottom59]

This currently brings up an error saying 'Syntax Error missing operator' but it's a start. I propose to have '0' or '1' in the Open_Site field. The site is open if the value is '1' and therefore the open sites are totalled.

TRANSFORM ((Count(tblNEWHelpdeskMain.LogNumber)) / (Count(tblNEWHelpdeskMain.Open_Site)) AS [The Value]
SELECT tblNEWHelpdeskMain.CallType, Count(tblNEWHelpdeskMain.LogNumber) AS [Call Type Total]
FROM tblNEWHelpdeskMain
WHERE (((tblNEWHelpdeskMain.Opened) Between [Start] And [End]))
GROUP BY tblNEWHelpdeskMain.CallType
PIVOT Format([Opened],"Short Date"


Any ideas,
Cheers Leon.