help writing an UpDate query 1

[teach314]

I need help writing an UPDATE query. Here is some background:

tblA has fields ID_A (integer), YN (boolean), etc..
25 -1
26 0
... etc ...

tblB has 12 fields ID_B ID1 ID2 ID3 ID4 ID5...
2 12 25 31 46 60....
3 10 12 19 26 28....
26 14 16 31 32 47....
... etc ...

If tblA.ID_A = 26, for example, I want to set the value of YN to NOT YN (ie, -1), for all records where tblA.ID_A = 14 or 16 or 31 or 32 or 47 or ... (values from tblB).

I've been trying something like...

strUpDate = "UPDATE tblA SET YN = NOT " & YN & " WHERE tblA.ID IN (" ???????? ")"
CurrentDb.Execute strUpDate

I can't get the ????? part, and I'm not even sure this is the best approach. Any ideas how to do this properly are appreciated.

Teach314

 

[dhookom]

It would probably be much easier if your data was normalized. IMO, the 12 fields should be 12 records in a related table.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

dhookom - thanks for the advice. I've got a lot to learn! So that I'm clear, I'll

a) make tblB like
ID_B ID_X
2 12
2 25
......
26 14
26 16
26 31
... etc ...

b) join tblA.ID_A and tblB.ID_B

c) then use code like...

strUpDate = "UPDATE tblA SET YN = NOT " & YN & " WHERE tblA.ID_A = tblB.ID_X"
CurrentDb.Execute strUpDate

Is that the idea? I'm a little uncertain about the WHERE part.

Thanks

 

[dhookom]

That is where I would start. You should be able to create a basic update query in design view. In the Update To, you can use NOT YN to return the opposite of YN.

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

hi again dhookom and others - thanks for the advice - the code now works perfectly! I'm hoping I can clarify one point.

I used a subquery in the IN clause like this:

... WHERE tblA.ID_A IN (SELECT tblB_.ID_X FROM tblA LEFT JOIN tblB ...etc

This works great. Here's my question - I tried excluding the 'LEFT JOIN tblB' part from the SQL but instead made a LEFT JOIN relationship in the Relationships window. This doesn't work. I have to include the 'LEFT JOIN tblB...' language in the SQL whether or not the relationship window connects these 2 tables. Just wonderin' why?

Teach314

 

[dhookom]

The relationships might assist in the GUI design view but not so much in a subquery.

Duane
Hook'D on Access
MS Access MVP