Results 1 to 7 of 7
  1. #1
    dalahans is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2018
    Posts
    4

    Update table based on closest match on date

    Hi



    I have got 2 tables: "Orderlines" and "Pricedates"

    Orderlines
    Itemnr Date of purchase Price at date
    A 2018-01-01 0
    A 2018-01-02 0
    A 2018-01-03 0
    A 2018-01-04 0
    A 2018-01-05 0
    A 2018-01-06 0
    A 2018-01-07 0
    A 2018-01-08 0
    A 2018-01-09 0
    A 2018-01-10 0
    A 2018-01-11 0
    A 2018-01-12 0
    A 2018-01-13 0
    A 2018-01-14 0
    A 2018-01-15 0
    B 2018-01-01 0
    B 2017-06-22 0
    B 2016-01-01 0
    B 2018-03-14 0


    Pricedates
    Item Price date Price
    A 2018-01-02 2
    A 2018-01-07 7
    A 2018-01-11 11
    A 2018-01-15 15
    A 2018-01-22 22
    A 2018-01-23 23
    B 2016-01-01 1000
    B 2017-01-01 2000
    B 2018-01-01 3000
    B 2018-02-02 4000
    B 2018-03-03 5000

    I need a query to update the field "Price at date" in the "Orderlines-table"
    It should pick the price from the "Pricedate"-table at the valid date, itemnumber by itemnumber
    The valid date is a date <= "Date of purchase"

    Any help is appreciated


    // Hans

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use DMax on your pricedates table for field price date with filter criteria for item 'number' or letter A, B AND price date<date of purchase:

    Code:
    DMax("[Price Date]", "PriceDates","Itemnr = '" & Item & "' And [Price Date] < #" & [Date Of Purchase & "#")
    This expression is used in the 'Update to' row of an update query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that only gets you the date! you then need to use that in a criteria for a dlookup on item and date to get the price.

    If this is a one off to populate the table because of a change in direction in your design (storing price along with things like tax rates/amounts is a common requirement for invoices etc for audit reasons), going forward you would store this data at the time of the purchase record creation. Otherwise you are creating a potential problem for yourself if dates or prices are changed/corrected meaning the purchase table is now showing incorrect data.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops - I didn't read the post thoroughly!
    I'll defer to your better reading skills...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    dalahans is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2018
    Posts
    4
    Ajax
    It is not a one timer. I will use this to populate prices into a table for a BI solution.
    Can you help me with the query to do the update?

    // Hans

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as suggested you need to use the dmax and dlookup functions.

    as advised, this is not a good way to go and I don't have the time to get involved with something that almost certainly will suffer from 'mission creep'

    Suggest from the advice already provided, try to build the query yourself and come back with specific issues as you find them.

    As a starting point, open a new query, select the table you want to update, then select the field you want to update. Then in the ribbon select update. In the update to row, right click and select build. Select functions>built in functions. Then select DLookup and follow the prompts. When you get to the criteria, refer to colins code

  7. #7
    dalahans is offline Novice
    Windows 8 Access 2016
    Join Date
    Jun 2018
    Posts
    4

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

Similar Threads

  1. Replies: 2
    Last Post: 08-01-2017, 01:40 AM
  2. Update table based on Max date link
    By janmack79 in forum Queries
    Replies: 2
    Last Post: 04-11-2017, 12:46 PM
  3. Replies: 2
    Last Post: 09-03-2015, 04:38 PM
  4. Replies: 4
    Last Post: 05-28-2014, 10:09 AM
  5. Trouble finding closest match
    By cutsygurl in forum SQL Server
    Replies: 1
    Last Post: 02-22-2013, 03:59 PM

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