Update selected table records

[BrianLe]

I have the table set up like this.

tblA

ID(pk) fld1 fld2 fld3
1 1 1 3
2 1 2
3 1 3
4 2 1 4
5 2 2
6 3 1 2
7 3 2
8 3 3
9 3 4

The fld3 value is always in the record where fld2=1.

With a single query, I would like to complete the fld3 fields, so the table would look like this.

ID(pk) fld1 fld2 fld3
1 1 1 3
2 1 2
3 1 3
4 2 1 4
5 2 2 4
6 3 1 2
7 3 2
8 3 3
9 3 4

I think I need a qryA something like this

SELECT DISTINCTROW tblA.fld1,tblA.fld2,tblA.fld3
FROM tblA
WHERE tblA.fld1=2 (selected from a form)

to return the records for all the fld1,fld2, and fld3 values for a selected fld1 value.

ID(pk) fld1 fld2 fld3
4 2 1 4
5 2 2 4

and then an UPDATE qryB to Update the fld3 values.

Is there there a way I can do both queries together?

Thanks,

Brian

 

[PHV]

UPDATE tblA AS A
SET fld3=(SELECT fld3 FROM yourTable WHERE fld1=A.fld1 AND fld2=1)
WHERE fld1=2 AND fld2<>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

I only have one table "tblA". There is no other table "yourTable" to put into the SELECT.

Thanks,

Brian

 

[PHV]

Sorry for the typo:
UPDATE tblA AS A
SET fld3=(SELECT fld3 FROM tblA WHERE fld1=A.fld1 AND fld2=1)
WHERE fld1=2 AND fld2<>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886