FIFO (1 Viewer)

[bhelmy]

Hi all

There no idea coming to my mind to how to perform me. That is what I need.

Suppose I have purchase table with following data

Batch no. Quantity. Date.
1001. 100. 01/01/2014
1002. 50. 02/01/2014

Now supposingly sales as follows

Date. Quantity invoice no

05/01/2014. 125 1
10/01/3014. 25. 2


Now the batch reports result I need as follows

Sales. Invoice no 1 100. Batch 1001
25. Batch. 1002

Invoice no 2. 25. Batch 1002

Thanks in advance

 

[arnelgp]

i think it is best to add 2 fields to your invoice, batch no and the quantity.
you need a way to compute the "remaining" balance from
previous invoices when you save the invoice.

or you can put those fields + the invoice_id to another table
and link them when you create the report.

 

[gemma-the-husky]

see this thread with a similar question.

(1) Fifo Bases Sales in MS Access | Access World Forums (access-programmers.co.uk)

 

[MajP]

I think you need a junction table

tblOrder_Batch
--BatchID_FK
--OrderID_FK
--Quanitiy

Assume batch 1 is 20 Units and order 1 is 10, order 2 is 5 and order 3 is 10. I need records like

1 1 10
1 2  5
1 3  5
2 3  5

I can fill this table pretty easily in code, I do not think I can do this in SQL