change SQL query to "make table" query

mmartin999

New Member
Joined
Oct 4, 2006
Messages
33
I am modifying a query that was written in SQL (I did not create it) and want to change it to a "make table" query. When the data is compiled I don't want to have to export it out to excel then bring back in to Access. I know how to change a regular query to make table, but not SQL.

thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
can you post the sql?

also, as long as nobody replies to your other posts you can delete them.

hth,
Giacomo
 

mmartin999

New Member
Joined
Oct 4, 2006
Messages
33
here is the sql, as requested.

SELECT Planning_distribution_volumes_extract.SID, EDS_ITEM_MASTER.WGT_PER_UOM, EDS_ITEM_MASTER.ROLLUP_LEVEL_5_CD AS SBU, EDS_ITEM_MASTER.RAW_PROD_VARIETY_CD AS RPV, Planning_distribution_volumes_extract.FROM, Planning_distribution_volumes_extract.TO, Planning_distribution_volumes_extract.MODE, Planning_distribution_volumes_extract.BUC_ST_DTTM, Planning_distribution_volumes_extract.BUC_END_DTTM, Planning_distribution_volumes_extract.QTY, FISCAL_CAL.Fiscal_month, (EDS_ITEM_MASTER.WGT_PER_UOM*Planning_distribution_volumes_extract.QTY/100) AS CWT, "FROM "+Planning_distribution_volumes_extract.FROM+" TO "+Planning_distribution_volumes_extract.TO AS [KEY], EDS_ITEM_MASTER.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, EDS_ITEM_MASTER.STAT_VALUE
FROM (EDS_ITEM_MASTER RIGHT JOIN Planning_distribution_volumes_extract ON EDS_ITEM_MASTER.ITEM_NUM = Planning_distribution_volumes_extract.SID) INNER JOIN FISCAL_CAL ON Planning_distribution_volumes_extract.BUC_ST_DTTM = FISCAL_CAL.Fiscal_month_start
WHERE (((FISCAL_CAL.Fiscal_year) Like "F07"))
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm1, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm1 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NOT NULL
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm2, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm2 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NULL;
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
try this:

select * into [some_table_name] from (
your sql goes here
)

another option would be to to turn your first SELECT into a make table and then break each UNION into an append query.

hth,
Giacomo
 

mmartin999

New Member
Joined
Oct 4, 2006
Messages
33

ADVERTISEMENT

syntax error from suggestion. any ideas?

Thanks for the suggestion. I tried the 1st and got a syntax error message. Can you see what I did wrong? I only modified the 1st SELECT. Maybe I should modify all?

SELECT * INTO [PROJECTED_DEPLOYMENT_TEST] FROM ( Planning_distribution_volumes_extract.SID, EDS_ITEM_MASTER.WGT_PER_UOM, EDS_ITEM_MASTER.ROLLUP_LEVEL_5_CD AS SBU, EDS_ITEM_MASTER.RAW_PROD_VARIETY_CD AS RPV, Planning_distribution_volumes_extract.FROM, Planning_distribution_volumes_extract.TO, Planning_distribution_volumes_extract.MODE, Planning_distribution_volumes_extract.BUC_ST_DTTM, Planning_distribution_volumes_extract.BUC_END_DTTM, Planning_distribution_volumes_extract.QTY, FISCAL_CAL.Fiscal_month, (EDS_ITEM_MASTER.WGT_PER_UOM*Planning_distribution_volumes_extract.QTY/100) AS CWT, "FROM "+Planning_distribution_volumes_extract.FROM+" TO "+Planning_distribution_volumes_extract.TO AS [KEY], EDS_ITEM_MASTER.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, EDS_ITEM_MASTER.STAT_VALUE
FROM (EDS_ITEM_MASTER RIGHT JOIN Planning_distribution_volumes_extract ON EDS_ITEM_MASTER.ITEM_NUM = Planning_distribution_volumes_extract.SID) INNER JOIN FISCAL_CAL ON Planning_distribution_volumes_extract.BUC_ST_DTTM = FISCAL_CAL.Fiscal_month_start
WHERE (((FISCAL_CAL.Fiscal_year) Like "F07"))
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm1, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm1 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NOT NULL
UNION ALL SELECT Purchase_Volumes.SID, HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM, HNAD_EDS_PROD1_ITEM_HIER_V.ROLLUP_LEVEL_5_CD AS SBU, HNAD_EDS_PROD1_ITEM_HIER_V.RAW_PROD_VARIETY_CD AS RPV, Purchase_Volumes.Vendor AS [FROM], Purchase_Volumes.TO, Purchase_Volumes.mode, Purchase_Volumes.dttm2, Purchase_Volumes.dttm2, Purchase_Volumes.qty, FISCAL_CAL.Fiscal_month,(HNAD_EDS_PROD1_ITEM_HIER_V.WGT_PER_UOM*Purchase_Volumes.QTY/100) AS CWT, "FROM "+Purchase_Volumes.vendor+" TO "+Purchase_Volumes.TO AS [KEY], HNAD_EDS_PROD1_ITEM_HIER_V.PROD_GRP_CD, FISCAL_CAL.Fiscal_year, HNAD_EDS_PROD1_ITEM_HIER_V.STAT_VALUE
FROM (Purchase_Volumes INNER JOIN FISCAL_CAL ON Purchase_Volumes.dttm2 = FISCAL_CAL.Fiscal_month_start) INNER JOIN HNAD_EDS_PROD1_ITEM_HIER_V ON Purchase_Volumes.SID = HNAD_EDS_PROD1_ITEM_HIER_V.ITEM_NUM
WHERE ( FISCAL_CAL.Fiscal_YEAR LIKE "F07") AND Purchase_Volumes.dttm1 IS NULL;)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just create another query based on the original one?

SELECT Query1.* INTO NewTable
FROM Query1;
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
remove the semicolon right before the closing parenthesis
 

Forum statistics

Threads
1,136,260
Messages
5,674,700
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top