need avg but sum is already used?

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

I have an existing query that already contains a calculated field that uses
the sum() aggregate function. From a table with 1 or several entries for a
particular Item containing start and stop times it sums the total time taken
per Item (each record has an ItemID, ItemType, ItemMake, Start, and Stop
field). So I am already using the sum() function to do this. However, now I
need to get the average time recorded grouped by ItemType.

So, as succinctly put as possible, I need to use the sum() function grouping
by the particular ItemID to get the TotalTime for each individual item. I
have this already. I then need to find the average TotalTime by ItemType.

How can I do this with one query? Do I need to use a sub query? I prefer
using one query if possible as if I understand correctly, that is preferable
over linking several query objects together. ie. SELECT * FROM qrySavedQuery
INNER JOIN tblTableName etc...
 
djc,

I have never heard that there is anything wrong with a query based on
another query. That's how I would do it!

- Steve Schapel, Microsoft Access MVP
 
hmm. ok. Thank you for clearing that up for me.

For purely academic purposes then do you know how to construct a single
query to do what I need? I have an interest in the ability to do all via
writting sql directly.

thanks again.
 
Back
Top