Append SQL Statement expression

[Jgk]

INSERT INTO [WeeklySalesTrending-CW] ( Location, Store, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 )
SELECT [qryWeekOverWeekSales-CurrentYear].Location, Left([location],Len([location])-9) AS Store, [qryWeekOverWeekSales-CurrentYear].[1] AS Expr1, [qryWeekOverWeekSales-CurrentYear].[2] AS Expr2, [qryWeekOverWeekSales-CurrentYear].[3] AS Expr3, [qryWeekOverWeekSales-CurrentYear].[4] AS Expr4, [qryWeekOverWeekSales-CurrentYear].[5] AS Expr5, [qryWeekOverWeekSales-CurrentYear].[6] AS Expr6, [qryWeekOverWeekSales-CurrentYear].[7] AS Expr7, [qryWeekOverWeekSales-CurrentYear].[8] AS Expr8, [qryWeekOverWeekSales-CurrentYear].[9] AS Expr9, [qryWeekOverWeekSales-CurrentYear].[10] AS Expr10, [qryWeekOverWeekSales-CurrentYear].[11] AS Expr11, [qryWeekOverWeekSales-CurrentYear].[12] AS Expr12, [qryWeekOverWeekSales-CurrentYear].[13] AS Expr13, [qryWeekOverWeekSales-CurrentYear].[14] AS Expr14, [qryWeekOverWeekSales-CurrentYear].[15] AS Expr15, [qryWeekOverWeekSales-CurrentYear].[16] AS Expr16, [qryWeekOverWeekSales-CurrentYear].[17] AS Expr17

FROM [qryWeekOverWeekSales-CurrentYear];


This is my sql statement that appends a table of which i have field names in table WeeklySalesTending-CW, location,Store, fields for 53 weeks



My question is how can I have sql to append to 53 weeks and maybe a null statement if only at week 20. Right now I have to edit the shaded sections and add a week each week, and add expr week because of my query( qryWeekOverWeeK-CurrentYear) sql listed below

SQL for this query:

TRANSFORM Sum(CCur([Net_Sales])) AS Sales
SELECT [Store]+"-"+"Cur Year" AS Location
FROM (qryWeeklySales LEFT JOIN tblStore ON qryWeeklySales.Store_Id = tblStore.StoreId) LEFT JOIN tblStoreReference ON tblStore.StoreRef = tblStoreReference.Ref_No
WHERE (((qryWeeklySales.year)=(Year(Date()))) AND ((tblStoreReference.Status) Is Null Or (tblStoreReference.Status)<>"CLD"))
GROUP BY [Store]+"-"+"Cur Year"
ORDER BY [Store]+"-"+"Cur Year", qryWeeklySales.Week_Number DESC
PIVOT qryWeeklySales.Week_Number;

It works now but I have to add red part in sql four times for four different queries I designed like above.

thanks

[June7]

You have fields named 1, 2, 3, 4, ... 53?
That is not a normalized data structure. Why are you saving non-normalized data?

Note that the & character is the preferred concatenation operator.

[ranman256]

Databases like to store data top-down, not left-to-right.

It would be:
[STORE#],[WEEK], [SALES AMT].

[Jgk]

Importing data from micros pos to access and creating excel report comparing weeks, I have 4 tables with weeks 1-53, location, and store to save thru append queries like above

[June7]

Same question. Why save non-normalized data? Just save raw data then manipulate with queries for desired output.

Forcing the CROSSTAB to produce fields for the future weeks where there is no data would be tricky at best, certainly not easy.

Also, I would think only 1 table is necessary with another field for the store ID.

[Jgk]

I save data to table raw data n manipulate with 4 queries saving to four tables