Query Averages

[bmw38]

So I'm kind of new to Access (having only a week of training my first semester in college) and I'm trying to get back into it. I have a bunch of forms that all have the same fields such as first and last name, department, location, and then a number for each month from 0-1.0 showing how much they worked in that month. Ex. 8/04 can be 1.0 and 9/04 could be .25 (this is for every person in the table).

What I'm trying to do is have a query that runs the average each person for the 12 months, then average by department and location, and average by just dept. I am not really sure where to start and when I tried it asks for parameter values and sometimes I get errors. I would really appreciate your help.

B

 

[randy700]

How about...
Using the Query Wizard, select Summary on the second screen.

Randy

 

[Golom]

Is it too late to modify your table structure? It is now un-normalized and that will cause serious problems in attempting to compute these kinds of statistics.

You should have a table that's something of the form
[tt]tblWorked
PersonID PK - Foreign key to Persons Table
WorkStart PK - Date of the first of the month
WorkFraction - Fraction 0 - 1.0
[/tt]
Then the average is simply

Select PersonID, Year(WorkStart)
     , Count(*) As [Months Reported]
     , AVG(WorkFraction) As [Average Worked]

From tblWorked

Group By PersonID, Year(WorkStart)

 

[bmw38]

Well its just that I imported it from excel. It was set up like this:
8/04 9/04 10/04
Name Loc Department .50 1.00 .75

etc.

 

[lespaul]

you can still create a normalize table and import your unnormalized data from the spreadsheet into the properly structured table.

Say you import your excel sheet into a temp table and then run an insert query to your real table:

SELECT Name, Loc, Department, 8/04, 'August' FROM TempTable
UNION
SELECT Name, Loc, Department, 9/04, 'September' FROM TempTable
UNION
etc.

now you can use Golom's query to get the averages.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi