Remove From My Forums
Access Crosstab query, I need a caluclated field

Question
0

Sign in to vote

Code looks a little like this.

SELECT Category & "_" & IIf([Category]='UW',[SewGroup] & "-" & Left([PSI_ReportTable].[Family],6),[PSI_ReportTable].[Family]) & "_" & [Style] & "_" & [DC] & "_" & 'D-WOS' AS Aggr, PSI_ReportTable.DC, PSI_ReportTable.Category, IIf([Category]='UW',[SewGroup] & "-" & Left([PSI_ReportTable].[Family],6),[PSI_ReportTable].[Family]) AS Family, Style, 'WOS' As Typ,
NZ(WOS00)/1 As Mth_00, NZ(WOS01)/1 As Mth_01, NZ(WOS02)/1 As Mth_02, NZ(WOS03)/1 As Mth_03, NZ(WOS04)/1 As Mth_04, NZ(WOS05)/1 As Mth_05, NZ(WOS06)/1 As Mth_06,
NZ(WOS07)/1 As Mth_07, NZ(WOS08)/1 As Mth_08, NZ(WOS09)/1 As Mth_09, NZ(WOS10)/1 As Mth_10, NZ(WOS11)/1 As Mth_11

FROM PSI_ReportTable) AS PSI_CY
WHERE (((Left([Category],6))<>'z-Disc'))
ORDER BY PSI_CY.Aggr;

Issue is I want to add another field WOSAvg under WOS. It needs to be a calculation.

WOSAvg = Inv-01/Sum(Sales01+Sales02+Sales03Sales04+Sales05+Sales06)/26)

PSI_ReportTable is a crosstab and I don't know how to add this field there. Can I insert it into this query?

Results of query above

Robin

Friday, September 28, 2018 2:53 PM
All replies

Public Function CalculateWOS_Monthly()
Dim DB As Database
Dim ProdPlan As Recordset
Dim StrDC, StrStyle As String
Dim WeeksOfStock, Inv, Fcst, LastWkFcst As Double
Dim CurMth, CurWk, MonthEnd As Date
Dim CurRec, WkCount As Integer
Dim StrSql As String

DoCmd.SetWarnings (False)

DoCmd.RunSQL "INSERT INTO Log_SolveTime (Process, Start, End) SELECT 'WOS Calculation_Monthly', #" & Now() & "#, #1/1/2000#;"

Forms!Startup.Requery
    
