Average Column in Crosstab Query

[Thales750]

Here is a simple crosstab showing daily sales figures for a particular day within a sample year.


Query5
SalesDay_____2008_______ 2009______ 2010______Avg(All Years)
Jan 1 _______2249.26_____2550.62____1823.02_____???
Jan 2_______ 8722.25____ 12918.56___ 11637.06____???
Jan 3________8854.21_____9560.93___ 4610.39_____???
etc.



Is there a way to automatically add a calculated field on the left showing the average of all years. Obviously you could make a new query from the results of this query, but then you would have to make a new formula every time you changed the year range.

 

[DCrake]

Add a new column and then do an Avg([ColumnHeadingField])

 

[vbaInet]

Actually you want DAvg("Field", "Table") but is there really a point showing this data repeating across all records?

 

[Thales750]

Actually you want DAvg("Field", "Table") but is there really a point showing this data repeating across all records?

What I'm trying to do is show the daily sales for each year. That's the easy part.

Then I would like an average of all of the daily sales (for each year) on a seperate column.

Obviously I could just make another sumQuery set to avg, and external join by date.

Actually that works fine. Thanks Guys.

 

[vbaInet]

Obviously I could just make another sumQuery set to avg, and external join by date.

Actually that works fine. Thanks Guys.

Good thinking! Glad to know you've resolved it.

 

[lvh519]

Good thinking! Glad to know you've resolved it.

Can someone please help me with this? I have a very similar situation. I have a crosstab query that shows the following :

Terminal Product Jan Feb etc. Total for Year Average for Year
xyz abc 45 52 97 NOT WORKING

I have it currently set up with parameters for the date range. The total is working perfectly, however the average is inconsistent. Some lines look good, others are off by exactly 1/2 or 1/3. I am at a loss. I have seen threads that talk about subqueries, but I am really new at this and I haven't figured that out yet. I am working mostly in design view as the coding is a little above my head just yet.

Thanks.

 

[vbaInet]

Can someone please help me with this? I have a very similar situation. I have a crosstab query that shows the following :

Terminal Product Jan Feb etc. Total for Year Average for Year
xyz abc 45 52 97 NOT WORKING

I have it currently set up with parameters for the date range. The total is working perfectly, however the average is inconsistent. Some lines look good, others are off by exactly 1/2 or 1/3. I am at a loss. I have seen threads that talk about subqueries, but I am really new at this and I haven't figured that out yet. I am working mostly in design view as the coding is a little above my head just yet.

Thanks.

Welcome to the forum!

Can you possibly put this on a new thread and we'll have a look there. Perhaps upload some screen shots so we can see what's going on.