Can I get multiple averages in one query

[Autoeng]

I have a table with the following information:

User Item # Start Date Finish Date Days Difference
"A" 1 01/01/02 01/03/02 3
"A" 2 01/01/02 01/05/02 5
"A" 3 01/01/02 01/08/02 8
"B" 4 02/01/02 02/15/02 15
"B" 5 02/01/02 02/21/02 21

I need to not only get the average for all items (10.4 in example) but need the averages for each user (5.3 for "A", 18 for "B" in example.

What is the best way to do this?

Autoeng

 

[Pat Hartman]

A query cannot produce a recordset containing multiple levels of summarization. You will need to use a report.

 

[Autoeng]

Can I use multiple queries (summarizing each user on individual queries) and pull the results all together in a final query? The reason I ask is that the results are for a supervisor's use and I know that I will get complaints that they cannot alter the report where if I exported the recordset as an excel file they could manipulate the data any way they wanted to. Plus it keeps their interaction within the database to a minimum (clicking a button on the switchboard).

Autoeng

 

[Pat Hartman]

You could use two totals queries and a union query to pull them together.

Select User, Avg(Difference) As AvgDif
From YourTable
Union Select "ZZ Total" As User, Avg(Difference) As AvgDif
From YourTable
Order By User;

Notice the literal ZZ Total. I added that so that the total row will sort to the end.

 

[Autoeng]

Pat:

I apologize for not being more knowledgable in queries. Can you detail the solution a little more as I don't understand what I need to do?

Thank you for your patience and assistance,
Autoeng

 

[Pat Hartman]

If the fieldnames are as you posted them, you only need to change the table name in the union query sample that I posted. Then you can cut and paste. But I left something out -

Select User, Avg(Difference) As AvgDif
From YourTable
Group By User
Union Select "ZZ Total" As User, Avg(Difference) As AvgDif
From YourTable
Order By User;

Open the query builder in SQL view.
Paste the above query
Change YourTable to your actual table name.

 

[Autoeng]

What do I change if "Start Date" comes from "Basic" table and "Finish Date" comes from "Detail" table?

Thanks again,
Autoeng

 

[Pat Hartman]

I presume that you already have a query that calculates the difference. Replace YourTable with the query name. Queries and tables can be used interchangeably for most purposes.

 

[Autoeng]

Pat:

You are a query god. Thank you. It works perfect!

Autoeng