Results 1 to 7 of 7
  1. #1
    Pabloplatinum is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3

    How to create this query

    Hi,

    Tabla A

    Part Available stock Sales # Age in days
    M45612 1 901004816 40
    M45612 2 901006075 37
    M45612 8 901004860 35
    M45612 4 901003870 35

    Tabla B

    Part ID # Sales #
    M45612 74682
    M45612 7D481
    M45612 7D358

    I have two tables, as I can get in “Table B” the results of the column Sales # automatically looking at “Table A” considering the available stock quantity and age in days. The result should look like this.

    Part ID # Sales #
    M45612 74682 901004816
    M45612 7D481 901006075
    M45612 7D358 901006075


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend you do some research on Stock Aging, and get a clear statement of what you are trying to do.
    Find some material/examples and work through the logic.
    You may find some links of interest in this older thread.
    Or this one
    Here's info on FIFO
    Or this one.

    General info on stock aging.

    Here's a link to an Excel solution. You may get insight from the process involved and either use Excel or Access
    to attempt a solution.

    Another Excel solution.

    Good luck with your project.
    Last edited by orange; 12-08-2016 at 07:14 AM. Reason: spelling

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I don't believe you can as I do not see a unique Key field and Foreign key field in those 2 tables? And how in your results does the 2nd and 3rd records have 901006075 as the Sales#?

  4. #4
    Pabloplatinum is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3
    Table A is fixed, I have to fill table B manually but considering the available stock quantity and age in days. The unique Key field is Part.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Sorry still do not understand what you are asking, maybe someone else gets it. Or try to explain it again. So you have TableA, you fill in TableB and you want a query to combine those and produce some results? But again, what unique values link TableA and TableB, Part is not unique to the records in those tables.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Pabloplatinum is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    3
    I'm checking the links. Thanks for the information.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-05-2016, 09:07 AM
  2. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  3. Replies: 5
    Last Post: 04-04-2014, 03:33 PM
  4. Replies: 7
    Last Post: 01-16-2014, 09:17 AM
  5. Replies: 3
    Last Post: 07-10-2012, 05:23 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