Urgent: Please help!

[Guest]

I have an employee table and a T&E table. I use an update query to update
the employee rates from the employee table to the T&E table. Rates change
every year. So I use the formula, if the week endings are in 2005, use Rate
05 from the employee table and so on. However, it's not working.

Please help. Below is my sql statement for my update query. Thanks.

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate = IIf([T&E
Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],IIf([T&E
Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],IIf([T&E
Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

 

[John Vinson]

I have an employee table and a T&E table. I use an update query to update
the employee rates from the employee table to the T&E table. Rates change
every year. So I use the formula, if the week endings are in 2005, use Rate
05 from the employee table and so on. However, it's not working.

In what way is it "not working"? Are you getting an error message,
wrong values, no records updated, or what? Give us a bit more help
here!

John W. Vinson[MVP]

 

[Guest]

Hi John,

Thanks for your response. I didn't get any error messages, and it does
update. It updates the 2006 rate, not 2005 or 2004 rates. For example, for
the week ending in 2004 (1/3/04), it updates as $100, instead of $80.

Thanks.

 

[Van T. Dinh]

I think you need to check your greater than > and less than > signs and the
effects of the nested IIf. The way you set the SQL String at the moment, a
date in 2004 will get the 2005 rate since it satisfies the second condition,
i.e.

[T&E Table]![Weekend]<#1/1/2006#

and therefore get the 2005 rate. Since it satisfies the second condition,
Access won't even check the 3rd condition <#1/1/2005# in this case.

My guess is that the SQL String should be something like:

UPDATE [T&E Table] LEFT JOIN EMPLOYEE
ON [T&E Table].[Emp No] = EMPLOYEE.EMPNUMBE
SET [T&E Table].Rate = IIf(
[T&E Table]![Weekend] <= #12/31/2006#,[EMPLOYEE]![RATE 06],
IIf([T&E Table]![Weekend] <= #12/31/2005#,[EMPLOYEE]![RATE 05],
IIf([T&E Table]![Weekend] <= #12/31/2004#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

OTOH, I am not sure how you use this query but if you use this regularly for
a deployed database application, the use of explicit date values means that
you have to modify this query every year and this is certainly not
appropriate for a real-life database application.

 

[Guest]

Thanks Van. It works.

I do have one more question from your response. Yes, I agree with you that
I have to change my update query every year to update the current year rate.
Please share with me what would be the real-life approach.

Thanks.

I think you need to check your greater than > and less than > signs and the
effects of the nested IIf. The way you set the SQL String at the moment, a
date in 2004 will get the 2005 rate since it satisfies the second condition,
i.e.

[T&E Table]![Weekend]<#1/1/2006#

and therefore get the 2005 rate. Since it satisfies the second condition,
Access won't even check the 3rd condition <#1/1/2005# in this case.

My guess is that the SQL String should be something like:

UPDATE [T&E Table] LEFT JOIN EMPLOYEE
ON [T&E Table].[Emp No] = EMPLOYEE.EMPNUMBE
SET [T&E Table].Rate = IIf(
[T&E Table]![Weekend] <= #12/31/2006#,[EMPLOYEE]![RATE 06],
IIf([T&E Table]![Weekend] <= #12/31/2005#,[EMPLOYEE]![RATE 05],
IIf([T&E Table]![Weekend] <= #12/31/2004#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

OTOH, I am not sure how you use this query but if you use this regularly for
a deployed database application, the use of explicit date values means that
you have to modify this query every year and this is certainly not
appropriate for a real-life database application.

--
HTH
Van T. Dinh
MVP (Access)

I have an employee table and a T&E table. I use an update query to update
the employee rates from the employee table to the T&E table. Rates change
every year. So I use the formula, if the week endings are in 2005, use
Rate
05 from the employee table and so on. However, it's not working.

Please help. Below is my sql statement for my update query. Thanks.

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate = IIf([T&E
Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],IIf([T&E
Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],IIf([T&E
Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

 

[John Vinson]

I have an employee table and a T&E table. I use an update query to update
the employee rates from the employee table to the T&E table. Rates change
every year. So I use the formula, if the week endings are in 2005, use Rate
05 from the employee table and so on. However, it's not working.

Please help. Below is my sql statement for my update query. Thanks.

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate = IIf([T&E
Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],IIf([T&E
Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],IIf([T&E
Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

Well, I'd object (strongly!) to a table containing data in fieldnames
as you have done; but if you must use such spreadsheet logic, you can
perhaps to better by using the Switch() function rather than nested
IIF's. Switch() takes arguments in pairs; reading left to right
through the list of arguments, it returns the second argument of the
pair when the first of the pair is TRUE, and then quits:

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate =
Switch(
[T&E Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],
[T&E Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],
[T&E Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],
True, 0)
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

Note the reversal of order - #8/15/2004# is in fact less than
#1/1/2006# so you should check earliest date first.

John W. Vinson[MVP]

 

[Van T. Dinh]

Without knowing your Table Structure, it is hard to answer. However, in
general, you should store "effective date(s)" for rates so basically you
have dates for relevant Tables. In the Query, you compare the date values
from different Tables rather than explicit date values.

For example, let's say that I have an Table tblRate for rates per hour for
the work I do (and I charge different rates to different Clients, then I
would have Fields like:

RateID AutoNum PK
frg_ClientID Long FK
Rate Currency
EffectiveDate DateTime

When I want to work out the applicable rate (some small clients I bill only
once a year), I simply compare the date I work with the EffectiveDate to
work out the rate I should charge.

 

[Guest]

Thanks both of you. Both of you gives good advices.

I have an employee table and a T&E table. I use an update query to update
the employee rates from the employee table to the T&E table. Rates change
every year. So I use the formula, if the week endings are in 2005, use Rate
05 from the employee table and so on. However, it's not working.

Please help. Below is my sql statement for my update query. Thanks.

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate = IIf([T&E
Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],IIf([T&E
Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],IIf([T&E
Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],0)))
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

Well, I'd object (strongly!) to a table containing data in fieldnames
as you have done; but if you must use such spreadsheet logic, you can
perhaps to better by using the Switch() function rather than nested
IIF's. Switch() takes arguments in pairs; reading left to right
through the list of arguments, it returns the second argument of the
pair when the first of the pair is TRUE, and then quits:

UPDATE [T&E Table] LEFT JOIN EMPLOYEE ON [T&E Table].[Emp No] =
EMPLOYEE.EMPNUMBE SET [T&E Table].Rate =
Switch(
[T&E Table]![Weekend]<#1/1/2005#,[EMPLOYEE]![RATE 04],
[T&E Table]![Weekend]<#1/1/2006#,[EMPLOYEE]![RATE 05],
[T&E Table]![Weekend]>#12/31/2005#,[EMPLOYEE]![RATE 06],
True, 0)
WHERE ((([T&E Table].ServNumber) Not In (50,51,53,92)));

Note the reversal of order - #8/15/2004# is in fact less than
#1/1/2006# so you should check earliest date first.

John W. Vinson[MVP]