Need help on Allocations (similar to FIFO/LIFO?) using 2 tables

[pj2024]

Hi everyone,

My example is I have 2 tables and need the 3rd table as my output. The goal is to deduct all I can from Bank A, then B and finally C based on numbers from table 2. Table 1 and 2 will always be the same amount.

i tried to use the example here but doesnt seem to work if i change the numbers (https://stackoverflow.com/questions/...s-in-ms-access)

Greatly appreciate any help !

[Micron]

Best that you post what you tried, else how are we supposed to see what's wrong with that?

[orange]

pj,

Have you searched this forum for FIFO? I know there have been sample databases and discussions over the last few years.

[pj2024]

sure...If I use 25 (or higher) the query seems to work. If you choose 9 (as an example) - i get some negative numbers in the output

[ano]

the parameter is for testing without form, if order is from table u need to join the table

PARAMETERS [order] Long;SELECT Product.Prod, Product.Btch, Product.Qty, (Select Sum(Qty) 
From Product As T 
Where T.Prod = Product.Prod And 
            T.Btch <= Product.Btch) AS Total,
 IIf([Total] - [Order]<=0, [Qty],
 iif([Order]-([Total]-[Qty])<=0,0,[Order]-([Total]-[Qty]))) AS Sale
FROM Product;

add iif for no wrong result

to insert into 3rd table use nxt code

INSERT INTO sale ( prd, btch, sle )SELECT p.[prod], p.btch, p.sale
FROM [product query] AS p
WHERE p.sale > 0;