Help? Updating quantity in one table based on changes in another?

G

Guest

Hi, I am desperate for some help.

I have 2 tables: Inventory and Acquisitions. Each has ProductName, Size, and
Quantity fields that correspond (Product Name and Size are keys from one to
the other table). Basically, what I want to do is designate a quantity in
Acquisitions that reduces the quantity level in Inventory. (That is, this is
an Inventory list for a food pantry. If I have 5 loaves of bread in
Inventory, and I give 2 of them away in Acquisitions to a client, I want the
resulting Quantity in Inventory to reflect that I now only have 3 loaves of
bread.)

I have tried using an Update Query for this, but the way it is working now,
the query updates all of my records in Iventory, even ones that do not appear
in Acquisitions. It reduces the Quantity in Inventory properly for the items
it finds in Acquisitions, but it clears all Quantity information for all
other records in Inventory.
 
D

Douglas J. Steele

Realistically, you shouldn't be doing this. What happens if you update one
table and forget to update the other? Which one do you believe?

Instead, you should store your acquisitions as pluses (or minuses), and have
a query that sums them to your inventory to be able to dynamically determine
what your true inventory is.
 
G

Guest

HI Douglas, and thanks for the quick response.

I agree that I don't want one table updating without the other. My goal is,
if I assign an item to be sent away with an acquisition, it would
automatically update the Inventory table without my having to do anything
else to it.

Do the minuses work for this? Can you tell me how I would implement that
specifically? Is it an update query?

Sorry if this seems obtuse; I am new to advanced queries.

Thanks a lot!
Rebecca
 
G

Guest

Thanks, Tom.

That may be a little above my head, but I will see if I can get it to work
for me.

Rebecca
 
G

Guest

Hi Rebecca,

If you have any trouble implementing Allen's procedure, be sure to post
back. You might even get a reply from Allen himself!

If my lead to Allen's article has helped you, please consider marking my
response as an answer.

Tom
______________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top