Help with writing a SQL statement

[Razor1]

I have the following SQL statement to load into a table. I want the code to be self contained and not use a Access query. I want to replace the reference to qry_CT_Inv_All_New with the actual SQL statement.

SELECT qry_CT_Inv_All_New.Yr, qry_CT_Inv_All_New.DescriptionShort, qry_CT_Inv_All_New.SalesRepCode, qry_CT_Inv_All_New.CustomerNo, qry_CT_Inv_All_New.Name, qry_CT_Inv_All_New.TerritoryCode, qry_CT_Inv_All_New.[Total Sales], qry_CT_Inv_All_New.[1], qry_CT_Inv_All_New.[2], qry_CT_Inv_All_New.[3], qry_CT_Inv_All_New.[4], qry_CT_Inv_All_New.[5], qry_CT_Inv_All_New.[6], qry_CT_Inv_All_New.[7], qry_CT_Inv_All_New.[8], qry_CT_Inv_All_New.[9], qry_CT_Inv_All_New.[10], qry_CT_Inv_All_New.[11] INTO tmp_Sales1
FROM qry_CT_Inv_All_New;

The qry_CT_Inv_All SQL statement is as follows.
TRANSFORM Sum(tmp_MthInvTotals.SumOfSubTotal) AS SumOfSumOfSubTotal
SELECT tmp_MthInvTotals.Yr, tmp_MthInvTotals.DescriptionShort, tmp_MthInvTotals.SalesRepCode, tmp_MthInvTotals.CustomerNo, tmp_MthInvTotals.Name, tmp_MthInvTotals.TerritoryCode, Sum(tmp_MthInvTotals.SumOfSubTotal) AS [Total Sales]
FROM tmp_MthInvTotals
GROUP BY tmp_MthInvTotals.Yr, tmp_MthInvTotals.DescriptionShort, tmp_MthInvTotals.SalesRepCode, tmp_MthInvTotals.CustomerNo, tmp_MthInvTotals.Name, tmp_MthInvTotals.TerritoryCode
PIVOT tmp_MthInvTotals.Mth;

Any help or suggestions will be greeatly appreciated.





Razor1

 

[JerryKlmns]

Razor1

Your "load into a table" means append the records to the table? Why store computed values in a table? Are you going to delete the records for the current year? If that is so, you could include the year value in a WHERE clause and have the query return the current year automagicaly.

Would you eplain it a bit more?