Received quantity distributed based on order number and order date

[artefact]

Hello,

I'd like to perform a report based on 2 tables (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-fille 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 reception 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 beforehand for your help.

Arte

[zmbd]

+ most of our experts are not able to d/l unrequested attachments, mainly due to IT-Security policies and/or firewalls.

+You wouldn't add another table here instead create a query.

Open the query editor

Show the [Order] table
Show the [Reception] table

It appears that the related field is between:
[Order]![OrderID] and [Reception]![OrderNumFK]

Once that relationship is created, decide if you want to show all of the [Order] table and matching [reception], only matching records between the two (default) or all of the [Reception] table records and matching [Order]

Now drag into the grid (or use the dropdown lists)
[Order]![OrderID]; [Order]![Item(pk)]; [Order]![Ref]; [Reception]![Qty]

Line 7 of the desired "table" is more problematic. This is not something that you can do with either a data-table nor directly within a query. What you can do is create an aggregate query that sums against [Order]![Qty] grouped by [Order]![OrderID].

From there I'll have to think about how to work the two together. This isn't something I usually do so I've not an easy solution for that. However, I think you'll be able to do a calculated control in either a form or a report to show the line as desired. You might be able to build a union query for this; however, I'll have to give that some thought.

--
Unlike a spreadsheet/workbook, it's not a good idea to duplicate the data as proposed in the original post Database Normalization and Table Structures

--

I am guessing that you primary key for [Order] is a composite key based on both [Order]![OrderID (pk)] and [Order]![Item (pk)] ?

Are the field names actually [OrderID (pk)] and [Item (pk)]
or are they [OrderID] and [Item]?

+ Composite keys as primary keys, will cause you some difficulties down the road. I do use composite indexes; however, I use a single field for my primary key - usually an autonumber field. This is after years of programing and building databases. Composite keys sound great, people swear by them, and they are a pain to work with.

+ If you are actually using the "()" in names, I highly advise changing the design now. This may cause you issues in the very near future:
... Error message when you use special characters in Access databases
... Access 2007 reserved words and symbols
... Problem names and reserved words in Access

It's old school; however, I highly advise not using spaces or anything other than alpha-numeric and the underscore for names in tables, queries, forms, controls, and fields.
--