Set DB = CurrentDb
Set ProdPlan = DB.OpenRecordset("  SELECT InventoryPlan_Month.DC, DCInfo.ConsolidateWith AS DCSub, DCInfo.Type AS DCType, DCInfo.Status AS DCStatus, InventoryPlan_Month.Style, StyleInfo.SewGroup, StyleInfo.DzsPerPallet, StyleInfo.FinKgPerDz, InventoryPlan_Month.[MO DATE], nz(inventoryplan_Month!InvDzs)+0 AS InvDzs, nz(inventoryplan_month!IntransitDzs)+0 AS IntransitDzs, nz(OpeningInTransit_Monthly!OpeningInTransitDzs)+nz(sewingdcreceipts_month!Dzs)+nz(dcreceipthistory!dzs)+0 AS ProdReceiptDzs, Sum(nz(sewingprodplan_month!SewingProdDzs)+0) AS SewingProdDzs, nz(forecast_monthly!SalesDzs)+0 AS SalesDzs, InventoryPlan_Month.WOS, nz(dctransfers_monthlyreceipts!qty)+nz(dctransfers_monthlyshipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs) AS DCTransfers, nz([Target-Dzs])+0 AS TargetDzs, OpenOrders.EndingDzs AS OpenOrders  " _
                              & "  FROM ((((((((((((InventoryPlan_Month LEFT JOIN SewingDCReceipts_Month ON (InventoryPlan_Month.DC = SewingDCReceipts_Month.ShipToDC) AND (InventoryPlan_Month.Style = SewingDCReceipts_Month.Style) AND (InventoryPlan_Month.[MO DATE] = SewingDCReceipts_Month.DCArrival)) LEFT JOIN SewingProdPlan_Month ON (InventoryPlan_Month.Style = SewingProdPlan_Month.Style) AND (InventoryPlan_Month.DC = SewingProdPlan_Month.ShipToDC) AND (InventoryPlan_Month.[MO DATE] = SewingProdPlan_Month.[MO DATE])) LEFT JOIN Forecast_Monthly ON (InventoryPlan_Month.DC = Forecast_Monthly.DC) AND (InventoryPlan_Month.Style = Forecast_Monthly.Style) AND (InventoryPlan_Month.[MO DATE] = Forecast_Monthly.SalesMonth)) LEFT JOIN OpeningInventory ON (InventoryPlan_Month.DC = OpeningInventory.DC) " _
                              & "  AND (InventoryPlan_Month.Style = OpeningInventory.STYLE)) LEFT JOIN StartDate ON InventoryPlan_Month.[MO DATE] = StartDate.[MO DATE]) LEFT JOIN DCTransfers_MonthlyReceipts ON (InventoryPlan_Month.DC = DCTransfers_MonthlyReceipts.ToDC) AND (InventoryPlan_Month.[MO DATE] = DCTransfers_MonthlyReceipts.MoDate) AND (InventoryPlan_Month.Style = DCTransfers_MonthlyReceipts.Style)) LEFT JOIN DCTransfers_MonthlyShipments ON (InventoryPlan_Month.DC = DCTransfers_MonthlyShipments.FromDC) AND (InventoryPlan_Month.[MO DATE] = DCTransfers_MonthlyShipments.MoDate) AND (InventoryPlan_Month.Style = DCTransfers_MonthlyShipments.Style)) LEFT JOIN StyleInfo ON InventoryPlan_Month.Style = StyleInfo.Style) LEFT JOIN DCInfo ON InventoryPlan_Month.DC = DCInfo.DC) LEFT JOIN DCReceiptHistory ON (InventoryPlan_Month.[MO DATE] = DCReceiptHistory.Month) " _
                              & "  AND (InventoryPlan_Month.Style = DCReceiptHistory.STYLE) AND (InventoryPlan_Month.DC = DCReceiptHistory.DC)) LEFT JOIN OpenOrders ON (InventoryPlan_Month.DC = OpenOrders.DC) AND (InventoryPlan_Month.Style = OpenOrders.Style) AND (InventoryPlan_Month.[MO DATE] = OpenOrders.Month)) LEFT JOIN OpeningInTransit_Monthly ON (InventoryPlan_Month.[MO DATE] = OpeningInTransit_Monthly.MonthDate) AND (InventoryPlan_Month.Style = OpeningInTransit_Monthly.STYLE) AND (InventoryPlan_Month.DC = OpeningInTransit_Monthly.DC)) LEFT JOIN TB_Link_DCTransfersOutToOtherDCs_04 ON (InventoryPlan_Month.DC = TB_Link_DCTransfersOutToOtherDCs_04.DC) AND (InventoryPlan_Month.Style = TB_Link_DCTransfersOutToOtherDCs_04.Style) AND (InventoryPlan_Month.[MO DATE] = TB_Link_DCTransfersOutToOtherDCs_04.MonthDate) GROUP BY InventoryPlan_Month.DC, DCInfo.ConsolidateWith, " _
                              & "  DCInfo.Type, DCInfo.Status, InventoryPlan_Month.Style, StyleInfo.SewGroup, StyleInfo.DzsPerPallet, StyleInfo.FinKgPerDz, InventoryPlan_Month.[MO DATE], nz(inventoryplan_Month!InvDzs)+0, nz(inventoryplan_month!IntransitDzs)+0, nz(OpeningInTransit_Monthly!OpeningInTransitDzs)+nz(sewingdcreceipts_month!Dzs)+nz(dcreceipthistory!dzs)+0, nz(forecast_monthly!SalesDzs)+0, InventoryPlan_Month.WOS, nz(dctransfers_monthlyreceipts!qty)+nz(dctransfers_monthlyshipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs), nz([Target-Dzs])+0, OpenOrders.EndingDzs ORDER BY InventoryPlan_Month.DC, InventoryPlan_Month.Style, InventoryPlan_Month.[MO DATE] ")

