Help with a Query

[JakeMann41]

Hi Guys

I need a bit of guidance on query writing

I have created a basic database that will remove and add stock (see attached)

The MovementsForm has buttons that will look at the queries (macro attached to the buttons) and remove stock correctly as per the MovementsTable

I would like to develop this further so that when the buttons are pressed on the Form the process would be-



Look at [ItemsTable].[StartingStock]
Subtract or add stock based on the information inputted into the Form
Return the balance from what was entered on the form back into [ItemsTable.][CurrentStock]

[ano]

I have created a basic database that will remove and add stock (see attached)

you always count in text?

The MovementsForm has buttons that will look at the queries (macro attached to the buttons) and remove stock correctly as per the MovementsTable

you tell it works but i not see my numbers back any where.
btw calculations u not save in tabels

Return the balance from what was entered on the form back into [ItemsTable.][CurrentStock]

you not do an execute if the values are not all valid

[Welshgasman]

I do not believe you need a starting stock.
Add a date to your movements table plus anything else you might need, like who added/edited any entry?
Then it is a case of summing for a particular item.
You already have the negatives and positives for out and in.

[moke123]

Take a look at allen browne's article.

http://allenbrowne.com/AppInventory.html

[NoellaG]

If you want a stock application you will always need to have a stock table, ask any logistics engineer, so min tables are
- Items (or Products/Articles)
- Movements (In/Out/Corrections, ...)
- Stock: current stock qty
I see some discussions here for not needing a stock table, but only on Access forums, so I wouldn't know why Access would work in another way.
If you have a stock table, you can use macro's with action queries that keep the current stock actual/

[Welshgasman]

My thoughts were, you just have a positive transaction for the stock.
If you wish to hold that in a seperate table, so be it ?