I have a stocktake file showing quantity in stock per item:
Item
StockQty
A
5
B
10
C
7
I have a file of Stock Received showing quantity received by date for each item
Date
Item
Ref
QtyIn
03-Jan
A
5497
3
05-Feb
A
5346
2
31-Mar
A
5386
2
05-Mar
B
5029
3
12-Apr
B
5442
5
15-May
B
5289
7
12-Feb
C
5401
3
08-Mar
C
5334
1
09-Apr
C
5267
5
I want to age the items in stock in the stocktake file by listing the Stock Received trasnactions that make up thw stocktake quantity ie by making a new table like so:
Date
Item
Ref
QtyIn
03-Jan
A
5497
1
05-Feb
A
5346
2
31-Mar
A
5386
2
12-Apr
B
5442
3
15-May
B
5289
7
12-Feb
C
5401
1
08-Mar
C
5334
1
09-Apr
C
5267
5
So, the stocktake quantity for A is made up of the full receipt on 31-Mar and 05-Feb, and 1 of the quantity of 3 received 03-Jan.