CurMth = DLookup("[MO Date]", "CurDateInfo")

ProdPlan.MoveFirst

Do Until ProdPlan.EOF

CurWk = ProdPlan![MO DATE]
StrDC = ProdPlan!DC
StrStyle = ProdPlan!Style
Inv = ProdPlan!InvDzs

WkCount = 0

ProdPlan.MoveNext
If Not ProdPlan.EOF Then
Fcst = ProdPlan!SalesDzs
End If
    Do Until ProdPlan.EOF
    If StrDC <> ProdPlan!DC Or StrStyle <> ProdPlan!Style Or Inv < Fcst Then
    Exit Do
    End If

    ProdPlan.MoveNext
    If Not ProdPlan.EOF Then
    Fcst = Fcst + ProdPlan!SalesDzs
    End If
    WkCount = WkCount + 1
    Loop

If ProdPlan.EOF Then
WeeksOfStock = 99
ElseIf StrDC <> ProdPlan!DC Or StrStyle <> ProdPlan!Style Then
WeeksOfStock = 99
ElseIf Inv > 0 Then
 WeeksOfStock = Round((((WkCount) + (Inv - (Fcst - ProdPlan!SalesDzs)) / Nz((ProdPlan!SalesDzs), 0))) * 4, 2)
Else
WeeksOfStock = 0
End If

ProdPlan.Move (-(WkCount + 1))

'update inventory table here

If CurWk < CurMth Then

 MonthEnd = DLookup("[EndWkDate]", "MonthInfo", "[month] = #" & CurWk & "#")
 StrSql = "UPDATE InventoryPlan SET InventoryPlan.WOS = " & WeeksOfStock & " WHERE (((InventoryPlan.DC)='" & ProdPlan!DC & "') AND ((InventoryPlan.Style)='" & ProdPlan!Style & "') AND ((InventoryPlan.InventoryWeek)= #" & MonthEnd & "#));"
 DoCmd.RunSQL StrSql

End If

ProdPlan.MoveNext

Loop

ProdPlan.Close
DB.Close

Set ProdPlan = Nothing
Set DB = Nothing

DoCmd.RunSQL "UPDATE Log_SolveTime SET Log_SolveTime.End = #" & Now() & "#, Log_SolveTime.Time = DateDiff('n',[Log_SolveTime].[Start], Now()) WHERE Log_SolveTime.Process LIKE 'WOS Calculation_Monthly*' AND Log_SolveTime.End = #1/1/2000#;"
Forms!Startup.Requery

'Wait (1)

DoCmd.SetWarnings (True)

End Function

Public Function CalculateWOS_Weekly()

CalculateWOS_Monthly

Dim DB As Database
Dim ProdPlan As Recordset
Dim StrDC, StrStyle As String
Dim WeeksOfStock, Inv, Fcst, LastWkFcst As Double
Dim CurWk, MonthEnd, YearWkEnd As Date
Dim CurRec, WkCount As Integer
Dim StrSql As String

DoCmd.SetWarnings (False)

DoCmd.RunSQL "INSERT INTO Log_SolveTime (Process, Start, End) SELECT 'WOS Calculation_Weekly', #" & Now() & "#, #1/1/2000#;"
Forms!Startup.Requery

'Wait (1)
  
'=====================================================================================================================================================
 'Append temporary Fcst and WOC
  TempFcstAppend
'=====================================================================================================================================================
    
