update query - 5 columns at once

[John1Chr]

Hi all,

I'm trying to update 5 columns on a table (tbl_bb_sqft_1a)in a query by linking this table by Brkey to a previous select query that has two fields that identify when to post the amount to the field. My question is - can one do an updateable query like this? I keep getting the error -"Operation must use an updateable query"


SQL is:

UPDATE tbl_bb_sqft_1a INNER JOIN qry_bb_sqft_1c ON tbl_bb_sqft_1a.BRKEY = qry_bb_sqft_1c.[SP TAIL] SET tbl_bb_sqft_1a.[BR Capitalize] = IIf([qry_bb_sqft_1c]![Capital_Ind]="CAPITAL" And [qry_bb_sqft_1c]![Structure Ind]="BRIDGE",[qry_bb_sqft_1c]![JFL Cost Amt],0), tbl_bb_sqft_1a.[BR Sys Pres] = IIf([qry_bb_sqft_1c].[Capital_Ind]="OTHER" And [qry_bb_sqft_1c].[Structure Ind]="BRIDGE",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.[Road Capitalize] = IIf([qry_bb_sqft_1c].[Capital_Ind]="CAPITAL" And [qry_bb_sqft_1c].[Structure Ind]="ROADWAY",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.[Road Sys Pres] = IIf([qry_bb_sqft_1c].[Capital_Ind]="OTHER" And [qry_bb_sqft_1c].[Structure Ind]="ROADWAY",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.unassigned = IIf([qry_bb_sqft_1c].[Capital_Ind]="" And [qry_bb_sqft_1c].[Structure Ind]="",[qry_bb_sqft_1c].[JFL Cost Amt],0);

 

[lameid]

Access is dumb about this sort of thing. It will only run if both sides of the Join are updateable. Try the ANSI-92 SQL Syntax...

UPDATE tbl_bb_sqft_1a
From tbl_bb_sqft_1a INNER JOIN qry_bb_sqft_1c ON tbl_bb_sqft_1a.BRKEY = qry_bb_sqft_1c.[SP TAIL] 

SET tbl_bb_sqft_1a.[BR Capitalize] = IIf([qry_bb_sqft_1c]![Capital_Ind]="CAPITAL" And [qry_bb_sqft_1c]![Structure Ind]="BRIDGE",[qry_bb_sqft_1c]![JFL Cost Amt],0), tbl_bb_sqft_1a.[BR Sys Pres] = IIf([qry_bb_sqft_1c].[Capital_Ind]="OTHER" And [qry_bb_sqft_1c].[Structure Ind]="BRIDGE",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.[Road Capitalize] = IIf([qry_bb_sqft_1c].[Capital_Ind]="CAPITAL" And [qry_bb_sqft_1c].[Structure Ind]="ROADWAY",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.[Road Sys Pres] = IIf([qry_bb_sqft_1c].[Capital_Ind]="OTHER" And [qry_bb_sqft_1c].[Structure Ind]="ROADWAY",[qry_bb_sqft_1c].[JFL Cost Amt],0), tbl_bb_sqft_1a.unassigned = IIf([qry_bb_sqft_1c].[Capital_Ind]="" And [qry_bb_sqft_1c].[Structure Ind]="",[qry_bb_sqft_1c].[JFL Cost Amt],0);

Your other alternatives would be to use Recordsets to update the data OR change your subqueries to maketable queries and use the resulting tables in your query. The latter option is the quicker solution but not good for repeated operations.