Currently, I have a table that contains the fields [Employee Name][Date][Rig #][State][Hours]. I have started a query that asks for the employees name, then a date range, and then which state was worked and it will reply the correct information. What I would like to add would be a sum of the hours worked for a stated entered within the date range that was entered earlier as well. So if someone enters John for the name, the date 8/1/2011 and then 8/30/2011, and a state for it to give me the sum of the hours worked in the state. I've been able to get my sum to give me the hours an employee worked in a specific state, but not for the specified date range. It adds all of their hours for the specific state.
How can I get a sum of a field according to prior criteria in a query?
- Thread starter ITguy1981
- Start date
SELECT [Daily Time].Employee, [Daily Time].[Work Date], [Daily Time].State, [Daily Time].Hours, DSum("[hours]","Daily Time","[state] like 'wv'") AS Total
FROM [Daily Time]
WHERE ((([Daily Time].Employee) Like "*" & [Enter all or part of the employees name] & "*") AND (([Daily Time].[Work Date]) Between [Enter Beginning Date:] And [Enter Ending Date:]) AND (([Daily Time].State) Like "*" & [Enter WV, PA, or leave blank for both] & "*"));
I'm using the last entry in the qeury as Total: DSum("[hours]","Daily Time","[state] like 'wv'"). Granted I was just checking to see if it would output what I was looking for, but it still gives me all hours worked in WV instead of the total of the hours worked in WV for the date range. I've not been able to get any type of date range to work as far as being able to prompt for a date range. It seems if the date range is put in to the dsum itself it works, but I can't go back and change the dsum values every time a different date range is needed.
FROM [Daily Time]
WHERE ((([Daily Time].Employee) Like "*" & [Enter all or part of the employees name] & "*") AND (([Daily Time].[Work Date]) Between [Enter Beginning Date:] And [Enter Ending Date:]) AND (([Daily Time].State) Like "*" & [Enter WV, PA, or leave blank for both] & "*"));
I'm using the last entry in the qeury as Total: DSum("[hours]","Daily Time","[state] like 'wv'"). Granted I was just checking to see if it would output what I was looking for, but it still gives me all hours worked in WV instead of the total of the hours worked in WV for the date range. I've not been able to get any type of date range to work as far as being able to prompt for a date range. It seems if the date range is put in to the dsum itself it works, but I can't go back and change the dsum values every time a different date range is needed.
I've tried to put criteria for the dsum but I can't get it to prompt for a date range. That' really what I'm looking to do. I tried to use
Between [Enter Beginning Date:] And [Enter Ending Date:]
but when I do that it accepts the query as having no syntax errors, but it get blank results. The query doesn't necessarily have to contain any of the syntax that is in it now. This is the first query in the database to be created and the only data in it is for testing purposes. I'd like the query to work like this. Prompt=Enter an emplyee name > Prompt=Enter starting date and then prompt for ending date for date range > Enter state= user enters WV or PA and then it outputs the records such as:
Employee Date Hours State Total Hrs
John Doe 8/4/10 2 WV 6
John Doe 8/6/10 4 WV 6
This is the result i'm looking for when running the query. The best I've got is employee, date, hours, state and total of all hours, but not the date range. How can I add a date range criteria prompt for the sum of hours?
I really appreciate your help. I've been at this for about 7 hours today.
Between [Enter Beginning Date:] And [Enter Ending Date:]
but when I do that it accepts the query as having no syntax errors, but it get blank results. The query doesn't necessarily have to contain any of the syntax that is in it now. This is the first query in the database to be created and the only data in it is for testing purposes. I'd like the query to work like this. Prompt=Enter an emplyee name > Prompt=Enter starting date and then prompt for ending date for date range > Enter state= user enters WV or PA and then it outputs the records such as:
Employee Date Hours State Total Hrs
John Doe 8/4/10 2 WV 6
John Doe 8/6/10 4 WV 6
This is the result i'm looking for when running the query. The best I've got is employee, date, hours, state and total of all hours, but not the date range. How can I add a date range criteria prompt for the sum of hours?
I really appreciate your help. I've been at this for about 7 hours today.
- Local time
- Yesterday, 22:56
- Joined
- Aug 30, 2003
- Messages
- 36,136
Personally I never use the [whatever] method of getting criteria. I have the user enter criteria on a form, and I make the query get them from there (the DSum() could too). You have a lot more control that way. Here's help on syntax:
http://access.mvps.org/access/general/gen0018.htm
That said, I probably wouldn't get the total that way, I'd just calculate it on a form/report. Domain aggregate functions like DSum() can be performance killers when used that way. If you did want that view in a query, I'd probably create a totals query that totaled up each person and join to it in this query. It would run faster on large data.
http://access.mvps.org/access/general/gen0018.htm
That said, I probably wouldn't get the total that way, I'd just calculate it on a form/report. Domain aggregate functions like DSum() can be performance killers when used that way. If you did want that view in a query, I'd probably create a totals query that totaled up each person and join to it in this query. It would run faster on large data.
So far I've made a form for the user to imput the hours, state, etc. Basically, I have a user using one of the largest excel spreadsheets i've ever seen to do payroll and keep track of jobs so they are use looking at tables. I think I will just do a form or report to where it will basically use the query I have now and then add in a separate box to total the hours instead of one query trying to do it all. thx for the help.
Thank you. I'm sure I will post again. I was really efficient with Access 2003 and now I'm having to use Access again, 7 years later, and i'm using 2007. I'm having issues sometimes doing simple tasks that use to be easy.
Similar threads
