Working on a scheduling tool that utilizes reports pulled from a SAP report server. The report contains production demand by week forward looking only so if I run the report today I lose the previous weeks information. As I plan on using this tool to store historic information, i would like to only update the information for the weeks after current week. The list contains about 500 different skus and contains weeknums from current week to week 51 of 2019 as well as past dues.
I am looking for something like an update query where it updates the Sku if(or(WkNum > datepart("ww",Date())+1, WkNum = "PD") Update Quantity else skip.
Code:
UPDATE tbLiveSupplyReport SET tbLiveSupplyReport.Quantity = [tblDemandTest]![Quantity]WHERE (((tbLiveSupplyReport.WkNum)="PD") AND ((tbLiveSupplyReport.Material)=[tblDemandTest]![Material]) AND ((tbLiveSupplyReport.Week)=[tblDemandTest]![Week])) OR (((tbLiveSupplyReport.WkNum)>=DatePart("ww",Date())+"1
") AND ((tbLiveSupplyReport.Material)=[tblDemandTest]![Material]) AND ((tbLiveSupplyReport.Week)=[tblDemandTest]![Week]));
See attached image.
Currently to pull this data I have it connected to an .atomsvc data feed (which only works with excel) into an excel worksheet and is formatted exactly like the image. I have that excel file linked to my database and a table named tblDemand that I plan on using as the data collector. It will hold the historic information and will update weekly.
Any help is greatly appreciated
Thx JR