Hi, i have three tables, (tblMaterials,tblCustomersSales,tblMiscSales), the tblMaterials table contain all Items in the store and the other tow tables contain the sales for this store, so i want to calculate the remaining Items in the store so i have to calculate the sum of each Item and how many Items from this item has been sold, if i want to show this calculation by PieceID its k no problem but how can i show all Items in subform and do calculations for each item( i.e i have entered the Item which its PieceID = 3 4 times and each times i bought 50 Item, then in the tblCustomersales theres is a PieceCount = 20, and tblMiscSales contain also PieceCount = 50 , now i want to calculate how many items i have in the store this will be simple if did it one item but i have many items how can i do this

ItemID= 2 Pieceinstore = 500 PieceCountinCustSales = 50 PieceCountinMiscSales = 200 Remain= 250
ItemID= 3 Pieceinstore = 500 PieceCountinCustSales = 50 PieceCountinMiscSales = 200 Remain= 250
ItemID= 4 Pieceinstore = 500 PieceCountinCustSales = 50 PieceCountinMiscSales = 200 Remain= 250

i want the result to be like this

if any one can help me

thanks in advance

base a query on tblMaterials

field --> ItemID

field --> Pieceinstore

field--> PieceCountInCustSales: nz(dCount("ItemID", "tblCustomersSales", "ItemID=" & [ItemID]),0)

field--> PieceCountInMiscSales : nz(dCount("ItemID", "tblMiscSales", "ItemID=" & [ItemID]),0)

field --> Remaining: nz([Pieceinstore]) - PieceCountInCustSales - PieceCountInMiscSales

Hi strive4peace2005

thank you for your reply, it works fine but i have one more question

when i display the result of query in continous form, i have tow fields (Remaining and MinCount), MinCount is the minimum count of pieces should be in the store, so i want to add new textbox in that form named "Needed" this will display "Yes" if the Remaining field is less than or equal MinCount and "NO" if not,i did that but this field display the same result for all fields depend on the first check, i did that with a VBA Code, so if there is any ideal how to do it.

thanks in advance

controlSource --> =IIF([Remaining] <= [Mincount], true, false)

where Remaining
and
MinCount

are control NAMES on your form

IIF is immediate if

IIF(condition, value-if-true, value-if-false)

ps it would be good to put this equation, also, in the query the form is based on

field --> Needed: IIF([Remaining] <= [Mincount], true, false)

always define fields AFTER the displaying the fields they are based on