Results 1 to 8 of 8
  1. #1
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51

    How to update past data based on most recent date.

    I can't figure out how to update a sales history table based on date.



    Item cost.

    Let's say, over time, products were sold with an incorrect or $0.00 cost.

    Now, I want to run a query to update the sales history table with the item cost most recent to the sell date.

    The item may not have been received the same day it was sold, so need to grab the most recent cost from the day it was sold.

    Any ideas? Seems complicated... Thx in advance...!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some examples of the data and desired results would help clarify. Reads like you'll need a sub query to get whatever you consider to be the last date. I'm thinking that sub query needs to get Max([SomeDate]) WHERE [SomeDate] <= [OtherDate]
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51
    Sales
    tblSalesInvoiceHeader
    tblSalesInvoiceItems

    Receiving
    tblCosting

    ItemNum
    UnitsReceived
    ItemCost

    InvoiceItemDescription (ItemNum)
    InvoiceItemCost

    This is exceeds my skillset. How to update a table over time where you need to grab the most recent value in time.

    Item A bought 1/1/2022 sold 1/3/2022 (update 1/3 sales record with 1/1 cost) do this to thousands of records...

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A one line description with one example was not what I had in mind. Suggest you enter some data in Excel sheet then copy/paste into a post or see if this puts you on the right track or at least tells you if any of the example problems match your issue

    http://allenbrowne.com/subquery-01.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    This is a fairly common question with sales apps. As Micron suggested you need to isolate the record in the costing table that has the latest effective date for Item A that is before the sales date. To do that you could use a dMax with the right criteria; I use a Top 1 query in which I group by my desired field (in your case Item) and order descending by effective date. Once you have the applicable effective date for the item it becomes easy to go back to the table and get the cost and use that to update the invoicing table if the cost in that is different
    were sold with an incorrect or $0.00 cost
    .

    Without a small sample db I am not sure what more can we\I offer....

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Agree with Micron, your description isn't very clear.

    so need to grab the most recent cost from the day it was sold.
    I presume this means the latest cost prior to the invoice date, not from

    Let's say, over time, products were sold with an incorrect or $0.00 cost.
    Is this every sale? or just some? if the latter how do you identify which records need to be updated?

    And to be clear, you not concerned about stock level/pricing at the time of sale? - i.e. you have in 100 items at £1 and later 100 items at £1.20. You then make a sale of 50 items - which will be costed at £1.20, regardless of the fact it should be £1.

    After reviewing the link provided by micron, if you require more help, provide some example data from your sales history table and your prices table and show the outcome required

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the other responders. You are proposing to change historic records--not sure what ramifications may ensue even if you do "whatever" correctly. Readers need to see some sample data and the logic involved.
    What about receipts that customers may have received? Or any audit/accounting activity against existing records? Readers need more context and some sample records showing what you have and what you expect with the adjustments.


    A common practice is to store the actual price that you sold the item for within the OrderDetail record. This allows for sales, discounts, loyalty programs etc and may not be the the official selling price (often the CurrentSellingPrice) of the item/product.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think the OP has the price, but needs to put a cost against it. And appears to be a sales history table rather than invoice header/lines tables.

    Would be good to know the purpose behind storing the cost since normal practice for reporting would be to calculate it as required - and update queries are notorious for being difficult to get to work when more than one table is involved, which would appear to be the case here.

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

Similar Threads

  1. Populate value based on most recent date
    By Poohbear0471 in forum Forms
    Replies: 7
    Last Post: 02-15-2021, 01:25 PM
  2. Replies: 4
    Last Post: 01-25-2019, 06:28 PM
  3. Calculate Date based on Past Date and Frequency
    By jchandler88 in forum Queries
    Replies: 9
    Last Post: 10-01-2018, 07:15 PM
  4. Update Query with Most Recent Date
    By W_Green in forum Queries
    Replies: 4
    Last Post: 02-23-2016, 12:30 PM
  5. Replies: 1
    Last Post: 07-23-2013, 01:39 AM

Tags for this Thread

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