How to compare value of one field with another?

[deepcec9]

Hi, Im new to access.

Trying to build a inventory database, following are the tables and its fields

product
productid productname supplier uoq weight

warehouseIN
productid productname supplier uoq quantity PONumber invoicenumber caseid

warehouseOUT
productid productname supplier uoq quantity PONumber invoicenumber caseid availabledate

warehouseIN is product coming into the warehouse and OUT is products going out. So warehouseIN is the inventory table.
The problem im facing is, when I enter the quantity field in warehouseOUT(products going out) its not checking whether the product is in the inventory or not. If the available inventory is less than the value in quantity field system should display a warning message. Please help me in this.
If anymore info needed please ask...

 

[leanpilar]

you can use function dlookup on exit event to check if qty out is < than qty out or can use openrecordset option to check and change qty in based on qty you are removing.

sub fiel_onexit()
if DLookup("quantity field", "warehouseIN", "productid =" & me.productid)< me.quantity field then
msgbox "ERROR"
me.quantity field.setfocus
end if

 

[deepcec9]

Forget to mention one thing,
there are different records of same product in the inventory.
So when quantity out is entered calculate the total available in the inventort and decrement the quantity out value.
Do I need to create new table for warehouse Inventory or warehouse In table is enough?
Sorry started using access few weeks back...

 

[arnelgp]

you should also place a date field in your warehouse in, you know FIFO system, the first product that arrived to your warehouse should be first deducted.

 

[leanpilar]

you can use a query to calculate the difference between the total of in and out to get the remaining qty. then the same code to look in the query and not in the table
in query design select the product id and group by and the field in where are the qty you need and add sum to this field
do the same for the out
make a new query (3rd) with select both query make relationship between the product id and add all the field and add a new calculated field to calculate IN - OUT
and the same code use to search in the 3rd query and not in the table

 

[Galaxiom]

You have ProductID, ProductName and Supplier in three tables.

Stop all further development right now and learn about normalization before you go any further.

 

[deepcec9]

these three values are fetched from one single table i.e; product registry

 

[Galaxiom]

these three values are fetched from one single table i.e; product registry

Then you need to be accurate in the description of your table structure.

following are the tables and its fields

product
productid productname supplier uoq weight

warehouseIN
productid productname supplier uoq quantity PONumber invoicenumber caseid

warehouseOUT
productid productname supplier uoq quantity PONumber invoicenumber caseid availabledate

warehouseIN is product coming into the warehouse and OUT is products going out. So warehouseIN is the inventory table.

 

[deepcec9]

you should also place a date field in your warehouse in, you know FIFO system, the first product that arrived to your warehouse should be first deducted.

Product is taken based on PONumber and Invoice number not by FIFO/LIFO.
One single product may have different PO and invoice numbers, so when a product is ordered (taken out) corresponding product with same PO and invoice number must be reduced from the inventory

 

[Galaxiom]

Product is taken based on PONumber and Invoice number not by FIFO/LIFO.
One single product may have different PO and invoice numbers, so when a product is ordered (taken out) corresponding product with same PO and invoice number must be reduced from the inventory

Many inventory systems do not store the Quantity On Hand at all but calculate it from the transactions as required. I would suggest that you employ that technique.

I would also suggest you combine the warehouseIN and warehouseOUT tables into a single table with a field to indicate the nature of the movement. Use negative quantities for OUT so that you can Group By ProductID and PONumber and Sum the Quantity to calculate the remaining available stock on all products and POs.

 

[deepcec9]

Many inventory systems do not store the Quantity On Hand at all but calculate it from the transactions as required. I would suggest that you employ that technique.

I would also suggest you combine the warehouseIN and warehouseOUT tables into a single table with a field to indicate the nature of the movement. Use negative quantities for OUT so that you can Group By ProductID and PONumber and Sum the Quantity to calculate the remaining available stock on all products and POs.

Thank you for the suggestion. When I save the product out form system should decrement/reduce the record from warehouseIN table/transaction table
For eg: ProductOUT (10 quantity PO:123 Invoice:dfg)
the following record in warehouseIN must change to (40 quantity PO:123 invoice:dfg) actual quantity was 50 decremented by 10. It may sound silly to you, sorry im new to this system