Summing payroll hours by day. How to?

[BTilson]

The database I'm currently designing has a table in it that has records, by day, for each employee. Each record has the date, employee info (id, name, etc) and the number of hours worked. I'm trying to make a query that, given a specified date range, will return the number of hours worked by each employee for that date range. I tried creating the query and then using the "sum" total on the hoursWorked field in the query design tool, but it doesn't want to work. It just shows each record still.

Anyone have any ideas? Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[RickSpr]

Switch your query to SQL View, then cut and paste the SQL statement here.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dhookom]

Can you take the time to share some field and table names as well as sample records? A simple totals query should work. What is the SQL view of the query that doesn't want to work?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cheerio]

Any other fields apart from hours will need to be changed to show Group By rather than SUM on the SUM row of the query grid

 

[BTilson]

Here is the SQL for my query.

SELECT Employees.ID, Employees.LAST_NAME, Employees.FIRST_NAME, Employees.MIDDLE_INITIAL, [Hours by Day].date, Sum([Hours by Day].hoursWorked) AS SumOfhoursWorked, [Pay Rates].basePayRate
FROM Employees INNER JOIN ([Hours by Day] INNER JOIN [Pay Rates] ON [Hours by Day].employeeID = [Pay Rates].employeeID) ON Employees.ID = [Hours by Day].employeeID
GROUP BY Employees.ID, Employees.LAST_NAME, Employees.FIRST_NAME, Employees.MIDDLE_INITIAL, [Hours by Day].date, [Pay Rates].basePayRate
HAVING ((([Hours by Day].date) Between #8/23/2004# And #8/29/2004#))
WITH OWNERACCESS OPTION;

Any suggestions? I'm not that adept with the actual SQL language, but I have a good bit of experience with Access and using the query design tool. It seems like this query should work, but it doesn't. It just displays each relevant record, without actually summing anything.

Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[BTilson]

Figured out my problem!! I was just randomly messing around, and I changed my date field from "group by" to "where" and now the query works perfectly.

Brooks Tilson
Database Development
Tilson Machine, Inc.