Need help with a complex query on a single table 1

[marcus81]

Well, not entirely sure how complex this is, but i can't see the wood for the trees at this point so i'm hoping one of you can help.

The table has 6 fields;

WEEK (Long Int)
NAME (Text)
JOB TYPE (Text)
OVERTIME HRS (Double, 2 Dec places)
OVERTIME VALUE (Double, 2 Dec places)
LOCATION (Text)

My problem is this,
I'm using the WEEK and NAME together as my primary key.
WEEK contains values anywhere between 1 and 50.
NAME can be any one of 80 people. The names repeat from week to week.

I want to extract the information for each name at a time. That is, i want to select all of the records containing JOE BLOGG, and then get an average value for his OVERTIME HRS and OVERTIME VALUE.

I can do this using the specific name for each employee but this is really drawn out, and even then it only gives me a table with 50 weeks worth of records on that one name. I want all the info condensed to one line.

Example of what i want to end up with;
Name Job Type Wkly avg hrs Wkly avg value
Joe Blogg Driver 6.52 84.23


Any suggestions?

 

[PHV]

A starting point:
SELECT A.NAME, A.[JOB TYPE], Avg(A.[OVERTIME HRS]) AS [Wkly avg hrs], Avg(A.[OVERTIME VALUE]) AS [Wkly avg value]
FROM yourTable AS A
GROUP BY A.NAME, A.[JOB TYPE];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244