Append Query

[JakeMann41]

Hi Guys

I've written an append query that updates a table from another table based on what item I want.

However when i run it it is duplicating the number of entries on the new table. What have I done to cause that?

[Welshgasman]

That is what an Append query does?, append new records to a table?

Perhaps you want an Update query once a record has been added to that table.?

[ano]

1st) stock is summation from buy/sell not a field in the database
2nd) transaction date belongs to buy/sell not to item
3rd) without criteria all items are processed

[ArviLaanemets]

A normal way to keep the stock of items in database is:
tblItems: ItemId, ItemName, ItemType,ItemUnit ... (contains a general and permanent info about every item);
tblItemTransactions: TransactionID, TransactionDate, StorageID, TransactionTypeID, TransactionAmount... (Contains the info about movements of items in certain location/storage. In case all movements are in same storage, StorageID can be dropped. TransactionTypeID determines, what kind of transaction the current one was, and was it incoming or outgoing one.);
tblTransactionTypes: TransactionTypeID, TransactionType, TransactionDirection (TransactionDirection will be 1 for incoming transactions, and -1 for outgoing transactions).

The stock of any item at any date can be calculated whenever it is needed as sum of (ItemAmount*TransactionDirection) from all transactions earlier or equal of query date. There is no need to save it anywhere (and doing so may be a source of problems for you).

In case the cumber of transactions is big, you may need to have e.g. yearly inventories, like
tblItemInventory: InventoryID, InventoryDate, ItemID, InventoryAmount
Then stock of any item at any date can be calculated whenever it is needed as InventoryAmount + sum of (ItemAmount*TransactionDirection) from all transactions later as inventory date, and earlier or equal of query date.

[NoellaG]

In every well designed stock database you do need a stock table, every logistic engineer will tell you this and every logistics application I encountered works this way. Calculating the + and - never does the job as it should, I don't know why only in Access this should be different.

[ano]

@noellag
every database should not save (re)calculated fields thats not database minded , what u see in forms does not need to be the same as the tables.
the example of extra field +/- is also not the efficient way <zero is out >zero is in (sell/buy) to check in form calculate the stock before change (get_focus) and after change (after_update) calculate again and display "buy"/"sell" depending on result diff old/new stock so the one see confirmd its action.
more important the table should not allow update only insert . if interesting a field with broken/stolen/misplaced .

[ano]

why u have 2 tables with field currentstock? Items is only basic item info without history , history belong in separated table with date.
startingstock is always zero so useless
currentstock is sum(currentstock) from currentstock table and that miss the date. yes "out" is negative, "in" is positive
and item in currentstock should match id from item table

[NoellaG]

@ano
the stock table is the heart of your application, you don't want it to be calculated on each access and from my engineering degree + 30 years of experience: starting stock is not always zero.
Table design is important but should take into account reality.
Avril's post is a very good start. But you always need a stock table: you need to know very fast if you have enough stock for orders, if you have enough room to stow away new input, if you need to order new items, create picking lists, ect ...
In most cases you have the movement tables + queue tables. This means orders come in through email or IDE and you haven't checked yet if you have enough stock, look upon it as reservations.
If you have to calculate the stock each time for every action you can run into trouble very fast.
If after every movement the stock is set up to date through an update query (VBA or macro result in the same on the database layer) you're good.

[ano]

your reason is fake your database design will fail and fail again and again and you have no history to adjust your picking list.
it is extra bad design because u have 2 same field in your database and more over you connect on text item

[ano]

some optimizing points.


if currentstock is getting very large , so sum becum slow, then add a broken with the stock , add 1 to the field that keep track of the recent stock and add misplaced with the stock , so you only sum the recent stock (highest)this is during the new order procedure.


currentstock : id(autonumber),item(number),date(today),recent(num ber),(-)stock(number),reason(number)
item : id(autonumber),item (text)
minmax : id(autonumber) ,item(number) ,kind (number), stock (number)
kind : id(autonumber), location(number),kind (text) {min (need order),max (max-stock to order)}
location : id (autonumber) , location(text) {shelf,warehouse}
reason: id(autonumber),reason(text) {misplaced,broken,lost,bought,sold}


select item.item,sum(stock) from item,currentstock where item.id=currentstock.item and currentstock.recent=(select max(recent) from currentstock)