How to write a query that changes the layout of an existing table

[nagiese]

I currently have a table that I need to rewrite so it is in a different format. Currently the table would be considered a flat file, but I need to normalize it. Right now the fields are as follows:
Year, Period, Location, Op_Alpha, Loc_Name, Plant, $EXT_Cost, $CURR_PD_IN_TRNS,$PR_PD_IN_TRNS, $CULLS, $TFER_AMT, $MARK_UP, $SALES, $SALES&TFERS. I need to rewrite the table so it has the following Fields:
Year, Period, Location, Op_Alpha, Loc_Name, Plant, Transaction type, $, CostCenter, ProfitCenter, Company.

Where the Transaction type would list the CURR_PD_IN_TRNS,$PR_PD_IN_TRNS, $CULLS, $TFER_AMT, $MARK_UP, $SALES, $SALES&TFERS as these are all transaction and the $ filed would list the associated $ for that transaction type.

Would anyone be able to tell me how I can write a query or SQL that would fix this problem?

I've attached samples of the tables.

This table is an apend table below is the SQL that appends that data to the table.
INSERT INTO [tbl-STAT34Detail_Tranfers&Sales] ( [Year], Period, LOCATION_NBR, OP_AlPHA, LOC_NAME, PLANT, SumOfEXTN_COST, SumOf6, SumOf5, [SumOfSumOfExt Cost], [Tranferred Amt], MARK_UP, SALES, [SALES & TFERS] )
SELECT [slq-STAT34Detail].Year, [slq-STAT34Detail].Period, [slq-STAT34Detail].LOCATION_NBR, [slq-STAT34Detail].OP_AlPHA, [slq-STAT34Detail].LOC_NAME, [slq-STAT34Detail].PLANT, Sum([slq-STAT34Detail].EXTN_COST) AS SumOfEXTN_COST, Sum([ctq-In_Transit_by_Period].[6]) AS CURR_PD_IN_TRNS, Sum([ctq-In_Transit_by_Period].[5]) AS PR_PD_IN_TRNS, Sum([mtb-Culls_In_Scope].[SumOfExt Cost]) AS CULLS, Sum([slq-STAT34Detail]!EXTN_COST-IIf([ctq-In_Transit_by_Period]![6] Is Null,0,[ctq-In_Transit_by_Period]![6])+IIf([ctq-In_Transit_by_Period]![5] Is Null,0,[ctq-In_Transit_by_Period]![5])-IIf([mtb-Culls_In_Scope]![SumOfExt Cost] Is Null,0,[mtb-Culls_In_Scope]![SumOfExt Cost])) AS TFER_AMT, [TFER_AMT]*0.0504 AS MARK_UP, [TFER_AMT]+[MARK_UP] AS SALES, [TFER_AMT]+[SALES] AS [SALES&TFERS]
FROM ([slq-STAT34Detail] LEFT JOIN [mtb-Culls_In_Scope] ON ([slq-STAT34Detail].Period = [mtb-Culls_In_Scope].Period) AND ([slq-STAT34Detail].Year = [mtb-Culls_In_Scope].Year) AND ([slq-STAT34Detail].LOCATION_NBR = [mtb-Culls_In_Scope].Location)) LEFT JOIN [ctq-In_Transit_by_Period] ON ([slq-STAT34Detail].Year = [ctq-In_Transit_by_Period].Year) AND ([slq-STAT34Detail].LOCATION_NBR = [ctq-In_Transit_by_Period].LOCATION_NBR)
GROUP BY [slq-STAT34Detail].Year, [slq-STAT34Detail].Period, [slq-STAT34Detail].LOCATION_NBR, [slq-STAT34Detail].OP_AlPHA, [slq-STAT34Detail].LOC_NAME, [slq-STAT34Detail].PLANT;

[June7]

What you want is to normalize the data structure. Use a UNION query. There is no wizard or designer, must type in the SQL View window.

SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "EXTN_COST" As Trans_Type, EXTN_COST As TransAmt FROM tablename
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "CURR_PD_IN_TRNS", CURR_PD_IN_TRNS FROM tablename
...repeat the UNION for each field
...;

[nagiese]

Thank you so much that worked great. I have one more question, how do I add formatting so the number is standard format with two decimals? My union query is below.

SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "EXTN_COST" As Trans_Type, EXTN_COST As TransAmt FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "CURR_PD_IN_TRNS", CURR_PD_IN_TRNS FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "PR_PD_IN_TRNS", PR_PD_IN_TRNS FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "CULLS", CULLS FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "TFER_AMT", TFER_AMT FROM[tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "MARK_UP", MARK_UP FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "SALES", SALES FROM [tbl-STAT34Detail_Tranfers&Sales]
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "SALES&TFERS", [tbl-STAT34Detail_Tranfers&Sales]![SALES&TFERS] FROM [tbl-STAT34Detail_Tranfers&Sales];

What you want is to normalize the data structure. Use a UNION query. There is no wizard or designer, must type in the SQL View window.

SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "EXTN_COST" As Trans_Type, EXTN_COST As TransAmt FROM tablename
UNION SELECT [Year], Period, Location_NBR, OP_ALPHA, Loc_Name, Plant, "CURR_PD_IN_TRNS", CURR_PD_IN_TRNS FROM tablename
...repeat the UNION for each field
...;

[June7]

I prefer not to apply formatting in tables and queries unless absolutely required to be rounded for subsequent calculations. I do formatting in controls on forms and reports.

Could use Format or FormatNumber function. CAUTION: the result of both functions is a text string, not a number, even if the value is composed of only digits. Round function will not show zeros in decimal part if the result has only zeros in decimal.

I use Format instead of Round because Round function has even/odd rule. Review https://www.accessforums.net/program...ion-26842.html