Hi All,
Not sure if I'm posting this in the right area...
I've created a crosstab query using ODBC. What I've notice is the dates on the Column is not in seqeuntial order. Is there anything I can do to dispay the dates in order.
Please see the SQL data I copied below. Appreciate anyones assistance.
TRANSFORM Sum([COIL QUERY].QTY) AS SumOfQTY
SELECT PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy") AS [Current Wk], Format(#6/30/2014#,"ww\,mmm\,yy") AS [Future Date], [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID AS [RA Coverage], [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID AS [ZR Coverage], [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR, Sum([COIL QUERY].QTY) AS [Total Demand], Avg([COIL QUERY].QTY) AS AvgOfQTY, DateDiff("ww",[Current Wk],[Future Date]) AS [WK END], ([Total Demand]/[WK END])*-1 AS [Avg WK], Sum(Year([COIL QUERY].[REQDATE])*12+DatePart("m",[COIL QUERY].[REQDATE])-1) AS Expr1
FROM (((PKV LEFT JOIN (([COIL QUERY] LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ex FA] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ex FA].ITEMID) LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ZR] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ZR].ITEMID) ON PKV.[Item number] = [COIL QUERY].ITEMID) LEFT JOIN [ITEM MASTER STD COST QUERY] ON PKV.[Item number] = [ITEM MASTER STD COST QUERY].ITEMID) LEFT JOIN [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab] ON PKV.[Item number] = [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ITEMID) LEFT JOIN [COIL ACTUAL DEMAND QUERY] ON PKV.[Item number] = [COIL ACTUAL DEMAND QUERY].ITEMID
GROUP BY PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy"), Format(#6/30/2014#,"ww\,mmm\,yy"), [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID, [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID, [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR
PIVOT Format([REQDATE]," ww"", ""yyyy");
Not sure if I'm posting this in the right area...
I've created a crosstab query using ODBC. What I've notice is the dates on the Column is not in seqeuntial order. Is there anything I can do to dispay the dates in order.
Please see the SQL data I copied below. Appreciate anyones assistance.
TRANSFORM Sum([COIL QUERY].QTY) AS SumOfQTY
SELECT PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy") AS [Current Wk], Format(#6/30/2014#,"ww\,mmm\,yy") AS [Future Date], [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID AS [RA Coverage], [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID AS [ZR Coverage], [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR, Sum([COIL QUERY].QTY) AS [Total Demand], Avg([COIL QUERY].QTY) AS AvgOfQTY, DateDiff("ww",[Current Wk],[Future Date]) AS [WK END], ([Total Demand]/[WK END])*-1 AS [Avg WK], Sum(Year([COIL QUERY].[REQDATE])*12+DatePart("m",[COIL QUERY].[REQDATE])-1) AS Expr1
FROM (((PKV LEFT JOIN (([COIL QUERY] LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ex FA] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ex FA].ITEMID) LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ZR] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ZR].ITEMID) ON PKV.[Item number] = [COIL QUERY].ITEMID) LEFT JOIN [ITEM MASTER STD COST QUERY] ON PKV.[Item number] = [ITEM MASTER STD COST QUERY].ITEMID) LEFT JOIN [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab] ON PKV.[Item number] = [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ITEMID) LEFT JOIN [COIL ACTUAL DEMAND QUERY] ON PKV.[Item number] = [COIL ACTUAL DEMAND QUERY].ITEMID
GROUP BY PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy"), Format(#6/30/2014#,"ww\,mmm\,yy"), [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID, [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID, [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR
PIVOT Format([REQDATE]," ww"", ""yyyy");
