Update Query

[NumLock]

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

 

[pbaldy]

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

 

[NumLock]

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?

 

[pbaldy]

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!

 

[NumLock]

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

 

[pbaldy]

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!

 

[NumLock]

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

 

[pbaldy]

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.

 

[NumLock]

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.

 

[pbaldy]

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

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

 

[NumLock]

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?

 

[NumLock]

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

 

[pbaldy]

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.

 

[NumLock]

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.

 

[pbaldy]

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

 

[NumLock]

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.

 

[wilpeter]

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

 

[NumLock]

Thx, I translated my database to English

 

[NumLock]

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.

 

[wilpeter]

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).