General Help and Advice

[blazy]

if anyone could help me id be greatful

Basically im creating a system for a small store, and im stuck i cant remember how to make the Stock Quantity from one table go down if i put a sale though in another table

For example i sell A computer which has a total of 20 Availiable how do i make it automatically go to 19 when i put a sale through anyone got any ideas?

How it would work is, id Have a sales Form which has the

Sale ID,
Customer ID,
Employee ID,
Product ID
Current Date

evertyime i put a sale though i will print a recipet with the information based on the Sale ID i choose but how do i make it go into my Product Table and remove -1 from the quantitiy based on a Sale i put though

 

[MarkK]

Quantity should not be in the product table. A product is a discrete thing which does not include quantity as an attribute. A product may have a colour, a weight and so on, but Quantity is a value I would expect to see in an order.
Typically you would not store a quantity anywhere, but rather you would subtract the amount you sold from the amount you bought. To the degree that your database models a reality, this is the reality:

QuantityOnHand = QuantityIn - QuantityOut

I would always calculate it from the most current data.
Cheers,
Mark

 

[blazy]

Im a bit confused how ive configured it atm is

Have a Quantity Table and put an IIF statment on a query which i will add to my form for an update to put

IF Quantity Field is below or equal to 5 then fill the Table with "LOW" else "Normal"

 

[blazy]

Problem with my method is how would i control the quantity im selling, since i cant have a Quantity out due to not knowing how much of that stock ive already sold and i would need to keep adding it every sale i have

E.g Sell 2 today then the next day if i sold 5 i would need to change the field to say 7 and so on which wouldnt be very productive

 

[MarkK]

What you do is create a Purchase Order in which you may purchase a Quantity of product. Then you create a Sales Order in which you may sell a Quantity of product.
Then you subtract the quantity sold in sales orders from the quantity purchased in purchase orders for the product in question, which gives you stock on hand for that product. This value is always subject to change and rather than manage changing it all the time, you just calculate it when you need it.
Is that clearer?
Cheers,
Mark

 

[blazy]

Alot Clearer

Only problem i have it is im trying to make it automatically update, so when it falls below say for example 10 it goes into Low on Stock then when its below 5 its Urgent, if this happens i want it to inform the user or print a recipet to reorder stock or something like that to make it automatic

Im just abit confused am i better off going for this route

Quantity_Sold
Quantity_Purchased
Quantity_Total = Subtracts Sold from Purchased to give me Total in stock

Stock_Level = Tells me if its Urgent or Normal to restock

Only problem with this is everytime i sell something i will have to always add + 1 to the Sold Field

so if i sell 5 Monitors in one day instead of having it add automatically everytime i sell it im going to have to change the value of the field by 1 everytime, is there anyway around this?

 

[DCrake]

Do you not have a transaction table that holds the information about sales? This should have the quantity in it. Use this to equate the balance in stock.

 

[blazy]

Do you not have a transaction table that holds the information about sales? This should have the quantity in it. Use this to equate the balance in stock.

I have a sales Table where it consists of

Product_ID
Barcode
Name
Model
Quantity(Think this is what your reffering to)
Price

in here basically id be able to find the total of the products and print an Invoice for the customer

 

[DCrake]

So a sum of the quantity grouped by product code will give you total sales. Then deduct this from the sum of the products purchased will give you the current stock level

 

[MarkK]

I don't think my posts have been understood. Maybe you'll have more luck David.
Cheers,
Mark

 

[blazy]

So a sum of the quantity grouped by product code will give you total sales. Then deduct this from the sum of the products purchased will give you the current stock level

So this would do the whole stock as one? im trying to get it so that i can do it individually

just cant work out how id be able to keep that field updated based on how much i sell of each product

i dont want an overall product count i want an indvidual one so when 1 product goes below 5 total quantitiy it reads Low and i can create a report to fish out all the "Low" stock levels and reorder them

 

[blazy]

Is there anyway i can upload it so u can take a look at my project its pretty small and could use some advice, currently got all the changes in Update queries not sure if i can make em automatic or not

 

[jdraw]

It would appear that you are in need of some basics to help get oriented.

There are a number of existing data models here, and some of the Product, Inventory, Order models could be worth reviewing -
http://www.databaseanswers.org/data_models/index.htm

Also, there are good tools and info here,
http://www.accessmvp.com/strive4peace/

There Inventory data base templates (functional and free) here
http://office.microsoft.com/en-us/t...ase-TC001018458.aspx?CTT=5&origin=HA001234159