I need to add a record in one table and have it add to a matching field in a second table

[Islandtraveler]

For some reason my mind is slipping.. I have an inventory database with each record identifying an item and several fields with qty of that item in different locations. I have a second database where people input changes to the stock locations (this tracks the inputter by records and their actions).

I want to input numbers in the second database and have it add to the field in the first without creating a new record there. Any help please?

[Islandtraveler]

PLEASE. even if you think it won't work... Let me know.

I REALLY need to get this done and if I can't update a field by adding to the number already there I would like to know I'm going down the wrong road.

Thanks

[comment]

This is not very clear, but it seems that if you establish a relationship between the two "databases" (files? tables?) based on ItemID, you could then define a calculation field to add up the different quantities - whether local or related.

[Islandtraveler]

THANK you for responding... To clear it up:

DB1 holds the item description and related info and 6 fields relating to locations where that item may be located, it is a quick reference and shows a total of all the sites too. To keep installers from getting into that area i created a second DB to track who takes or moves items in or out of locations (in this DB2 I create a record each time they input the changes. THIS is where I would like the fields to be updated in DB1 to either add to a number or with a negative remove from the stock level (without creating a new record there).

I had it create a new record every time and then do running totals which worked if I did it, BUT if they forgot to sort or find a specific item everytime it would do running totals with odd results (as other items or older records were showing) so even with buttons scripted to sort and such for them, they still find a way to mess up. I then wanted to do it this new way to clean it up. Hope that helps THANKS!!

[comment]

Perhaps a better method might be to have a table of Items and a table of Transactions (and maybe a third table of Locations). But even with you have now, it should work the way I suggested above.

[malia]

If i think I understand you correctly:

you have one table that holds the "master information" of the items and the locations of those items and quantities

you have another table that keeps track of movements of those items or increases or decreases in quantity

If this is correct

then

In your master file that tracks the summary data, you can use the calculation field and base the results of the calculation field on the sum of the value in the appropriate field in the table that tracks all the changes. Which is essentially what comment wrote (it's not a summary field)...

However, you may need more fields becuase of this:

THIS is where I would like the fields to be updated in DB1 to either add to a number or with a negative remove from the stock level (without creating a new record there).

You could script this, however, I don't. But I think I understand your problem, because I have similar issues and this is how I worked it out:

You have a quantity moved, but people don't automatically enter negative numbers, they just enter the quantity that they took from one place and moved to another (or whatever).

What I did was define the type of action using a field with an action code. Then I used another field to calculate the impact of that action upon the inventory.

I cannot make sense of it with your data but with mine, there are orders and items.

So an order has a quantity of 2, but it's impact on inventory is to decrease it by 2 (-2).

Or if you are adding to inventory a quantity of 2, you are increasing it by 2 (positive 2)

So I have a calculation field that looks at the TYPE of action and determines whether it is a positive or negative impact on inventory and then returns a negative or positive value of that number accordingly.

So if it is an order, the calculation field returns qty -2 becuase it is decreasing the quantity in inventory by 2. If it is an addition to inventory, it leaves the number as is (because people normally enter normal numbers).

I think this is what you're trying to do.

My "summary table" calculation field summarizes THAT field's data, the one with positive or negative calculated values.

I hope that explains things.

And I am sure there probably is a better way to do it, but for auditing purposes, I didn't want to script those changes becuase if there was an error, I wanted to be able to see which action record contained the error (i.e. who messed up) and correct it accordingly.

I hope that helps, if I understand the problem correctly.

As far as I can see in my testing (and I've only been working on THIS one for 2 days) it works properly regardless of sorting. The calculation field that looks for the sum only looks for the sum on related records so it should not matter if those records are in a found set or not (at least that I have been able to see, still testing)

HTH!