Results 1 to 4 of 4
  1. #1
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78

    Update Query the overwrites Future dates only

    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.
    Click image for larger version. 

Name:	Example.PNG 
Views:	12 
Size:	31.8 KB 
ID:	34775

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is the issue? You show data and query, so what happens?

    Suggest you test on a copy of data.

    Why do you update data as opposed to importing new records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JayRab is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    78
    It is working now

    I did not know if they would work but figured it wouldn't and when I run it I get a cannot update '(expression'); field not update able error. Fixed it had the tables backwards was trying to update to the excel linked table.. its been a long day.


    The report i pull has weeks current -up to 2019 week 51 and it updates almost daily so week 30 will change several times before I lock in the data the Saturday before the week starts (week starts on sunday) also throughout the current week the numbers will change as I produce parts.

    Would import/appending the records overwrite the existing data?

    As an example I would go and manually change the week 30 demand quantity all to 10 and see if would update all the values.

    The scheduling tool will be used look into the future and schedule the skus on the appropriate machine/line and verify I have the machine/labor hours available to meet demand.

    For those who might need example code here is what I used to accomplished exactly what I wanted.
    Code:
    UPDATE tbLiveSupplyReport INNER JOIN tblDemand ON tbLiveSupplyReport.Helper = tblDemand.Helper SET tblDemand.Quantity = [tbLiveSupplyReport].[Quantity]WHERE (((tblDemand.WkNum)="PD") AND ((tblDemand.Material)=[tbLiveSupplyReport]![Material]) AND ((tblDemand.Week)=[tbLiveSupplyReport]![Week])) OR (((tblDemand.WkNum)>=DatePart("ww",Date())+"1 ") AND ((tblDemand.Material)=[tbLiveSupplyReport]![Material]) AND ((tblDemand.Week)=[tbLiveSupplyReport]![Week])) OR (((tblDemand.Yr)>Year(Date())));

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can import/append to existing table without overwriting existing. Certainly if you want to retain historical info that is what you should do but I don't know your data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding Dates to have a future date
    By gambesq in forum Access
    Replies: 2
    Last Post: 02-12-2018, 10:30 AM
  2. Using Dates to count down to a future date.
    By mbenton in forum Database Design
    Replies: 19
    Last Post: 06-23-2016, 02:08 PM
  3. Help using update query to change dates
    By goingcrosseyed in forum Queries
    Replies: 1
    Last Post: 12-03-2012, 07:17 AM
  4. Calculated Future Dates
    By Monterey_Manzer in forum Forms
    Replies: 5
    Last Post: 10-02-2012, 05:02 PM
  5. Update query for 12/30/1899 dates
    By P5C768 in forum Queries
    Replies: 8
    Last Post: 09-25-2009, 09:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums