Update query messed up, looking for a quicker solution

[iuianj07]

Hello guys,

I need help with a messed up update query I did today... I had the wrong approach on how to correctly use an update query, and I know now how to write it correctly, however there are about 53 total fields I need to re-write per query, and I have to do it for 3 different queries again.

Basically, what I did was (I will use a simple table structure and query):

Table1

ID a b c
1 s s s
2 d s d
3 g h s

example this is the table data that I have. what I want to do is update all "s" to 0, the query I wrote was:

UPDATE Table1 SET Table1.a = "0", Table1.b = "0", Table1.c = "0"
WHERE (((Table1.a)="s")) OR (((Table1.b)="s")) OR (((Table1.c)="s"));

and when I run this, every record updates to 0...

What I need though is only "s" updates to 0... so I now know that I should re-write the query as:

UPDATE Table1 SET Table1.a = IIf([Table1].[a]="s",0,[Table1].[a]), Table1.b = IIf([Table1].[b]="s",0,[Table1].[b]), Table1.c = IIf([Table1].[c]="s",0,[Table1].[c]);

and this gives me the correct results that I need.

My question now though is that, is this the only way for me to write the query correctly? or are there any other options that I could choose to re-write 53 fields in 3 different queries?

I know that this was my stupid mistake and I take the blame for my logic error...

But any help is greatly appreciated...

 

[dhookom]

Updating 53 fields like this sounds a bit un-normalized. A wild guess suggests you have 53 fields for 53 weeks.

If you are using only one table and your field names don't have spaces, you don't need to use:

  Table1.a = IIf([Table1].[a]="s",0,[Table1].[a]),

  a = IIf(a="s",0,a),

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As Duane suggests most likely your data is not normal, and instead of 53 records you have 53 fields making this overly complicated. Better off fixing it in my opinion

WIth that said, I am not bothering writing some mammoth query. The below is untested but the concept is correct.


dim rs as dao.recordset
dim i as integer
set rs = currentdb.openrecordset("table1",dbopendynaset)

'assumes the fields in question are fields 1 - 53
'adjust as needed

while not rs.eof
for i = 1 to 53
if rs.fields(i) = "S" then
rs.edit
rs.fields(I) = "0"
rs.update
end if
next i
rs.movenext
loop


Just so you know you can update to "0" but not 0. The field is text.

 

[iuianj07]

Thank you both MajP and dhookom,

well it is 53 fields coming from different tables and just being used in one query. Thanks for both of your help

 

[PWise]

Well you can write the update Sql in code

dim mydb as database 
dim rst as recordset 
Dim SqlStr as string
dim iifStr As string
Dim Fld As Field
set mydb= currentdb

Set rst = mydb.openrecordset("Table1")
Sqlstr = "UPDATE Table1 SET"
iifStr ="Table1.~ = IIf([Table1].[~]='s',0,[Table1].[~]), "
for each fld in rst.fields
Sqlstr =SqlStr & replace(iifStr,"~",fld.name )
next
SqlStr = trim(SqlStr)
SqlStr = left(sqlstr,len(sqlstr)-1)
mydb.execute SqlStr