Update Query

[Melinda]

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?

 

[KARL DEWEY]

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]

thanks for your advice

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]

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]

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]

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

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.

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]

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]

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

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.

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

:

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.

:

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?

 

[Melinda]

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?

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]

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

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.

:

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

:

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.

:

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?

 

[John Spencer]

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.

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?

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]

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

:

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.

:

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

:

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.

:

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?

 

[Melinda]

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

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.

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?

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]

:

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

:

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.

:

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

:

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.

:

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?

 

[Melinda]

One more question, if I may, I have a callout Report in which we would call
out all employees to plow snow and I need to list the employees with "0"
hours as well as employees with overtime hours. Any suggestions?

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

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.

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?

:

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]

:

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

:

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.

:

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

:

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.

:

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?

 

[John Spencer]

Isn't that what you have? IF not POST the SQL of your query.

One more question, if I may, I have a callout Report in which we would
call
out all employees to plow snow and I need to list the employees with "0"
hours as well as employees with overtime hours. Any suggestions?

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

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.

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?

:

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]

:

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

:

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.

:

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

:

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.

:

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?