 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Month Totals
I have a query that has the names of employees and the dates and hrs. they did a job, about 10,000 entries from Jan to present. How do I make a query total the time by month for each employee? Thanks! SELECT [Employee], Format([WorkDate],"yyyymm") as YrMth, Sum([WorkHours]) As TotalWorkHours FROM [a] GROUP BY [Employee], Format([WorkDate],"yyyymm"); If you can't get this to work, come back with some actual names.  SignatureDuane Hookom Microsoft Access MVP > I have a query that has the names of employees and the dates and hrs. they > did a job, about 10,000 entries from Jan to present. How do I make a query > total the time by month for each employee? > > Thanks! Thank you Duane, that works and I understand it some what! With that working I have another question. I want to divide the number of hours a employee is at the job by the number of hour in the work month to determine the percent of time they are present for duty. Should I establish a variable and an "if" saying if the month = "Nov" the number of work hours = 152 and if so, would you show me how to do that?? Thank You > SELECT [Employee], Format([WorkDate],"yyyymm") as YrMth, Sum([WorkHours]) As > TotalWorkHours [quoted text clipped - 8 lines] > > > > Thanks! I would create a table of Years and Months with a column for the number of work hours: tblMthWorkHours =============== WorkYear numeric WorkMonth numeric WorkHours numeric You could then use this in your query to join your records to the appropriate record to grab the WorkHours. SignatureDuane Hookom Microsoft Access MVP > Thank you Duane, that works and I understand it some what! With that working > I have another question. I want to divide the number of hours a employee is [quoted text clipped - 17 lines] > > > > > > Thanks! Duane, Hi. I'm sorry but in looking at the the query, it does not work, probably because I did not explain what I want correctly. I have 2 tables, one named Name with "NameId" as the PK and a field called "Name", the other table is named "Slips", it has 10 fields of the different types of leave a person can take and it also has the "NameId" PK from the other table. When I run the query it does not filter the name I type in. Can you help me, again?? Thank You Very Much > I would create a table of Years and Months with a column for the number of > work hours: [quoted text clipped - 29 lines] > > > > > > > > Thanks! To be totally honest, I would not do anything with your application until: 1) you change the names of objects. "Name" is property of almost all objects in Access and should never be used as the name given to anything. 2) "10 fields of the different types of leave" normalize your table structure. What happens with your application when a new type of leave is created? You shouldn't have to change tables, forms, queries, reports,... when your business changes. I don't understand the remainder of your reply. Can you create a query that sums employee hours by month? You don't have any date fields so you can't get monthly totals. SignatureDuane Hookom Microsoft Access MVP > Duane, Hi. I'm sorry but in looking at the the query, it does not work, > probably because I did not explain what I want correctly. I have 2 tables, [quoted text clipped - 38 lines] > > > > > > > > > > Thanks! Ok, I understand changing "name", are you saying I should use a drop down for the type of leave a person uses? There's only 9 types of leave a person can take so I added a field named "other" just in case something changed. I have field Named VacDate in the slips table. Sorry, this is such an exacting science. I will do better. It should be 2 tables, correct?? Thank You > To be totally honest, I would not do anything with your application until: > 1) you change the names of objects. "Name" is property of almost all objects [quoted text clipped - 50 lines] > > > > > > > > > > > > Thanks! You should not create fields with the names of leave types. Each "leave event" should create a record in a new table with a structure like: tblEmployeeLeave ================== EmpLeaveID autonumber primary key EmployeeID links to your table of employees LeaveType either the value like "Sick", "PTO",... or a link to the primary key value of a table of leave types LeaveHours Numeric double for recording the number of hours There are lots of great resources on the web regarding normalization. For instance http://www.datamodel.org/NormalizationRules.html and http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101 SignatureDuane Hookom Microsoft Access MVP > Ok, I understand changing "name", are you saying I should use a drop down > for the type of leave a person uses? There's only 9 types of leave a person [quoted text clipped - 58 lines] > > > > > > > > > > > > > > Thanks! Duane, Hi. I now have 4 tables, tblEmployee with 2 fields "EmployeeId-(Pk)" & "EmployeeName", tblSlips with 5 fields "SlipId-(Pk)", "EmployeeId-(Fk)", "LeaveDate", "LeaveType" & "LeaveHrs", tblEmployeeLookup, 1 field "EmployeeName-(Pk)" & tblLvTypeLookUp, 1 field "LeaveType-(Pk)" > You should not create fields with the names of leave types. Each "leave > event" should create a record in a new table with a structure like: [quoted text clipped - 73 lines] > > > > > > > > > > > > > > > > Thanks! I'm not sure why you would need tblEmployeeLookup since I would expect it to have the same number of records as tblEmployee. Otherwise, this is a much improved structure and naming convention :-) SignatureDuane Hookom Microsoft Access MVP > Duane, Hi. I now have 4 tables, tblEmployee with 2 fields "EmployeeId-(Pk)" > & "EmployeeName", tblSlips with 5 fields "SlipId-(Pk)", "EmployeeId-(Fk)", [quoted text clipped - 78 lines] > > > > > > > > > > > > > > > > > > Thanks! Duane, Hi. I made the tblEmployeeLookup so I could create a form for new employees. Also I have a table named tblMonths with 4 fields, "MonthId-(Pk), workYear, WorkMonth, & WorkDays. I entered 5 years of data, workyear=2007, workmonth=01, & workdays=22, 60 records from 2005 thru 2009, is that correct? Thank You > I'm not sure why you would need tblEmployeeLookup since I would expect it to > have the same number of records as tblEmployee. Otherwise, this is a much [quoted text clipped - 82 lines] > > > > > > > > > > > > > > > > > > > > Thanks! Why not add new employees to a form bound to tblEmployees? tblMonths looks good. I would expect all fields to be numeric. SignatureDuane Hookom Microsoft Access MVP > Duane, Hi. I made the tblEmployeeLookup so I could create a form for new > employees. Also I have a table named tblMonths with 4 fields, "MonthId-(Pk), [quoted text clipped - 89 lines] > > > > > > > > > > > > > > > > > > > > > > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.