Fields calculated from different table

[Steve Thompson]

I'm trying to figure out how to populate a table or query field with values
calculated from a different table. For example, if I have the following
records in a table:

Job Type | Hours
---------- | ------
Type 1 | 5
Type 1 | 7
Type 2 | 2
Type 2 | 3
Type 2 | 4
Type 3 | 5

I want to generate a result that will look something like this:

Job Type | Avg. Hours
---------- | ------
Type 1 | 6
Type 2 | 3
Type 3 | 5

In other words, I'd like to have the "Avg. Hours" field calculated by
averaging the Hours value for all records with the same Job Type.

I'm not sure whether this is best accomplished by having an update query
fill values into an existing table, or creating a select query to calculate
the values on the fly when run.

If anyone has any tips on this, I'd appreciate it.

 

[John Spencer]

It is almost always better to use a query for this type of thing. You
will always have the current values.

SELECT [Job Type]
, Avg(Hours) as AvgHours
FROM [Some Table]
GROUP BY [Job Type]

In the query design view
-- Select Job Type and hours as your fields
-- Select View: Totals from the menu
-- Change Group By to Avg under Hours



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================