Access Query - Avg of each total

  • Thread starter Thread starter cjwenngatz
  • Start date Start date
C

cjwenngatz

I'm new to Access so please bear with me. I currently have information
on a multitude of construction projects that is broken down by cost
type.

ie:
project 1 - cost type 1
- cost type 2, etc

project 2 - cost type 1
- cost type 2, etc

What I'm trying to do is take one cost type, and add the that for
every single project, into one sum. Then take that sum and average it
out on the total sq footage of those projects.

I've created the query to search for each cost type, but how do I
create this calculation to avg the cost?
 
I'm new to Access so please bear with me. I currently have information
on a multitude of construction projects that is broken down by cost
type.

ie:
 project 1 - cost type 1
                 - cost type 2, etc

project 2 - cost type 1
             - cost type 2, etc

What I'm trying to do is take one cost type, and add the that for
every single project, into one sum. Then take that sum and average it
out on the total sq footage of those projects.

I've created the query to search for each cost type, but how do I
create this calculation to avg the cost?
Click to expand...

SELECT [your projects table name].[your cost type Amount field]
FROM [your projects table name]
UNION ALL SELECT Sum([your projects table name].[your cost type
Amount field]) AS SumOfCostType
FROM [your projects table name];
 
I guess you'd want it by cost type also. and you needed average too

SELECT [your projects table name].[your cost type Amount field]
FROM [your projects table name]
WHERE [your projects table name].[your cost type Amount field] = "Your
Cost Type name you want to filter by"
UNION ALL SELECT Sum([your projects table name].[your cost
type
Amount field]) AS SumOfCostType
FROM [your projects table name]
WHERE [your projects table name].[your cost type Amount field] = "Your
Cost Type name you want to filter by";

I don't know if you can union a second time. I never tried it. but
you could run another query similar to extract the average

SELECT [your projects table name].[your cost type Amount field]
FROM [your projects table name]
WHERE [your projects table name].[your cost type Amount field] = "Your
Cost Type name you want to filter by"
UNION ALL SELECT Avg([your projects table name].[your cost
type
Amount field]) AS AvgOfCostType
FROM [your projects table name]
WHERE [your projects table name].[your cost type Amount field] = "Your
Cost Type name you want to filter by";
 
Back
Top