Update records

[Southern Prince]

Hello all...

What I am doing is attempting to update a table through a form. I have been reading up on update queries, but I am not quite understanding how to use them. I have a table containing consumables that we constantly issue and receive. I have a form for each of these functions. Example: I order two ink cartridges from a vendor through a purchase order. When they arrive, I would like to enter the qty. I am receiving, then have it automatically add it to the table value. I don't know where to implement the arithmetic. Also, for PO's with multiple items, what's the best way to update the table with those?

Thanks in advance...

 

[stopher]

I am receiving, then have it automatically add it to the table value.

Could you explain a bit more. Add it to what value in what table ? Does a line on a purchase order only ever have one delivery ? Or could a PO be fulfilled by several deliveries ? If it is the former then you just need a qty ordered field and a qty received field in your table and for both to be present on your form.
If it is the later, then, yes you could keep adding to the qty received field (either manually or more sophisticated) but there are risks in your design for doing this. What happens if someone accidentally records a delivery twice? How would you be able to tell if they'd done that ? How would you know how many deliverys had been made and when and which delivery notes they tie up with?

Also, for PO's with multiple items, what's the best way to update the table with those?

Typically you would have a PO_header table and a PO_detail table. The header would contain info such as PO_number date_ordered, vendor number, ordered_by etc. The detail table would contain PO_number (to reference the main table), Item_Ordered, Qty_Ordered, unit_price etc (and maybe qty_received if you decide there is only ever one delivery). In this fashion you can record several items for one purchase order. Have a read up on relational database theory. Also, have a look at the Northwind sample database which shows the princiople but for orders (order header/order detail)

If you have multiple deliveries against a PO line then you will need a third table to record deliveries per line including PO_number, item, qty_received, date_received, delivery_ref. From this table you then calculate what has been received using queries and can compare to the original PO qty. Note that you do not store these calculations in a table since you can always derive them using queries.

hth
Stopher

 

[Southern Prince]

Update records...

Ok, I checked out the Northwind sample. The subform they have on there is exactly what I need to do. Our PO's are filled in one shipment. Stopher, can you assist me in setting up a subform like the "Orders" form Northwind one? I would like to bring up a "Receive PO" form, then in allow users to add items one at a time via a blank subform.

Thanks in advance...