Microsoft Access
--
Questions
--
Followers
Top Experts
Can't figure out Insert SQL command with subquery
Hi,
I'm trying to write an Insert statement in Access where the value the field is set to is determined by a subquery. I have two tables, the target table, tblShiftDetails that has fields shiftId(integer) and Shift (datetime); and the other table is tblShift that has fields ShiftId (integer), ShiftStart and ShiftEnd.
What I want to do is:
update tblShiftDetails
set shiftId = (select ShiftId from tblShift where Shift between ShiftStart and shiftEnd)
I get an "Operation must use an updateable query" error.
I'm trying to write an Insert statement in Access where the value the field is set to is determined by a subquery. I have two tables, the target table, tblShiftDetails that has fields shiftId(integer) and Shift (datetime); and the other table is tblShift that has fields ShiftId (integer), ShiftStart and ShiftEnd.
What I want to do is:
update tblShiftDetails
set shiftId = (select ShiftId from tblShift where Shift between ShiftStart and shiftEnd)
I get an "Operation must use an updateable query" error.
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Hello jseneris,
There is a possibility that your subquery returns more than one record in tblShift for each record
in tblShiftDetails, which would be a big no-no.
Regards,
Patrick
There is a possibility that your subquery returns more than one record in tblShift for each record
in tblShiftDetails, which would be a big no-no.
Regards,
Patrick
I have found that many queries that I would LIKE to use as an update query will not allow me for the same reason.
My solution is to convert the query into a make table query, creating a temp table, then running the update using a second query and this new table.
My solution is to convert the query into a make table query, creating a temp table, then running the update using a second query and this new table.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.