Crosstab Query help

[ken]

Hello,

I have a table that has some fields openDate, closedDate,Site and callID. I
am trying to use a crosstab query to list the average time spent on calls
for each site. Below is the query I am using.
TRANSFORM Avg(calls.ClosedDate) AS AvgOfClosedDate
SELECT Format([opendate],' mm') AS [Month],
Count(DateDiff("d",[openDate],[ClosedDate])) AS Elapsed
FROM calls
WHERE (((calls.site)="OC") AND ((calls.ClosedDate) Is Not Null))
GROUP BY Format([opendate],' mm')
PIVOT DateDiff("d",[openDate],[ClosedDate]);

The results returned are:
Month Elapsed 4 26
03 2 38075 38075

What I need would be:
Month Total Avg
03 2 15

Any help would be appreciated as this is new to me.
Ken

 

[Duane Hookom]

I don't think you need a crosstab
SELECT Format([opendate],' mm') AS [Month], Count([OpenDate]) as TheTotal,
Avg(DateDiff("d",[openDate],[ClosedDate])) as TheAvg
FROM Calls
WHERE site="OC" AND ClosedDate Is Not Null
GROUP BY Format([opendate],' mm');

 

[ken]

That worked great. Thanks for all the help

I don't think you need a crosstab
SELECT Format([opendate],' mm') AS [Month], Count([OpenDate]) as TheTotal,
Avg(DateDiff("d",[openDate],[ClosedDate])) as TheAvg
FROM Calls
WHERE site="OC" AND ClosedDate Is Not Null
GROUP BY Format([opendate],' mm');

--
Duane Hookom
MS Access MVP
--

Hello,

I have a table that has some fields openDate, closedDate,Site and

callID.

I
am trying to use a crosstab query to list the average time spent on calls
for each site. Below is the query I am using.
TRANSFORM Avg(calls.ClosedDate) AS AvgOfClosedDate
SELECT Format([opendate],' mm') AS [Month],
Count(DateDiff("d",[openDate],[ClosedDate])) AS Elapsed
FROM calls
WHERE (((calls.site)="OC") AND ((calls.ClosedDate) Is Not Null))
GROUP BY Format([opendate],' mm')
PIVOT DateDiff("d",[openDate],[ClosedDate]);

The results returned are:
Month Elapsed 4 26
03 2 38075 38075

What I need would be:
Month Total Avg
03 2 15

Any help would be appreciated as this is new to me.
Ken