Update table with different layout

[Deutz]

Thanks in advance. I have two tables, table 1 and table 2 as per below. I want to update table 2 with values from the Cost and Parts fields in Table 1.



My question is, how would I match up the fields so that the data ends up in the right slots?


Table 1:

ForeignKey|Type|Range|Cost|Parts
202|A|1|$10|120 
202|A|2|$50|10 
202|B|1|$100|30 
202|B|2|$5|0

Table 2 prior to data entered from Table 1:

ForeignKey|FieldNm|Range|A|B
202|Cost|1|| 
202|Cost|2|| 
202|Parts|1|| 
202|Parts|2||

Desired result: Table 2 after data entered from Table 1:

ForeignKey|FieldNm|Range|A|B
202|Cost|1|$10|$100 
202|Cost|2|$50|5 
202|Parts|1|120|30 
202|Parts|2|10|0

Rgds
Deutz

[June7]

Using the sample data, can get the result of table 2 with:

SELECT ForeignKey, Type, Range, "Cost" As FieldNm,Cost As Amount FROM Table1
UNION SELECT ForeignKey, Type, Range, "Parts", Parts FROM Table1;

TRANSFORM First([Amount]) AS FirstOfAmount
SELECT [ForeignKey], [FieldNm], [Range]
FROM Query1
GROUP BY [ForeignKey], [Range], [FieldNm]
PIVOT [Type];

There is no wizard or designer for UNION query. Must type in the SQL View window of the query designer.

Since a query can produce the desired output, not really necessary to maintain another table. However, if you must, then use the CROSSTAB query as source for an UPDATE SQL.

[Deutz]

Thanks June7

I'll give it a go and see if I can get it working.