Update Query - Operation must use updatable query

[kwalters110]

Hi

I am trying to create an update query but I get the following error:
Operation must use an updatable query
The query joins two a table (Daily Log) to a query (Time and Attendance) and then is supposed to update 2 fields in Daiy Log to the appropriate times in Time and Attendance for yesterday. Here is the SQL

UPDATE [Daily Log] INNER JOIN [Time And Attendance] ON [Daily Log].BADGE_TXT = [Time And Attendance].EMPLOYEE SET [Daily Log].[SCH IN] = CDate([Time And Attendance]![DATE_IN] & " " & [Time And Attendance]![TIME_IN]), [Daily Log].[SCH OUT] = CDate([Time And Attendance]![DATE_OUT] & " " & [Time And Attendance]![TIME_OUT]), [Daily Log].TA_Flag = "TA"
WHERE ((([Time And Attendance].DATE_IN)=Date()-1));

I think the cause might be the fact that Time and Attendance is actually a union of 2 views (non-updateable) from my server. I am unioning Current T&A with the history to get a complete collection of data to run this against. Anyone thing I might be on the right track with that being the problem? and if so, anyone have any good ideas to get around it?

-Keith

 

[Crowley16]

do you have to use JOIN?

can you not use a subQuery and WHERE instead?

I find that if you use a JOIN, then you usually can't update, there are exceptions, but they are rare...

without analysing your query, I can only suggest, try to remove any joins, and use a mix of WHERE and subQuery or saved query to find what rows to update.

and if you have to join, then only join on 1-1 relationships...

--------------------
Procrastinate Now!

 

[kwalters110]

Turns out the problem was with using a Union query. I removed Time and Attendance from the union and the join and update worked just fine