Hi
Im try to use a database to control my fertilizer stocks. Fertilizer is dumped with different percentages of lime at the front of the stockpile and worked back so the first delivery is used first. If I have a dumped table which I enter deliveries into.

Date Type KG %
11/11/13 F20 3000 .12

I would like a query which takes the amount being added to the pile and the strength of it and places it in another table or something as shown below. If you say 3000 kilos at 12 percent, it enters 5 new records at 12 percent into a table or something and 0 in the used column.

KG percentage used

1 .20 0
2 .20 0
3 .20 0
4 .20 0
5 .20 0
6 .12 0
7 .12 0
8 .12 0
9 .12 0
3006 .12 0


Each day the removed table is update with the amount of fertilizer removed.

Date KG
11/11/13 1000

A piece of code would update the amount used from the pile. With the above original data, when you reported taking 3 kilos, the update would set the used field for three records with the highest id to used (1). If you took 5 units you would set "used" in the top 5 records ordered by id to 1

kg percentage used
1 .20 1
2 .20 1
3 .20 1
4 .20 1
5 .20 1
6 .12 1
7 .12 1
8 .12 1
9 .12 1
1000 .12 1
1001 .12 0

The remaining amount is calculated as below.

SELECT Count(KG) AS [amount on hand],
format(Sum(%)/count(KG), "#,##0.00") AS [average strength]
FROM deliveries
where used=0

Does anyone have any ideas. Thanks Jeremy