How to split item quantity over most recent dates??

[craig198101]

Hi all,

How would I do the following in Access:

Item A has a total quantity of 150 in the Master Table

Item A has a total quantity of 200 in the Detail Table made up of receipts:

Receipt A QTY 50 date 01/04/2016
Receipt B QTY 50 date 02/04/2016
Receipt C QTY 100 date 03/04/2016

How could I show the split of Item A from the Master Table over the most recent dates only for example Receipt B & C?

Item A
Receipt B QTY 50 date 02/04/2016
Receipt C QTY 100 date 03/04/2016

I some how need to split each item total in the Master table over the most recent receipt dates in the Detail table.

Any ideas would be great

 

[Micron]

I see no Item field in the receipts table example. Did you forget to include it here, or does it not exist? This should be simple if your tables are properly normalized. There should be a PK (primary key) field 'itemID' in tblMaster which is also in tblDetail as a foreign key (FK). After that, it's a simple matter of knowing what you mean by "over the most recent dates" in order to decide how far back to go. Some would say 01/04/2016 is recent, relatively speaking, but you don't indicate what the cut off point is.

After thought: it's odd that you seem OK with having two different quantities stored for the same item, much less keeping quantities for the same item in two different tables. Just doesn't seem right based on the info you provided.

 

[craig198101]

Hi Micron,

Thank you for the reply.

I will try and make it clearer

I have data from an ERP system and data from a duty system.

The Master data is from the ERP system and the detail data is from the duty system.

There could be a higher quantity of the same item in the duty system than the ERP system. I need to use LIFO (last in first out) to split the ERP quantity over the receipt dates in the duty data.

For example:

Item A (ERP data ) has qty 150

Items A (Duty data) has total qty 200
Receipt A QTY 50 date 01/04/2016
Receipt B QTY 50 date 02/04/2016
Receipt C QTY 100 date 03/04/2016

Based on LIFO the result would be
Item A Receipt B QTY 50 date 02/04/2016
Item A Receipt C QTY 100 date 03/04/2016

Primary key is item number.

 

[Micron]

OK, so two db sources are involved. I can't think of a query solution because the number of rows to be returned is an unknown, plus I presume there is the possibility that the number of periods involved (count of receipt dates) may not evenly divide into the ERP quantity involved. For example, (using simple numbers) if there are 13 "A" items in ERP table how would you want to split that? It doesn't divide evenly over any number of periods save 1 or 13, yet there could be 4 periods of in/out transactions.

If you're prepared to deal with an Access vba solution, I could probably devise one but would need to know how to mete out quantities where the division is not as elegant as what you've shown - unless you say it always will be. You'd need a query based on the two tables, inner joined by item number, that returns the Item and qty from ERP and the receipt, qty and date from duty system.