Calculating remaining materials

[AlexanderHUN]

Hi I have a rather complex problem.
Where I work I have to create a table which shows whether we have enough material to start constructing the given isometrics or not.
Let's say we have 3 isos named 001, 002 and 003 and we have a material called MAT1. This MAT1 is needed in all 3 isos, in total 15 is needed. 8 for iso 001, 4 for iso 002, and 3 for iso 003.
I order 15 pieces of MAT1 material and when they arrive I open my databank and insert 15 to the given material.

Now starts the fun part: We start working on the given isos that means first I need a table to check whether we have enough materials to start working on the given iso or not. When we have enough materials, then with a checkbox I can mark the given iso as "build ready" and this is the point where it should update the remaining available materials for the other isos (Since we needed 8 mats for iso 001, the remaining should be 7 when I check whether iso 002 is "build ready" or not). The table looks kinda like this:
Main table with the isos:

Project	ISO Nr.
XXX	001
XXX	002
XXX	003

<tbody>
</tbody>

Right now when I click on any of the ISO Numbers twice, a query pops up which lists all the materials needed for the given iso like this:

ISO Nr.	Material	Needed	Ordered	Received	Sent for prefab	Missing?
001	MAT1	8	15	15	Yes	(Yes/No)

<tbody>
</tbody>

This is how it looks like roughly. What I'd like to do is the last column (Missing?) to work, so when I select yes in the "Sent for prefab" it would automatically update the quantity of the available materials of the give material type, so when I open the same query for iso 002 the available materials would be only 7 (Since we had 15 and needed 8 to finish the first iso)
Is there a way to make this work?
Thanks in advance.

 

[Micron]

Is "Missing?" really Available? You can do this with a calculated query field that calculates Received - Needed IIF [Sent for prefab] 'Yes'. This could be in query 2, or you can make this an unbound control in your form with an expression =IIF([Sent for prefab]='Yes', [Received]-[Needed],[Received]) assuming Received is the value you want if 'No'.

I have a suggestion. I'd add a 'Reserved' field to the materials table and store the job id there. When you query to discover if materials are in for 002, you ignore the rows with anything in the Reserved field (such as 001). Not only does that give you the remaining total available, it identifies material quantity reserved for which projects. As we know, a physical count would show there is enough for 002 without considering material reserved for other jobs. This should aid in preventing material being taken for another job as well as allow you to 'borrow' material for a job which might come in later but at a higher priority, and know the amount you have to re-order to replace material against the correct job.