Hello,

I used an update query to update my stock.
How is it possible to update the stock from the record that I am working in and not all the records in the same table?

So the query only updates the stock from the record I opened

I wouldn't try to update the stock, just calculate it from activity. In any case, you'd add a WHERE clause:

UPDATE ...
SET...
WHERE StockNumber = Forms!FormName.ControlName

I am new to Access so can you help me with that? :)

I have a Product table and a Mutation table

In the Product table I can see the stock for each product
And in the Mutation table I want to add product to different departments.

The purpose of the database is to see the usage of product for each department.

Edit

But is it then still possible to add and remove products to departments?

So for example a department returns a pen. Is it then still possible to easily add the pen back to the stock?

Help with what exactly? As mentioned, I wouldn't try to store the amount on hand. Either in one table or two, you'd track what came in and what went out for each product (product, quantity, date, etc). In the "out" data, you would include the department. It would be a simple matter to query the table to find the amount out by department for any given period of time. There's some info here on inventory:

http://allenbrowne.com/AppInventory.html

Welcome to the site by the way!

Thank you :)
Sorry my English Access terms are not very good (im from the Netherlands).
But you are saying that I need to make a:

Product Table
A table for products that come in
And a table for products that go out

http://i50.tinypic.com/10pvk9s.jpg

Basically, yes. Some would use one table, with negative values for "out" transactions, some would use two tables. I lean towards one, but if the fields necessary for the different transactions are different enough, I'd use two. I would drop the "in stock" field from Products.

Your English is much better than my Dutch! ;)

Haha but I am used to the Dutch version of Access 2007 :) I hope its clear what I am saying because of the translation to English.


If I drop the "In Stock" field how I am able to see what I have in stock?

And how do I make those calculations as you told me before?
So it will automatic do - when I add products to deparments and + when I get products back.

I am a big noob in Access :(

In either case, the amount on hand is the sum of purchases less sales (or distributions in your case). In the one table model, that's a simple totals query that groups by product and sums the quantity field (since distributions are negative values). At any point you run that query, you will get an accurate count based on the transactions.

Trying to keep that value stored is possible, but as noted in that link means you have to account for every possible way the users can add/edit/delete data. Much simpler to calculate it from transactions.

Is it possible if you make me an example query in Access? That would help me allot
Because I really don't know where to start.
The only query that worked was [Mutations].[quantity]+[Products].[In Stock].
And I made a button for the query in a Form.

The query for the one-table model would look like:

SELECT ProductID, Sum(Quantity) As QuantityOnHand
FROM TableName
GROUP BY ProductID

I made now 3 tables

Products
Product ID
Name
Discription

Mutations for each department
Mutation ID
Product ID
Department ID
Quantity

Deparments
Department name
Department Chef
Telephone number

But I don't know how to add the query.
With the QueryDesign button? and do I need to change any fields in the query you posted?

I think I got it working now :)

I used a query that will use SUM
And if I want to add products to a department i need to fill in the form -2 and if i want to add products back to the stock i need to fill in a positive number.

Edit

It doenst work

It will count all the quantity of the records that I placed in Mutations for each Department

Excellent! Glad you got it going. I think you'll be happier in the long run doing it this way rather than trying to update the value.

It did not work :)

I made a query that will calculate the quantity but the query is calculating everything in the Mutations for each Product table.
I need a way that it will only calculate for each product ID.

What is the SQL of your query? Can you post the database here?

I translated it for you in English

When I can add/remove products to departments I need to add suppliers en price but that's easier.

Paul, in case he's written it in Dutch, may I offer this pdf explanation without code..
www.bowls.mb.ca/Mutation.pdf (http://www.bowls.mb.ca/Mutation.pdf)
Pete

Thx, I translated my database to English :)

Ok I got it almost working now :)
I can add products to departments and I can generate a report that is sorted by Department name en Product ID.

My only question is how can I add a quantity that I have default in stock. And how do I need to connect it with my query for the mutations?

And when I add products to departments I use a positive value in the mutations.
And a negative value for the products that return from departments back to stock.
But I think its easier to use negative values to add products to departments because I need to connect my Stock quantity.

Your transactions all flow through the Mutations table as In and Out (I chose to have two fields, but your method using + and - works also), so to add a starting balance it would be a mutation of + that amount.
Presumably the 'return to' location is also a Department (like "stores").
One problem I see is (excuse me Paul for interfering) that I'm gathering from your comments that this isn't a "Take from Stock and Consume" as much as "Borrow from stock and Return to Stock". Thus, Adding to the Department stock is really reducing the actual owned stock (hence a minus).

With the mutations I just add products to different departments. So I have a overview what products a department used.

How do I need to add a Stock quantity and how I am able to add new stock?

Is it possible to calculate the Stock with a Dsum?

There might be a comprehension difficulty on my part, since I am not aware of your activities, so let me attempt my version of what I think would work:
You have a number of Products that are stored at Department "Home" and which are withdrawn by other Departments "Bakery", "Grocery", etc. and which may also be returned to Department "Home" afterwards. You want to know how much of each Product you have left at "Home" and how much is in each other Department. You also want to know the In and Out Mutation patterns for those other Departments. My approach is to include "Home" in the Department Table with its own ID# and to include the number of each Product that are at that location in your overall totals as an inventory control.
Thus: At the start, and each time brand new Product is purchased, add it to Home (a plus transaction in your Mutation table). When removing Product from Home to Bakery, it is a minus Mutation. When returning Product to Home it is a plus Mutation (but only to the Bakery). The total Product in Home at any time may have to be adjusted by the difference between the other Departments' net balance.
Example: 500 Product A exist at Home. 250 are withdrawn to Bakery during the quarter-year and 200 are returned by Bakery during the same period. If they used up 50 in the process that no longer exist, a minus Mutation would then be made to the Home Department to adjust the balance on hand to 450.
Am I even close?