Adding & subtracting values in two diffrent tables

[brandys]

I am working on an inventory database. I am needing the values entered in one table to add or subtract from another field in another table.

Example:
Supplies are requested. The request is entered into the database. Need number of supplies taken out to be reflected in the total inventory field. The same when supplies are ordered. The amount received will need to be added to the total inventory. This is so there is a running total of what is in the inventory at a given time. I did not know if I could do this with an append query or would need to use sql. Thanks for the help.

 

[Darrylles]

Hi,

How this works really depends on the logic you decide on.

i.e. When supplies are 'requested' do you want the total to be reduced by that amount (even though the supplies haven't physically been reduced at that point)?

Are supplies 'requested' and 'ordered' on a form?
If not, where/how is this done?

I will assume a form.

When the request amount is 'input' on a form - you must have the 'Product' within that form. If you've got the 'Product' in the form, then you can also have the related table - field in the same form. Update this field and it will be saved back to the 'other' table.

Your description is a bit sketchy. If you come back, please describe your relevant tables/fields and relationships.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[brandys]

I have 2 tables the first is the inventory table that contains the fields:
Item#
Description
Price per Item
Price per case
Date
Total used
Total received
Total in inventory
par level
and I will need a begining inventory field for a starting point on the totals that are in the inventory.

Requisition table:
Department Name
Date
Item#
quantity delivered
supply type

the supplies are requested by paper form. I will want the quantity to be decreased when the information from the form is entered into the database (requisition table)there will be a form for input based on this paper form. This will then add the number input to the number already in the total used field in the inventory table. My goal is to be able to decrease and increase the amount in the 'total in inventory' field when the information on the form has been entered.There will also be par level set so that the staff will know when to reorder. I will also be running reports at the end of the month to determine how much each department has spent on each supply type, ie dietary, housekeeping, office, medical, forms etc. I hope that this helps. The goal is for the staff to input requisitions on a daily bases and be able to run reports at the end of the month. I hope that this helps for the big picture.