Cross Tab query avg calculations

[zjzastava]

I received some help on construction a cross tab query. The query is

PARAMETERS [forms].[frmDateRangeOA].[txtDateStartOA] DateTime, [forms].[frmDateRangeOA].[txtDateEndOA] DateTime;
TRANSFORM Count(tblOldAbes.RawData) AS CountOfRawData
SELECT tblOldAbes.Time
FROM tblOldAbes
WHERE (((tblOldAbes.FullDate) Between [forms].[frmDateRangeOA].[txtDateStartOA] And [forms].[frmDateRangeOA].[txtDateEndOA]))
GROUP BY tblOldAbes.Time
PIVOT Format([FullDate],"dddd") In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");


And it works great. Unfortunately I do not completely understand it. I would like to create a similar query that will give an Avg for the CountOfRawData from the above query.

 

[dhookom]

What happens if you change the Count of the Value column to Avg?

Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

I am not sure what you mean when you mean when you mentioned to "change the Count of the value column to Avg". I have tried changing Count to Avg in
TRANSFORM Count(tblOldAbes.RawData) AS CountOfRawData
An error came up "expression is typed incorrectly or is too complex..."

I should have given a little background on what I am trying to do. I have a report with the record source of the cross tab query (qryCrossTabCountOA) from my original post. I have a text box for each day of the week. In each text box controls source is =[Monday]/DateDiff("ww",[forms].[frmDateRangeAvgOA].[txtDateSrtartAvgOA},[forms].[frmDateRangeAvgOA].[txtDateEndAvgOA])
This is returning the values I need which is the Avg for that day per hour.

The problem is coming from trying to get an total avg. I have text box in the report footer with =Avg([Monday]). This is giving me and avg of the day (Monday). When I am looking for the Avg per day per hour (avg of the averages).

I was thinking I would need to create a query that would calculate the avg and put that in the record source of the report to get what I need. If there is another way to do this I am eager to learn.

Thank You
Zac Z

 

[lameid]

I think you mean you want to turn off crosstab for the query you have, make a query based off it and do the same crosstab but take the average of the column you counted.