Apr 1 2006, 03:52 AM
Ok im a bit of an access newbie but im trying to create an advanced query liking two tables together, but what i need to happen is the information to be stored in a table when i have entered it.
However i essentially want the query to delete some information form another part of the database at the same time. Is a query best to use in the sanario and if not what is and how would i go about developing such a solution? Ill post the database up to see if anyone can help me out its the "Stock" and "Order" tables I need linked together in some way or other to use a query and caculate a total price for the order.
So say the quantity ordered form the order table is multiplied by the price in the stock table and then the quantity orderd is taken form the quantity in stock.
Thank you for your time!
Apr 1 2006, 07:11 AM
So what you have is an Order database with an Inventory piece. If so, you are not going about this correctly. There is specfic process used for Inventory apps. In that process stock on hand is NOT stored, its calculated.
An inventory app centers around a transactions table that records all movement of stock in and out. That table would look like this:
TransactionsID (Primary Key Autonumber)
OrderID (Foreign Key)
This table would function as the line items for your orders. The TransTypeID would be taken from a lookup table like this
TransType (Sale, Order, Shrinkage, etc.)
You then calculate stock on hand by adding all the IN transactions and subtracting all the OUT transactions.
Apr 1 2006, 10:37 AM
Im not quite sure if i understand would this not create data redundancy because the unit cost details are already stored in the Product table and the quantity ordered in the Order tbl.
I cant quite understand what is being asked, if I were to the above how would i know what were in and out when caculating the amount in stock?
Thanks for your time
Apr 1 2006, 11:47 AM
OK, First, You would take quantity out of the Order table. It doesn't bel;ongf there, it belongs with the line items. Second, Yes there would be redundancy with the UnitPrice, but this is one of the few places where this is permissable. Prices change so you need to freeze the price at the time of the order. You can do this byt storing the price in the Transaction record. Or you can do it by maintaining a history of prices and using the Order date to get the appropriate price. The latter requires more complex setup. Third, the TransactionTypeID tells you what type of transaction it is. When entering transactions on the Order form you can default this to a Sales. You also enter transactions thru other forms and there you would select the transaction type.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here