Set DB = CurrentDb
 
 'DoCmd.RunSQL (" SELECT InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek, nz(inventoryplan!invdzs)+0 AS InvDzs, nz(InventoryPlan!intransitdzs)+0 AS IntransitDzs, nz(sewingdcreceipts!Dzs)+nz(dcreceipthistory!dzs)+nz(fps_receiptplan!qty)+0 AS ProdReceiptDzs, nz([SewingProdPlan_ByDC (without Region)]!SumOFsewingproddzs)+0 AS SewingProdDzs, nz(Forecast_Weekly!salesdzs)+1 AS SalesDzs, nz(dctransfers_receipts!qty)-nz(DCTransfers_Shipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs) AS DCTransfers, InventoryPlan.WOS, InventoryPlan.[Target-Weeks] INTO PSI_Tab  " _
              & " FROM ((((((((InventoryPlan LEFT JOIN SewingDCReceipts ON (InventoryPlan.Style = SewingDCReceipts.Style) AND (InventoryPlan.InventoryWeek = SewingDCReceipts.DCArrival) AND (InventoryPlan.DC = SewingDCReceipts.ShipToDC)) LEFT JOIN Forecast_Weekly ON (InventoryPlan.DC = Forecast_Weekly.DC) AND (InventoryPlan.Style = Forecast_Weekly.Style) AND (InventoryPlan.InventoryWeek = Forecast_Weekly.[WK DATE])) LEFT JOIN StartDate ON InventoryPlan.InventoryWeek = StartDate.StartDate) LEFT JOIN DCTransfers_Shipments ON (InventoryPlan.DC = DCTransfers_Shipments.FromDC) AND  " _
              & " (InventoryPlan.Style = DCTransfers_Shipments.FromStyle) AND (InventoryPlan.InventoryWeek = DCTransfers_Shipments.ShipDate)) LEFT JOIN DCTransfers_Receipts ON (InventoryPlan.DC = DCTransfers_Receipts.ToDC) AND (InventoryPlan.Style = DCTransfers_Receipts.Style) AND (InventoryPlan.InventoryWeek = DCTransfers_Receipts.RcptDate)) LEFT JOIN DCReceiptHistory ON (InventoryPlan.DC = DCReceiptHistory.DC) AND (InventoryPlan.Style = DCReceiptHistory.STYLE) AND (InventoryPlan.InventoryWeek = DCReceiptHistory.DCReceiptWk)) LEFT JOIN FPS_ReceiptPlan ON (InventoryPlan.DC = FPS_ReceiptPlan.ToDC)  " _
              & " AND (InventoryPlan.Style = FPS_ReceiptPlan.Style) AND (InventoryPlan.InventoryWeek = FPS_ReceiptPlan.ReceiptDate)) LEFT JOIN [SewingProdPlan_ByDC (without Region)] ON (InventoryPlan.Style = [SewingProdPlan_ByDC (without Region)].Style) AND (InventoryPlan.DC = [SewingProdPlan_ByDC (without Region)].ShipToDC) AND (InventoryPlan.InventoryWeek = [SewingProdPlan_ByDC (without Region)].SewingWeek)) LEFT JOIN TB_Link_DCTransfersOutToOtherDCs_04 ON (InventoryPlan.Style = TB_Link_DCTransfersOutToOtherDCs_04.Style) AND (InventoryPlan.InventoryWeek = TB_Link_DCTransfersOutToOtherDCs_04.ReceiptWkDate)  " _
              & " AND (InventoryPlan.DC = TB_Link_DCTransfersOutToOtherDCs_04.DC) GROUP BY InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek, nz(inventoryplan!invdzs)+0, nz(InventoryPlan!intransitdzs)+0, nz(sewingdcreceipts!Dzs)+nz(dcreceipthistory!dzs)+nz(fps_receiptplan!qty)+0, nz([SewingProdPlan_ByDC (without Region)]!SumOFsewingproddzs)+0, nz(Forecast_Weekly!salesdzs)+1, nz(dctransfers_receipts!qty)-nz(DCTransfers_Shipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs), InventoryPlan.WOS, InventoryPlan.[Target-Weeks]HAVING (((InventoryPlan.InventoryWeek)>=DLookUp('[mondaydate]','MondayDate'))) ORDER BY InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek ")

'Set ProdPlan = DB.OpenRecordset("PSI_Tab")


