how can i calculate fields???

[Muzicmn]

hi guys,

prob a simple solution but my simple mind just cant seem to figure it out....

my db tracks overtime, when a person is assigned ot the supervisor puts the total number of hours they are to work on that specific day along with other information into a table. i would like access to be able to generate a list of employees (prob through a query) with the employee with the least hours on top. Now when i set up a query, how do i ask the query to sum all the hours for each individual and compare it to the total hours of the other employees and then sort them.

only field i have to go by is the ot scheduled for a specific date so an emp could have hundreds of records

hope i explained that correctly

thanks in advance

Ricky

 

[pbaldy]

See if this is doing what you want:

SELECT TableName.EmployeeField, Sum(TableName.HoursField) AS SumOfHoursField
FROM TableName
GROUP BY TableName.EmployeeField
ORDER BY Sum(TableName.HoursField);

 

[Muzicmn]

sorry but i dont understand

where do i put that code??/

also, i read my original post and it seems a bit confusing, let me try and explain it again.

my db has an employee_info table and an overtime_events table...through a form i created the supervisor assigns overtime to each employee and it creates a record in the overtime_events table.....

now, what i want to do is generate a report with the employees name and total of overtime hours for that employee, (sort of a summary report if you would)

ie: tom 120
charles 225

whey i try to use the sum in the report it gives me the sum of ALL employees OT, i would like to break it into individual empls

thanks

Ricky

 

[theprez]

You need to create a summation query on the overtime table. You can sum by employee

 

[pbaldy]

What I gave you was a query that may do what you're asking for. You'd have to go into the SQL view of a new query and paste it in. You'd have to change the table and field names to match your actual names. You may want to add criteria to restrict to certain activity, for instance a date range. Perhaps if you could post a sample of the table, one of us can create a query for you.

 

[Muzicmn]

wow......

it worked, i have been beating my head against the wall for days with that one

i guess its time i learned how to write code.......

thanks alot

Ricky

ps, any good suggestions for books on how to learn to write code for access???