Results 1 to 4 of 4
  1. #1
    artefact is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    4

    Received quantity distributed based on order number and order date

    Hello,



    I'd like to perform a report based on 2 tabless (Order Table and Reception Table):


    Order Table
    OrderID (PK)|Item(PK)|Ref|Qty|Order Date
    1|1|Orange|5|20/01/2016
    1|2|Orange|3|22/01/2016
    1|3|Orange|2|22/01/2016

    Reception Table
    RecpID|OrdernumFK|Qty|ReceptionDate
    1|1|2|22/01/2016
    2|1|7|23/01/2016


    Via a request, I would like to be able to fill a table like this

    Table request (Reporting)
    OrderID (PK)|Item(PK)|Ref|Received Qty|
    1|1|Orange|2
    1|1|Orange|3
    1|2|Orange|3
    1|3|Orange|1 (it remains 1 qty to full-fil le line)

    Would it be possible to perform something like that in access?
    I would like to distribute the received qty based on order num and order item + date of recpetion of the good?

    I have programming skill on vba on excel 2010, I think it might be possible to use it for access 2010?

    Thanks beforehands for your help.

    Arte

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I would also have a tWarehouse table to keep the current total.
    tWarehouse
    item,ref,Qty
    1, orange, 0

    reception adds to tWarehouse
    1|1|2|22/01/2016

    tWarehouse.Qty = 2

    order comes in
    1|1|2|22/01/2016

    order1 is fullfilled
    tWarehouse.Qty = 0

    order come in
    1|2|Orange|3|22/01/2016
    1|3|Orange|2|22/01/2016

    whs.qty = 0 , order on hold

    recvg gets more
    2|1|7|23/01/2016

    tWarehouse.Qty = 7

    fulfill order 1,2 = 3

    tWarehouse.Qty = 4
    etc....

  3. #3
    artefact is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    4

    Received quantity distributed based on order number and order date

    Quote Originally Posted by ranman256 View Post
    I would also have a tWarehouse table to keep the current total.
    tWarehouse
    item,ref,Qty
    1, orange, 0

    reception adds to tWarehouse
    1|1|2|22/01/2016

    tWarehouse.Qty = 2

    order comes in
    1|1|2|22/01/2016

    order1 is fullfilled
    tWarehouse.Qty = 0

    order come in
    1|2|Orange|3|22/01/2016
    1|3|Orange|2|22/01/2016

    whs.qty = 0 , order on hold

    recvg gets more
    2|1|7|23/01/2016

    tWarehouse.Qty = 7

    fulfill order 1,2 = 3

    tWarehouse.Qty = 4
    etc....
    Thanks for your help, but how do you hand the Twarehouse qty? by coding?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Cross posted http://www.access-programmers.co.uk/...d.php?t=284482

    Cross posting isn't wrong -- Just please advise users that you have done so, And include a link for reference.

    Here's why:

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

Similar Threads

  1. Replies: 6
    Last Post: 09-04-2014, 02:03 PM
  2. Replies: 3
    Last Post: 07-31-2013, 08:46 PM
  3. Autogenerated Purchase Order Number
    By burrina in forum Forms
    Replies: 2
    Last Post: 12-15-2012, 10:37 PM
  4. display order number
    By Hopeless in forum Forms
    Replies: 3
    Last Post: 08-25-2012, 06:46 PM
  5. Northwind Purchase Order Number
    By jpl85716 in forum Access
    Replies: 3
    Last Post: 09-29-2010, 12:53 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