Set ProdPlan = DB.OpenRecordset(" SELECT InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek, nz(inventoryplan!invdzs)+0 AS InvDzs, nz(InventoryPlan!intransitdzs)+0 AS IntransitDzs, nz(sewingdcreceipts!Dzs)+nz(dcreceipthistory!dzs)+nz(fps_receiptplan!qty)+0 AS ProdReceiptDzs, nz([SewingProdPlan_ByDC (without Region)]!SumOFsewingproddzs)+0 AS SewingProdDzs, nz(Forecast_Weekly!salesdzs)+1 AS SalesDzs, nz(dctransfers_receipts!qty)-nz(DCTransfers_Shipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs) AS DCTransfers, InventoryPlan.WOS, InventoryPlan.[Target-Weeks] " _
                              & " FROM ((((((((InventoryPlan LEFT JOIN SewingDCReceipts ON (InventoryPlan.DC = SewingDCReceipts.ShipToDC) AND (InventoryPlan.InventoryWeek = SewingDCReceipts.DCArrival) AND (InventoryPlan.Style = SewingDCReceipts.Style)) LEFT JOIN Forecast_Weekly ON (InventoryPlan.InventoryWeek = Forecast_Weekly.[WK DATE]) AND (InventoryPlan.Style = Forecast_Weekly.Style) AND (InventoryPlan.DC = Forecast_Weekly.DC)) LEFT JOIN StartDate ON InventoryPlan.InventoryWeek = StartDate.StartDate) LEFT JOIN DCTransfers_Shipments ON (InventoryPlan.InventoryWeek = DCTransfers_Shipments.ShipDate) " _
                              & " AND (InventoryPlan.Style = DCTransfers_Shipments.FromStyle) AND (InventoryPlan.DC = DCTransfers_Shipments.FromDC)) LEFT JOIN DCTransfers_Receipts ON (InventoryPlan.InventoryWeek = DCTransfers_Receipts.RcptDate) AND (InventoryPlan.Style = DCTransfers_Receipts.Style) AND (InventoryPlan.DC = DCTransfers_Receipts.ToDC)) LEFT JOIN DCReceiptHistory ON (InventoryPlan.InventoryWeek = DCReceiptHistory.DCReceiptWk) AND (InventoryPlan.Style = DCReceiptHistory.STYLE) AND (InventoryPlan.DC = DCReceiptHistory.DC)) LEFT JOIN FPS_ReceiptPlan ON (InventoryPlan.InventoryWeek = FPS_ReceiptPlan.ReceiptDate) " _
                              & " AND (InventoryPlan.Style = FPS_ReceiptPlan.Style) AND (InventoryPlan.DC = FPS_ReceiptPlan.ToDC)) LEFT JOIN [SewingProdPlan_ByDC (without Region)] ON (InventoryPlan.InventoryWeek = [SewingProdPlan_ByDC (without Region)].SewingWeek) AND (InventoryPlan.DC = [SewingProdPlan_ByDC (without Region)].ShipToDC) AND (InventoryPlan.Style = [SewingProdPlan_ByDC (without Region)].Style)) LEFT JOIN TB_Link_DCTransfersOutToOtherDCs_04 ON (InventoryPlan.DC = TB_Link_DCTransfersOutToOtherDCs_04.DC) AND (InventoryPlan.InventoryWeek = TB_Link_DCTransfersOutToOtherDCs_04.ReceiptWkDate) AND (InventoryPlan.Style = TB_Link_DCTransfersOutToOtherDCs_04.Style) " _
                              & " GROUP BY InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek, nz(inventoryplan!invdzs)+0, nz(InventoryPlan!intransitdzs)+0, nz(sewingdcreceipts!Dzs)+nz(dcreceipthistory!dzs)+nz(fps_receiptplan!qty)+0, nz([SewingProdPlan_ByDC (without Region)]!SumOFsewingproddzs)+0, nz(Forecast_Weekly!salesdzs)+1, nz(dctransfers_receipts!qty)-nz(DCTransfers_Shipments!qty)+nz(TB_Link_DCTransfersOutToOtherDCs_04.Dzs), InventoryPlan.WOS, InventoryPlan.[Target-Weeks] HAVING (((InventoryPlan.InventoryWeek)>=DLookUp('[mondaydate]','MondayDate'))) ORDER BY InventoryPlan.DC, InventoryPlan.Style, InventoryPlan.InventoryWeek ")

