change SQL query to "make table" query

[mmartin999]

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

 

[giacomo]

can you post the sql?

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

hth,
Giacomo

 

[mmartin999]

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]

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]

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]

Why not just create another query based on the original one?

SELECT Query1.* INTO NewTable
FROM Query1;

 

[giacomo]

remove the semicolon right before the closing parenthesis

 

[mmartin999]

thanks everyone!

I'm new to Access and appreciate the helpful tips.