If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
I am writing an overtime program for overtime usage. I have a table for
employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#2
|
|||
|
|||
Update Query
Suggest you not do it. Storing calculated data is bad as the data is always
changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#3
|
|||
|
|||
Update Query
thanks for your advice
"KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#4
|
|||
|
|||
Update Query
I thought about this a bit more overnight, I see what you are saying and I am
collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#5
|
|||
|
|||
Update Query
Use this --
Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#6
|
|||
|
|||
Update Query
In this database I am only tracking overtime hours per our union contract so
that people can be called out to plow snow appropriately. I am tracking each hour of overtime that is worked and I am running a query to total the hours, but if the employee has a "0" balance for overtime they do not show up on my callout report when really they should be at the top of the list and being given the first opportunity to plow. I used the Nz([Hours Worked],0) and the query ran as a parameter query and then had no results. I was putting that in the criteria and my hours worked are being sumed. Melinda "KARL DEWEY" wrote: Use this -- Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#7
|
|||
|
|||
Update Query
It is not a criteria but a calculated field like this --
Number of hours worked: Nz([Hours Worked],0) If you are placing it in your SQL then -- Nz([Hours Worked],0) AS [Number of hours worked] OR Sum(Nz([Hours Worked],0)) AS [Number of hours worked] "Melinda" wrote: In this database I am only tracking overtime hours per our union contract so that people can be called out to plow snow appropriately. I am tracking each hour of overtime that is worked and I am running a query to total the hours, but if the employee has a "0" balance for overtime they do not show up on my callout report when really they should be at the top of the list and being given the first opportunity to plow. I used the Nz([Hours Worked],0) and the query ran as a parameter query and then had no results. I was putting that in the criteria and my hours worked are being sumed. Melinda "KARL DEWEY" wrote: Use this -- Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#8
|
|||
|
|||
Update Query
That worked great, but it replaced everyone's hours with "0". I need to
bring in the year to date overtime hours of those employees who have a total and if your total is "0" I need to also reflect that. Does that make sense? "KARL DEWEY" wrote: It is not a criteria but a calculated field like this -- Number of hours worked: Nz([Hours Worked],0) If you are placing it in your SQL then -- Nz([Hours Worked],0) AS [Number of hours worked] OR Sum(Nz([Hours Worked],0)) AS [Number of hours worked] "Melinda" wrote: In this database I am only tracking overtime hours per our union contract so that people can be called out to plow snow appropriately. I am tracking each hour of overtime that is worked and I am running a query to total the hours, but if the employee has a "0" balance for overtime they do not show up on my callout report when really they should be at the top of the list and being given the first opportunity to plow. I used the Nz([Hours Worked],0) and the query ran as a parameter query and then had no results. I was putting that in the criteria and my hours worked are being sumed. Melinda "KARL DEWEY" wrote: Use this -- Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#9
|
|||
|
|||
Update Query
Pardon me for jumping in.
If so, add the Employee table to your query. Join the Table to the WorkHours Table on the "EmployeeID" fields. Double click on the line linking the two and choose All records from Employee table and only matching records from the Workhours table. Otherwise post your SQL statement. (Open query in design mode, Select View: SQL from the menu; copy and paste) In addition tell us a bit about the structure of the employee table. Which field is the Primary Key in the tEmployee able and which field is the foreign Key in the Hours worked table. "Melinda" wrote in message ... That worked great, but it replaced everyone's hours with "0". I need to bring in the year to date overtime hours of those employees who have a total and if your total is "0" I need to also reflect that. Does that make sense? "KARL DEWEY" wrote: It is not a criteria but a calculated field like this -- Number of hours worked: Nz([Hours Worked],0) If you are placing it in your SQL then -- Nz([Hours Worked],0) AS [Number of hours worked] OR Sum(Nz([Hours Worked],0)) AS [Number of hours worked] "Melinda" wrote: In this database I am only tracking overtime hours per our union contract so that people can be called out to plow snow appropriately. I am tracking each hour of overtime that is worked and I am running a query to total the hours, but if the employee has a "0" balance for overtime they do not show up on my callout report when really they should be at the top of the list and being given the first opportunity to plow. I used the Nz([Hours Worked],0) and the query ran as a parameter query and then had no results. I was putting that in the criteria and my hours worked are being sumed. Melinda "KARL DEWEY" wrote: Use this -- Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
#10
|
|||
|
|||
Update Query
Hey---I think this might of worked. I will keep working on it, but it
looks as if the employees with a 0 balance of overtime populated in the query. Thanks so much "John Spencer" wrote: Pardon me for jumping in. If so, add the Employee table to your query. Join the Table to the WorkHours Table on the "EmployeeID" fields. Double click on the line linking the two and choose All records from Employee table and only matching records from the Workhours table. Otherwise post your SQL statement. (Open query in design mode, Select View: SQL from the menu; copy and paste) In addition tell us a bit about the structure of the employee table. Which field is the Primary Key in the tEmployee able and which field is the foreign Key in the Hours worked table. "Melinda" wrote in message ... That worked great, but it replaced everyone's hours with "0". I need to bring in the year to date overtime hours of those employees who have a total and if your total is "0" I need to also reflect that. Does that make sense? "KARL DEWEY" wrote: It is not a criteria but a calculated field like this -- Number of hours worked: Nz([Hours Worked],0) If you are placing it in your SQL then -- Nz([Hours Worked],0) AS [Number of hours worked] OR Sum(Nz([Hours Worked],0)) AS [Number of hours worked] "Melinda" wrote: In this database I am only tracking overtime hours per our union contract so that people can be called out to plow snow appropriately. I am tracking each hour of overtime that is worked and I am running a query to total the hours, but if the employee has a "0" balance for overtime they do not show up on my callout report when really they should be at the top of the list and being given the first opportunity to plow. I used the Nz([Hours Worked],0) and the query ran as a parameter query and then had no results. I was putting that in the criteria and my hours worked are being sumed. Melinda "KARL DEWEY" wrote: Use this -- Nz([Field1],0) I returns a zero if the field is null. I do not know how your company does time accounting but many use a charge code for their projects/activities, another for vaction, sick, and a separate field for overtime or donated time. They also have a field for speciality pay such as hazardous duty, shift differential, Quality Checker. The entry would look like this -- WeekEnding EmpID ChargeCode Hours TypeHRS SpecPay This data is collected by payroll/finance and downloaded for managers to do analysis on projects, labor trends, etc. "Melinda" wrote: I thought about this a bit more overnight, I see what you are saying and I am collecting each instance of overtime in the hours table, but how would I show the employees who have a year to date total overtime of "0" in that query. I need to have a overtime line for each employee whether they have hours or not. Thanks "KARL DEWEY" wrote: Suggest you not do it. Storing calculated data is bad as the data is always changing. What if someone updated it and then another person updates it again? Best to have a table collecting each instance of overtime and then sum it in a query when you need the information and therefore it will always be current. "Melinda" wrote: I am writing an overtime program for overtime usage. I have a table for employees and another table for overtime hours. I want to update the employee table to always have the current total overtime hours that the employee has year to date. The hours being entered into the overtime hour form is added into the hours table. Any suggestions? |
|
Thread Tools | |
Display Modes | |
|
|