Update table from union query using temp table headache!!

[millrat]

Hi all,

Can anyone please help here? I need to transpose data as below in tbl1 and update to tbl2 as below.

tbl1:

pkID1 f1 f2 f3 f4
1 A 1 2 2
2 B 1 2 1

This Union query does the trick.

SELECT pkID, f2 AS A FROM tbl1
UNION ALL SELECT pkID, f3 AS A FROM tbl1
UNION ALL SELECT pkID, f4 AS A FROM tbl1
ORDER BY pkID;

This is the result

qry1:

pkID1 A
1 1
1 2
1 2
2 1
2 2
2 1

My problem is I can't figure out how to update qry1 to tbl2 as below.

tbl2:

pkID2 pkID1 fA f5 f6
1 1 2 null null
2 1 2 null null

PS The null fields are to be filled in after the table is updated.

I've got a make table query, using

SELECT qry1.pkID1, qry1.A INTO tblTemp
FROM qry1 LEFT JOIN tbl2 ON qry1.pkID1 = tbl2.pkID1
WHERE (((qry1.A)=2));

This works and gives

tblTemp:

pkID1 A
1 2
1 2

This is where I'm stuck!!!

The update query below when run gives a dialog box "You are updating 2 records"


UPDATE tblTemp LEFT JOIN tbl2 ON tblTemp.pkID1 = tbl2.pkID1 SET tblTemp.pkID1 = tbl2.pkID1, tblTemp.A = tbl2.fA;

But when I check tbl2, there's no records and the tblTemp is empty too!

Anyone know what's going on????
My brain hurts!

Thanks,

millrat