YearWkEnd = DateAdd("Ww", 56, DLookup("[YearEndWkDate]", "CurDateInfo"))

ProdPlan.MoveFirst

Do Until ProdPlan.EOF
CurWk = ProdPlan![Inventoryweek]

StrDC = ProdPlan!DC
StrStyle = ProdPlan!Style
WkCount = 0
Inv = ProdPlan!InvDzs
'CurRec = ProdPlan.AbsolutePosition

ProdPlan.MoveNext

If Not ProdPlan.EOF Then
Fcst = ProdPlan!SalesDzs
End If
    
    Do Until ProdPlan.EOF
    If StrDC <> ProdPlan!DC Or StrStyle <> ProdPlan!Style Or Inv < Fcst Then
    Exit Do
    End If

    ProdPlan.MoveNext
    If Not ProdPlan.EOF Then
    Fcst = Fcst + ProdPlan!SalesDzs
    End If
    WkCount = WkCount + 1
    
    Loop

If ProdPlan.EOF Then
WeeksOfStock = 99
ElseIf StrDC <> ProdPlan!DC Or StrStyle <> ProdPlan!Style Then
WeeksOfStock = 99
ElseIf Inv > 0 Then
WeeksOfStock = Round(((WkCount) + (Inv - (Fcst - ProdPlan!SalesDzs)) / Nz((ProdPlan!SalesDzs), 0) * 1), 2)
Else
WeeksOfStock = 0
End If

ProdPlan.Move (-(WkCount + 1))

'update inventory table here

If CurWk <= YearWkEnd Then
     StrSql = "UPDATE InventoryPlan SET InventoryPlan.WOS = " & WeeksOfStock & " WHERE (((InventoryPlan.DC)='" & ProdPlan!DC & "') AND ((InventoryPlan.Style)='" & ProdPlan!Style & "') AND ((InventoryPlan.InventoryWeek)= #" & ProdPlan![Inventoryweek] & "#));"
DoCmd.RunSQL StrSql
End If

ProdPlan.MoveNext
Loop

DoCmd.RunSQL "UPDATE InventoryPlan SET InventoryPlan.WOS = 99 WHERE (((InventoryPlan.WOS)>99))"
DoCmd.RunSQL "UPDATE InventoryPlan SET InventoryPlan.WOS = 0 WHERE (((InventoryPlan.InvDzs)<=0) AND ((InventoryPlan.InventoryWeek)<DLookUp('[MondayDate]','MondayDate')))"

'=====================================================================================================================================================
 'Delete temporary Fcst and WOC
  TempFcstDelete
'=====================================================================================================================================================

ProdPlan.Close
DB.Close

Set ProdPlan = Nothing
Set DB = Nothing

  'DoCmd.DeleteObject acTable, "PSI_Tab"
  
DoCmd.RunSQL "UPDATE Log_SolveTime SET Log_SolveTime.End = #" & Now() & "#, Log_SolveTime.Time = DateDiff('n',[Log_SolveTime].[Start], Now()) WHERE Log_SolveTime.Process LIKE 'WOS Calculation_Weekly*' AND Log_SolveTime.End = #1/1/2000#;"
Forms!Startup.Requery
'Wait (1)

DoCmd.SetWarnings (True)

End Function
Attached is the actual Code for the WOS calculation. Not sure if that helps you understand what Im doing.
Robin
Friday, September 28, 2018 4:32 PM
0

Sign in to vote

I didn't build this. I am not good with crosstab queries.
Robin

Friday, September 28, 2018 4:47 PM
0

Sign in to vote

New field calculation

Robin

Friday, September 28, 2018 6:07 PM
Asked by:

Access Crosstab query, I need a caluclated field

Question

All replies
