How to update query with data from transposed table?

[jelena1290]

Hi, I desperately need help here....

I have 2 tables to start with:

Table1: Data_2009

ID filiale product plan fakt
10 filiale 4 product 1
3 filiale 3 product 1
2 filiale 2 product 1
1 filiale 1 product 1
11 filiale 4 product 2 66 777
6 filiale 3 product 2
5 filiale 2 product 2
4 filiale 1 product 2
12 filiale 4 product 3
9 filiale 3 product 3 22 421
8 filiale 2 product 3 124 124
7 filiale 1 product 3 421 14

Table2: Plani

filiales prod1 prod2
filiale 1 4535 3423
filiale 2 3223 23424
filiale 3 2324 234324
filiale 4 324324 234234

I need to plug in numbers per filiales per products from Table2 into Table1, so blanks in column 'plan' in table A will be filled in.

What I've tried so far:

Union query on Table2:

SELECT filiales as ColHead, "product1" AS RowHead, product1 AS TheVal
FROM plani_po_fil_po _prod
UNION SELECT filiales, "product1",prod uct1
FROM plani_po_fil_po _prod;
UNION SELECT filiales, "product2",prod uct2
FROM plani_po_fil_po _prod;

Then CrosstabQuery on it:

TRANSFORM Avg(plani_trans pose.TheVal) AS AvgOfTheVal
SELECT plani_transpose .RowHead
FROM plani_transpose
GROUP BY plani_transpose .RowHead
PIVOT plani_transpose .ColHead;

Then SelectQuery on the Crosstab (which probabaly wasn't necessary):

SELECT plani_transpose .ColHead AS Filiales, plani_transpose .RowHead AS Produkti, plani_transpose .TheVal AS Plans
FROM plani_transpose ;

This way I get both tables look the same way, but when I try to create Update Query, nothing works...Which would be the right way to go??

Please help!!! file with sample data is attached...

[ChipR]

This is not very elegant, but I believe it will work. Not tested though!

create tempQry
SELECT ID, Data_2009.filiale, product, plan, fakt, 
       Plani.prod1, Plani.prod2 
FROM   Data_2009 INNER JOIN Plani 
ON     Data_2009.filiale = Plani.filiales

then

UPDATE tempQry SET plan = [prod1] WHERE product = 'product 1'

and

UPDATE tempQry SET plan = [prod2] WHERE product = 'product 2'

[jelena1290]

Niccccce!!!!!

Works just fine, and I suppose elegance is not my goal here ;))

The only addition I made was that I added one extra condition so that the records that are already in Table A in column 'plan' don't get overridden by values from Table B...

Thank you so so much for your help!!!!!!!!!!! !!!