It occurs to me that if the fertilizer is dumped in front of a large existing pile and then picked up form the same point, it is last in first out, not first in first out, so this may be all wrong but we can deal with that,
Okay this is going to be weird. And someone will tell me I am effing up. Bear with me. Based on what I reading, this might be the best for you.
I think of a record that says 75 kgs as summary information. It is a count. A different way to think about it would be to think about it as 75 records- each record representing one kilo at a specified strength. So you could have a data structure that looked like:
Code:
deliv_id strength used
1 0.15 0
2 0.15 0
3 0.15 0
4 0.15 0
5 0.1 0
6 0.1 0
7 0.2 0
8 0.2 0
9 0.2 0
10 0.2 0
Higher delivery_id means later delivery. I think the deliv_id field could be a simple autonumber. So this table has 4 kgs of 15 percent, 2 at 10 percent, and 4 high test at 20 percent. The 4 kgs of 15 percent are the oldest (back of the pile) and the 4 kgs of 20 percent are the newest (front of the pile). So if you have 75,000 kilos on hand you have 75,000 records. 75K records or 375K records is nothing for a database- it doesn't care.
I said this was weird. Give it a minute.
Anyway, with that data structure, you can tell how much you have on hand with this:
Code:
SELECT Count(deliv_id) AS [amount on hand],
format(Sum(strength)/count(deliv_id), "#,##0.00") AS [average strength]
FROM deliveries
where used=0
which will output:
Code:
amount on hand average strength
10 0.16
Now, let's say you use 3 Kilos:
Code:
deliv_id strength used
1 0.15 0
1 0.15 0
1 0.15 0
1 0.15 0
2 0.1 0
2 0.1 0
3 0.2 0
3 0.2 1
3 0.2 1
3 0.2 1
Your "on hand" query now says:
Code:
amount on hand average strength
7 0.14
so that is good.
What you need to make this work is something I don't know how to do. I think Alan or somebody would or maybe you could Google.
What you want are two pieces of VBscript code. The first one you use for input. It queries you on the amount being added to the pile and the strength of it. If you say 5 kilos at 12 percent, it enters 5 new records at 12 percent into your table. Could just be a simple little loop, don't know.
Anyway, then you need a second piece of code that does an update when you use some of your stuff and take it 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. If you took 5 units you would set "used" in the top 5 records ordered by id to 1:
Code:
UPDATE deliveries
SET used=1
WHERE deliv_id in (
select top 5 deliv_id
from deliveries
order by deliv_id desc)
That would give you data like:
Code:
deliv_id strength used
1 0.15 0
1 0.15 0
1 0.15 0
1 0.15 0
2 0.1 0
2 0.1 1
3 0.2 1
3 0.2 1
3 0.2 1
3 0.2 1
and on hand results like:
Code:
amount on hand average strength
5 0.14
In the real world you will have the situation where the database says you have 5 kg and the pile is empty just because of counting errors or spillage or whatever . You'll have to deal with reconciling that.
So. I said it was weird. And I'm sorry it is incomplete because I don't know how to write little module things in Access. I would guess the entry code and use code coudl be ties to little forms.
