Subtracting stock numbers with a query

ewan97

Registered User.
Local time
Today, 08:51
Joined
Nov 17, 2014
Messages
27
I'm creating a sales system that allows customers to place orders using a customer account. when a customer completes an order the number items in the order needs to be subtracted from the stock numbers of each of those items stored in the system.
This is for a school project and to meet the criteria this has to be done with a query, problem is I have no idea how to do this, honestly queries scare me.

What I have so far is zipped and attached,
The way my system works should be easy enough to understand, there are four linked tables that store information on customers, products and orders. What I'm really struggling to understand is how to take the values in the records in the Invoice-Product table and take them away from the stock numbers for the products in the products table. But only new orders should take away from the stock number when a new order is placed.
If possible I also need to be able to add stock numbers for example if a delivery comes in, and if it's similar enough could that also be done here? Or else I could ask for help on that in a different thread.
 

Attachments

Typically you don't store the stock level, you always calculate it from your orders.

A similar problem is someone's age, which is a moving target because it changes with the passage of time. So, some data points we don't store, we always calculate them from other fixed reference points. In this case we use 1) date of birth, and 2) the date for which we want to calculate the age.

Inventory level is a thing like this. Don't store it, always calculate it from your orders. That way you can calculate past and future stock levels too, by only summing the orders up to a certain date.
 
ewan,

I notice you do not have an Order table. But you mention Order??
Usually there would be a Customer makes an Order consisting of 1 or more Products.
It is not a good idea to get the Price for the OrderedItem from the Product table. If you ever change the Price of an Item, an in real life you will, all of your historic data will change. That is when you make a change to Price in the Product table, all those prices on existing Orders/Invoices will also change.
see this link for more info

Stock/inventory management is discussed in several forums. It is not a trivial subject. The best practice for managing stock is to use a query. Items/Products received by you are + (positive), and all Items/Products sold are considered negative. So, at any point in time, your stock is Starting Quantity + Received - Sold ; and is calculated when needed.
See this article for more info

Seems there may be some other tables to consider. There are videos and tutorials listed here that may be helpful to you.
Good luck with your project.
 
Oh man, looking at all those links with all that information on how to properly make a system makes me realise just how broken and unorthodox mine is.
In my school we are expected to go from nothing to making an entire system and document it in a very short period of time. The way I made my system was by basing it off one of the examples we were given.
From what I can tell my finished system can be as backward and botched as possible but as long as it just barely works I'll get the marks.
Sorry for the lengthy sob story but if there is any way to make the system do what I wanted with the stock numbers using a query I'll take it.

And to answer the question anything to do with orders is stored in the tables, Invoice and Invoice-Product. Invoice product contains all the items in the order and their quantities, while everything else about the order is stored in the Invoice table e.g. customerID, date.
 
There are free data models here which may give you some ideas. These models are generic and don't deal with your specific case. They are intended to show how some things relate to each other, but not all entities are relevant to individual situations. This one deals with Customers, Order, Invoices.

The links are only meant for further info and reference -- use them as you wish.
 

Users who are viewing this thread

Back
Top Bottom