Having a problem with Access: update query updates zero rows

Question

I have my Table set up with last name, first name, hours, rate, and years - with the objective being an IIf query to update Gross field

IIf([Hours]<=40,[Gross]=[Hours]*[Rate],[Gross]=(([Hours]-40)*([Rate]*1.5))+([Rate]*40))

I get the message that 0 rows are about to update.

I have tried putting the IF-THEN-ELSE in the criteria spot on the query design, I have tried it in the Field area with Gross: preceeding it, which throws a different message, and so many other ways.

Exactly WHAT do I need to do?

Answer 1

Good point Ken, I wasn't looking far enough.

Answer 2

John and Scott would be right if it can be guaranteed that the standard working week will be 40 hours, and the overtime rate will be 1.5 until the end of time.  I doubt that's a valid assumption, but even if we assume that it is, then encoding the length of the standard working week and the overtime rate in an expression is not good practice.  It is data, and a fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The length of the standard working week should be a value at a column position in a row in a table.  If the above assumption can be made then the table would have a single row and a single column.  The same is true of the overtime rate of 1.5, which also, as data, should be a value in a table.  The gross pay would then be computed in the same by including these two tables in the query, but not joined to any other table.  The expression would then reference the columns in the two tables in place of the literal values of 40 and 1.5

On the other hand, if the assumption is not valid (more likely in my experience), then the working week hours table, and the overtime rates tables would each also have DateFrom and DateTo columns.  The two tables would then be joined to the timesheet table on its relevant date column being between the start and end dates in the other two tables respectively.

The fundamental point which John and Scott are making is correct in both scenarios, i.e. that the gross weekly pay should not be stored at a column position in a row in a base table, but computed from the stored data on which it is based.

Answer 3

Exactly WHAT do I need to do?

Correct your flawed table design.

Don't store Gross. Its a calculated value so shouldn't be stored. Also you should not have the name fields in this table. You should have an employees field with an employeeID. Only the EmployeeID should be in this table. And you refer to a Years field, but you appear to be calculating on a weekly basis.

So you table should look like this:

tblWeeklyPay

WeeklyPayID (PK Autonumber)

EmployeeID (FK)

WeekEnding  (or weekBeginning)

Hours

Rate

The create 2 queries:

qryRegOTHours

SELECT EmployeeID, WeekEnding Rate, IIf(Hours>40,40,Hours) As RegHrs, IIF(Hours<=40,0,Hours-40) AS OTHrs

FROM tblWeeklyPay;

qryRegOTPay

SELECT EmployeeID, WeekEnding, RegHrs*Rate as RegPay, OTHrs*Rate AS OTPay

FROM qryRegOTHrs;

You can do it in one query, but this is easier to follow the logic and debug.

Answer 4

Well... I'd recommend "unasking" the question. This kind of calculation should generally NOT be stored in any table, anywhere; just store the underlying data and calculate it (by putting the expression in a Field cell in a query, or preceded by an equals sign in the Control Source of a textbox on a Form or Report) whenever you need it.

Storing calculated values risks update anomalies: if one of the underlying fields is edited, your Gross value will now be Grossly wrong (sorry!) with no easy way to detect the error. Computers love to do calculations; that's what they're built for! 

If you have some GOOD reason (such as wanting to be able to override the calculation for some records) then Hans' suggestion is of course correct; but I don't think your accountants or the Labor Relations Board would approve.

Answer 5

In the Update to: row of the Gross column, use

IIf([Hours]<=40,[Hours]*[Rate],([Hours]-40)*[Rate]*1.5+[Rate]*40)

or

IIf([Hours]<=40,[Hours],([Hours]-40)*1.5+40)*[Rate]

or even

IIf([Hours]<=40,[Hours],[Hours]*1.5-20)*[Rate]