Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    14

    Unanswered: sql to fill null values with info from next date

    I have a table in Access built from an inventory stock log table (the stock log shows every change in inventory position).

    The table contains three columns -- item, date, inventory

    Unfortunately since not all items have stock changes every day, some of the inventory fields are blank.

    If the stock log is blank, the inventory on that day should be the same as it was on the last date that had info -- how can I ask Access to fill blanks with the last-known inventory?

  2. #2
    Join Date
    Oct 2010
    Posts
    51
    Do you want to add a new record if nothing has changed on the previous record? I would think that might be a bit redundant.

    But you can create a query that selects the most recent date grouped by item or inventory field. Then append data where the new record is null.

  3. #3
    Join Date
    Jul 2010
    Posts
    14
    HI and thanks --

    I need to end up with a record showing inventory position for every day so I can get a true count of cases in inventory each day -- currently the inventory count is way off, because if there was no transaction on that day, no inventory cases show for the item.

    Right now I see 2 cases of widgets in inventory on Jan 1st, and 1 case on Jan 4 -- meaning I sold one on the 4th -- but need to be able to see that I also had 2 cases on the 2nd and the 3rd.

  4. #4
    Join Date
    Jul 2010
    Posts
    14
    in other words I need the IIF for IIF(the inventory from the stock log for this date) is not null then take that info, if it is null then take info from last date where it wasnt null.

    Dont know how to write the second part

  5. #5
    Join Date
    Jul 2010
    Posts
    14
    here's a snippet of what I have -- you can see that I had 10 cases of this item on 9/28, and went down to 8 cs on 10/4. This means that I had 10 cases on each day in between. Just looking for a way to fill in the blanks.

    DATE ITEM INVENTORY
    20100928 VN230908 10
    20100929 VN230908
    20100930 VN230908
    20101001 VN230908
    20101002 VN230908
    20101003 VN230908
    20101004 VN230908 8
    20101005 VN230908
    20101006 VN230908
    20101007 VN230908 7
    20101008 VN230908 4
    20101009 VN230908
    20101010 VN230908
    20101011 VN230908 3
    20101012 VN230908
    20101013 VN230908 2
    20101014 VN230908 1
    20101015 VN230908
    20101016 VN230908
    20101017 VN230908
    20101018 VN230908
    20101019 VN230908 13
    20101020 VN230908 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •