Summing records in a query that have the same values in 2 columns

zephyrtear

New member
Local time
Today, 08:00
Joined
Jul 19, 2010
Messages
2
Is it possible to sum all records that have the same values in 2 columns?

Hello!

Im having a bit of trouble with a database i am making for my farm and need help figuring out how to sum multiple records that share a same constant. Let me try to explain better...

I have the following tables and queries with the following columns:

Table 1 - JobsDone
JobID | Date | ActivityID | AreaID

Table 2 - JobsDetailed
JobID | WorkerID | BegTime | EndTime

Table 3 - Workers
WorkerID | Name | Wage

Query 1 - (with Example data)
Name | Date | BegTime | EndTime | ActivityID | Hrsworked
Joe | Jul -10 | 8:00 | 9:30 | 1 | =[Endtime]-[Begtime]
Joe | Jul -10 | 12:30 | 15:30 | 4 | =[Endtime]-[Begtime]
Joe | Jul -11 | 16:00 | 18:30 | 3 | =[Endtime]-[Begtime]
Eric | Jul 12 | 8:00 | 9:00 | 1 | =[Endtime]-[Begtime]

I need to have a daily sum of all the hours of each worker in order to find out if any of them worked overtime or not. Is there a way where this can be done automatically directly in a report? I dont want to have to make a query for each worker for each day to pull this information.

I used to have it done in excel by manually inputting the time each person began and ended their workday. But now that im trying to make a well planned db in access im having trouble with the automation of this process.

Any help is very much appreciated! Thank you!!
 
Last edited:
Have you tried using the Totals button in your query? This may be the simplest solution.

If it isn't then maybe you need to run a Make Table query and then query that for the totals.
 
Have you tried using the Totals button in your query? This may be the simplest solution.

If it isn't then maybe you need to run a Make Table query and then query that for the totals.

Thanks for the quick answer.

The issue with this is that i would have to make a query for every different employee for every day in order to have the report pull that info, every month.

Another way i wonder is if i could make a new union query and unite all records that contain same name and same date, but then sum the hrs worked to end up with one record per employee per day that has the sum of their worked hours that day. Is that possible?
 

Users who are viewing this thread

Back
Top Bottom