Update Query to add an value from an expression to an existing field

[Guest]

I have been trying to create an update query that will do the following

Calculate the number of days of holidays accrued in a 28 day period [Expr2] and then add it to a value in a field [PermanentAccrual]. Each time the update query is run it will add the value to [PermanentAccrual] to increment it through a year

The select query I created looks like this (SQL taken from Access QBE

SELECT Employees.EmployeeID, Employees.EmployeeName, tblWeekDates.WeekNo, tblWeekDates.WeekEndingDate, tblWeekDates.WeekCommencingDate, Employees.EmploymentStatus, DateDiff("d",Date()-28,Date()) AS Expr1, Employees.AverageWorkedDays, IIf([AverageWorkedDays]=5,([Expr1]/7)*0.38,(IIf([AverageWorkedDays]=4,([Expr1]/7)*0.31,(IIf([AverageWorkedDays]=3,([Expr1]/7)*0.23,(IIf([AverageWorkedDays]=2,([Expr1]/7)*0.15,(IIf([AverageWorkedDays]=1,([Expr1]/7)*0.08,0))))))))) AS Expr2, [PermanentAccrual]+[Expr2] AS Expr
FROM tblWeekDates, Employees INNER JOIN tblEntitlements ON Employees.EmployeeID = tblEntitlements.EmployeeI
WHERE (((tblWeekDates.WeekNo)=1) AND ((Employees.EmploymentStatus)="Currently Employed" Or (Employees.EmploymentStatus)="Maternity Leave"))

(Expr2 calculates the rate of accrual based on the value [AverageWorkedDays]
(expr3 calculates the value that [PermanentAccrual] should be updated to

This stage works correctly, however when I convert to an update query it does not update the value of [PermanetAccrual
this is the SQL of the update quer

UPDATE tblWeekDates, Employees INNER JOIN tblEntitlements ON Employees.EmployeeID = tblEntitlements.EmployeeID SET tblEntitlements.PermanentAccrual = [Expr3
WHERE (((tblWeekDates.WeekNo)=1) AND ((Employees.EmploymentStatus)="Currently Employed" Or (Employees.EmploymentStatus)="Maternity Leave"))

The values returned by the update query are incorrect. sorry for the length of this request but can anybody hel
Many Thank
Nea

 

[Gary Walter]

Hi Neal,

I'm not sure I fully understand,
but since no other solutions have
been given......

One method would be to save your
first query (which you have said works
correctly) as say "qryHolDaysAccrued"

Then, I believe, your update query would be:

UPDATE tblEntitlements INNER JOIN qryHolDaysAccrued
ON tblEntitlements.EmployeeID=qryHolDaysAccrued.EmployeeID
SET tblEntitlements.PermanentAccrual = [Expr3];

I could be wrong and there could be
a more clever way.

Gary Walter

I have been trying to create an update query that will do the following:

Calculate the number of days of holidays accrued in a 28 day period [Expr2] and

then add it to a value in a field [PermanentAccrual]. Each time the update query is
run it will add the value to [PermanentAccrual] to increment it through a year.

The select query I created looks like this (SQL taken from Access QBE)

SELECT Employees.EmployeeID, Employees.EmployeeName, tblWeekDates.WeekNo,

tblWeekDates.WeekEndingDate, tblWeekDates.WeekCommencingDate,
Employees.EmploymentStatus, DateDiff("d",Date()-28,Date()) AS Expr1,
Employees.AverageWorkedDays,
IIf([AverageWorkedDays]=5,([Expr1]/7)*0.38,(IIf([AverageWorkedDays]=4,([Expr1]/7)*0.3
1,(IIf([AverageWorkedDays]=3,([Expr1]/7)*0.23,(IIf([AverageWorkedDays]=2,([Expr1]/7)*
0.15,(IIf([AverageWorkedDays]=1,([Expr1]/7)*0.08,0))))))))) AS Expr2,
[PermanentAccrual]+[Expr2] AS Expr3

FROM tblWeekDates, Employees INNER JOIN tblEntitlements ON Employees.EmployeeID = tblEntitlements.EmployeeID
WHERE (((tblWeekDates.WeekNo)=1) AND ((Employees.EmploymentStatus)="Currently

Employed" Or (Employees.EmploymentStatus)="Maternity Leave"));

(Expr2 calculates the rate of accrual based on the value [AverageWorkedDays])
(expr3 calculates the value that [PermanentAccrual] should be updated to)

This stage works correctly, however when I convert to an update query it does not

update the value of [PermanetAccrual]

this is the SQL of the update query

UPDATE tblWeekDates, Employees INNER JOIN tblEntitlements ON Employees.EmployeeID =

tblEntitlements.EmployeeID SET tblEntitlements.PermanentAccrual = [Expr3]

WHERE (((tblWeekDates.WeekNo)=1) AND ((Employees.EmploymentStatus)="Currently

Employed" Or (Employees.EmploymentStatus)="Maternity Leave"));