By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

How to update query with data from transposed table?

P: 2
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",product1
FROM plani_po_fil_po_prod;
UNION SELECT filiales, "product2",product2
FROM plani_po_fil_po_prod;

Then CrosstabQuery on it:

TRANSFORM Avg(plani_transpose.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...
Attached Files
File Type: txt sample_data.txt (478 Bytes, 320 views)
Aug 5 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
This is not very elegant, but I believe it will work. Not tested though!
Expand|Select|Wrap|Line Numbers
  1. create tempQry
  2. SELECT ID, Data_2009.filiale, product, plan, fakt, 
  3.        Plani.prod1, Plani.prod2 
  4. FROM   Data_2009 INNER JOIN Plani 
  5. ON     Data_2009.filiale = Plani.filiales
then
Expand|Select|Wrap|Line Numbers
  1. UPDATE tempQry SET plan = [prod1] WHERE product = 'product 1'
and
Expand|Select|Wrap|Line Numbers
  1. UPDATE tempQry SET plan = [prod2] WHERE product = 'product 2'
Aug 5 '09 #2

P: 2
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!!!!!!!!!!!!!!
Aug 5 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.