Update Queries

[Melinda]

I am somewhat of a newbie and very new at having to change all the data in
one swipe. I have a overtime database that tracks overtime worked and
refused for the company. On April 1st we have to roll back their total
hours worked by the lowest number of overtime hours within their class. I
need to subtract 10 hours from the total number of overtime hours worked for
each employee. When I do an update query it subtracts 10 hours from every
entry of overtime they have. I don't show the ytd of overtime hours in the
table because from everything I read they said it was a big no no to do
calculations in the tables. What measures do I need to take to decrease
everyones overtime by 10 hours.

Thanks

Melinda

 

[Michel Walsh]

One of the possible method is to add a negative worktime time, for each
employee. I assume your query computing the total amount of overtime is
based on some SUM( overtime ), like below, so, if one of the row has -10 as
overtime, it will add that minus 10, ONCE, per employee.

SELECT employee, iif(SUM(overtime)> 0, SUM(overtime), 0 )
FROM myDate
GROUP BY employee



Hoping it may help,
Vanderghast, Access MVP

 

[Jeff Boyce]

Melinda

Won't "reducing everyone's OT hours by 10" wipe out the 'history'
information? Or is it not important to keep track of previous year's
numbers? I can imagine a question about credits that doesn't get resolved
by April 1, but it sounds like the data is being modified.

Another approach might be to keep historical info, plus create a new record
to hold the "transaction" that subtracts 10 OT hours. That way, a person's
current balance is the sum of all the previous transactions, including the
4/1/2007 -10 OT

Just one person's opinion...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Melinda]

It does indeed wipe out the history information. We keep a printout of
their ending total overtime for March 31st and then we start with the new
figure on April 1st. We really have no need to go back and look at past
years overtime. I do like the sound of your second paragraph approach and
maybe we should do it that way. The fellow that has been doing this
overtimeis in the past has wrote it in DBASE (yes we are behind just at bit)
he is retiring so I have wrote an overtime database in access, (and learning
alot, I might add) and thank god for this forum.
So anyhelp at how to roll these hours back will be greatly appreciated.

I thought I would do an update query--but that goes back and subtracts 10
hours from each every line of the table. I only want to subtract from the
total hours worked and I can't seem to accomplish that.

Thanks

 

[Jeff Boyce]

Since I can't really see the data structure, I'm not sure how you would
"subtract [10 hours] from the total hours worked".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/