Ok now this is a very hard thing to do for me!

[Desperate]

I have 1 table and 1 query. Table shows required materials to fabricate structures. Query shows the material quantity in inventory.

Table:
Structure ID --Required Material Code--Required Quantity ---Status
str1 ------------------Mat1----------------------3
str1 ------------------Mat2 ----------------------2
str1 ------------------Mat3 ----------------------1
str2 ------------------Mat2 ----------------------5
str2 ------------------Mat5 ----------------------1

Query: (is a select query gets the total from another table)
Material Code -------Total Stock
Mat1 -------------------3
Mat2 -------------------2

What I would like to do is update status column to;
Full: if we have all materials in stock(which means required qty< stock qty)
Partial: We don't have complete amount of materials but still can fabricate (stock qty > 0)
None : We have none of the item in stock. (ie mat1 total stock = 0)

I am trying to make an update query where Tables required material code equals to query Material Code and Table required qty > total stock but I am not able to get success.

I tried so many ways to do by query, vba.. but I can't manage. May be you can help me.

 

[Galaxiom]

Status is a derived value. It should not be stored. Calculate the status on the fly whenever and wherever it is required for display using the form or report's recordsource query.

 

[Desperate]

Ok. Can you explain more detailed? How I can do that?

 

[Desperate]

I am trying to make an update query which compares the total required qty with stock qty but every time I am getting the "operation must use an updateable query" message

 

[Galaxiom]

Join the two tables on Material code.
Derive a field based on the available stock versus the required use an IIF statement to set the value true or false.

 

[Galaxiom]

You don't want an update query. The status field should not even exist in the table.

 

[Desperate]

Well I removed the field from the table and put an unbound textbox on the form. I wrote
=IIf([Forms]![StructureT]![BOMF].[Form]![SumOfRequired]<[Forms]![StructureT]![BOMF].[Form]![Stock_Qty],"full","none")

to the control source.
The problem is now, it only checks where the cursor is and apply same status to the whole column.

 

[Galaxiom]

Use a version of the IIF statement refering to fields in the tables to make a derived field in the recordsource query of the form. Then display this field in a bound textbox control.

 

[Desperate]

Yess!!! Done!! Thanks alot!!

 

[gemma-the-husky]

but here is a problem though

in your example, your

str1 needs 2 mat2
str2 needs 5 mat2

but you only have 2 in stock.

so what result do you expect to see. what if you only had 1 in stock? what if you had 5 in stock? is the result affected by the avaialbility of other items to fulfil str1 and/or str2.
this can become very complex

 

[Desperate]

You are %100 right. This is far away complex than I manage to achieve at the moment.
In your example;
str1 needs 2 mat2
str2 needs 5 mat2
If I have 2 mat 2 in the stock, status should be full for str1 and none for str2
If I have 6 mat 2 in the stock, status should be full for str1 and partial for str2
If I have 1 mat 2 in stock, status should be partial for str1 and none for str2

Now I just managed to mention status as "full" if stock has complete amount of material and "none" if nothing is in the stock. I am trying to get to the last point step by step.I wish I will not bang my head to the walls

 

[Desperate]

Done perfectly by recordsets. It checks every row as per the quantity and fills status.