Update a table based on a totals query

[buratti]

i have an Employees table which holds amongst the basics, Hourly Rate, Daily Pay, and Weekly Salary, fields which are used based on the employee type.

I also have a Employee Log table that holds, Work Date, Start/End Times, Hours (which is calculated after start/end times are entered), Total Pay for Day (which is calculated based on employee type), and other fields that are not relavant to this particular problem i am having.

I need to add all the "total for day" fields for each employee for each work week, and then insert that sum into a payroll table. I have created a Totals query just fine that gives me the correct amounts for each employee and each week, but when i try to use that query in an update query i get the error "Operation must use an updatable query".

How do I overcome this problem and insert those values into my payroll table like i need to?

 

[stopher]

Can you post your query (SQL)?

How are you running the query?

 

[buratti]

The SQL from the query that sums the total for day is:
The name of this query is "Payroll Calc"

SELECT Sum(EmployeeLog.Total_For_Day) AS SumOfTotal_For_Day, EmployeeLog.WorkWeekID, Employees.FName, EmployeeLog.EmployeeID, Workweek.StartingDate
FROM (Employees INNER JOIN EmployeeLog ON Employees.ID = EmployeeLog.EmployeeID) INNER JOIN Workweek ON EmployeeLog.WorkWeekID = Workweek.WorkweekID
GROUP BY EmployeeLog.WorkWeekID, Employees.FName, EmployeeLog.EmployeeID, Workweek.StartingDate;

The update query SQL is:

UPDATE [Payroll Calc] INNER JOIN Payroll ON ([Payroll Calc].StartingDate = Payroll.WorkWeek) AND ([Payroll Calc].EmployeeID = Payroll.EmployeeID) SET Payroll.PayTotal = [Payroll Calc].[SumofTotal_for_Day];

if I switch to Datasheet view on this update query it shows what "should" be updated correctly, but if i click Run, it gives me the error

In the end when i get it working the query will be ran from VBA on the click of a button

 

[stopher]

I don't know the exact reason other than there's comes a point where Access decides it can't update - even though logically you'd think you can.

Here's an alternative using Dlookup on your first query. It's not going to be as efficient though:

UPDATE Payroll 
SET Payroll.PayTotal = DLookUp("[SumOfTotal_For_Day]","[Payroll Calc]","[EmployeeID]=" & [EmployeeID] & " AND [StartingDate] = #" & Format([WorkWeek],'mm/dd/yyyy') & "#")

Maybe someone else has a better solution. I guess if you are planning to run from code then you could just your a recordset of your original query and iterate though and update the Payroll table that way.

Chris