Another Total Query Question

[Raychel162]

I am trying to create a query that will give me the total full time/part time hours per year per position. Ex: I want to find out how many hours part time attorneys worked in 1995.

I have the following fields in my query: Role, Form year, Unique Key, and Time Percent. In the time percent field the value consist of part time: .5, .6, .75, .8 and in full time: 1. I want it to create a sum.

Any comments on what direction i should take would be greatly appreciated.

 

[GingerR]

What have you tried so far? How is it not working for you?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Raychel162]

I have tried using the total function and the sum function. As of now its listing each part time employee, but its not totalling up their hours. Example: it lists 1995, attorney, .5 hours( for three different employees it'll list three different entries telling me this info). I want it to list it like 1995, attorney, 1.5 (totalling those three attorneys hours).

I hope this clarifies things a bit.

 

[DotNetGnat]

you need to use GROUP BY

show us your query.

-DNG

 

[Raychel162]

Field: Role FORM_YEAR ID TIME_PERCENT
Table: tbl_PL_Role HC_YEAR HC_YEAR HC_YEAR
Total: Group By Group By Group by Sum
Sort: Ascending Ascending
Show: X X X
Criteria: <1

That's my query as of now.

 

[PHV]

Suppress the ID column.

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

 

[GingerR]

Make the ID column say WHERE instead of GROUP BY.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Raychel162]

no dice, now i get no results at all.

 

[Raychel162]

nevermind, i fixed it. What i did was i put the time percent column in twice, once specifying that i wanted it to sum and another using where and specifying the criteria.
The final query ended up looking like this:
Field: Role Form_Year ID Time% Time%
Table: tbl_PL_Role HC_Year HC_Year HC_Year HC_Year
Total: Group by Group by Sum Where
Sort: Ascending Ascending
Show: X X X
Criteria: <1

Thanks for everyone's help!