Received quantity distributed based on order number and order date (1 Viewer)

[artefact]

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

 

[arnelgp]

you need temporary tables to hold the resulting data.

 

[artefact]

Hello arnelgp,

Thank you for your help. Could you please be more specific? I mean how can I handle the distribution of the received quantity vs the order? Do I need to code something? What do you mean temporary table? Thanks beforehands for your help.

Arte

 

[jdraw]

Arte,

If you were describing the logic of how to distribute these products, what would it be?

You have to know that in order to create a process or function.

 

[artefact]

Arte,

If you were describing the logic of how to distribute these products, what would it be?

You have to know that in order to create a process or function.

hello Jdraw,

The logic would be the following

Loop each record on reception table

Assign qty received to right order​

Check if the order line is complete​

If yes go to the next order line​

Go back to the loop

 

[jdraw]

Sounds reasonable. How do you decide which Orders should be filled in what sequence?

Or would you figure out the maximum number of Orders that could be filled with the current Products, and fill those?

Now research looping in MsAccess vba with Google.

Here's a link to start.