Stock Control Database

[Aidanb]

Hi All,

I am looking for some help on creating a stock control database. I have looked at the templates online and cant find one that helps me create what i want.

I need the system to produce a consumtion report off this is the opening stock figure(this is the previous weeks closing stock) plus the purchase stock figure minus the closing stock figure (eg; tins of baked beans cost us a £1 per tin we had at the end of last week 3 tins we bought in the 3 more tins creating our total tins of beans to hand as 6 we do the stock take and find out we have 2 tins of beans left this means we have used 4 giving us a consumption figure of £4).

this would then be printed off in a report with what our opening stock figure is the purchase figure is and what the closing stock figure is.

i am struggling to pull all the products from the product table through to the closing stock form.

is there anyway for this to be done? any help would be greatly appriciated.

Regards

Aidan

attached the stock database i have done so far.

 

[spoole]

Hi Aiden,

Ive looked at your DB and have a few suggestions.

Are you using transaction types; I.E, SA=Sale, PO=receipt, DA=Damaged Stock etc?

You seem to be holding the product qty in the product table. Really this should only be used for product details. You need a seperate table for transactions. Then you can have by date all transactions for a product. So on the 1/10/09 you sold 3 tins of beans. This will be in the table as Beans (product transacted) TransQty (number of tins) and TransType (Type of transaction) and TransDate (date transaction happened)

Transaction types are very importand E.G a transaction type of "SALE" (SA) will be a negative number, so if i have 3 tins in stock and perform a SA transaction for 3 tins the number recorded should be -3 in the transaction table. If you now group the transaction table in a query by product and sort by TransDate then sum the Qty field then beans will come out at 0. Which you could also use as an opertunity to show a message box saying "order more beans" etc.

Do you see where im going?

 

[Aidanb]

Hi Spoole,

Thanks for the info have you got a sample of this just strugaling a bit to understand what you mean also i am having problems bringing all the products in to the form so all i need to do is add the amount of stock we have in.

as we need to do a closing stock at the end of the week this will help us to identify what has gone missing as we work with food its not as easy as just working out what we should have on hand we need an actual on hand stock.

i have attached a spreadsheet which does the job but want to convert it to a Database.

Thanks

Aidan

 

[spoole]

Hi Aidan ,

Ill see if I can knock one up for you. It may take a coupl eof hours though as im at work.

 

[spoole]

Hi Aiden,

After looking into this some more it appears that unless you want to go the whole hog

http://office.microsoft.com/en-us/templates/TC010184581033.aspx

and have an all singing inventory management database then excel does seem to be the format to do what you want to do.

I have had a go at creating something myself but kept coming up with the same ways to do it, and that involves pretty much what is in the link.

 

[gemma-the-husky]

try this MS demo of an inventory database

I have used this as the basis for a practical inventory database


http://office.microsoft.com/en-us/templates/TC010184581033.aspx?ofcresset=1


but ... i cant work out how to download and save it!

 

[Aidanb]

Thanks Dave and Spoole,

The Sample is not what i am after i want to be able to just have a list of products and just type in the amount counted like the spreadsheet.

is there any way that you can all the products from one table to a sub form say based on the week start date?

if there is no way then i will stay with the spreadsheet just have seen systems that can do it from my time in pubs but was on a dos type system.

Aidan

 

[SteveF]

Thanks Dave and Spoole,

The Sample is not what i am after i want to be able to just have a list of products and just type in the amount counted like the spreadsheet.

is there any way that you can all the products from one table to a sub form say based on the week start date?

if there is no way then i will stay with the spreadsheet just have seen systems that can do it from my time in pubs but was on a dos type system.

Aidan

I can't answer this from a database point of view because I'm not a database genius.

From an understanding what you are getting at point of view though I can help. I wrote a stock control database specifically for bar and food stocktaking within pubs and restaurants.

I will PM you some contact details and am happy to send over a DB that will do the job for you.

On the database that you have started, all you are trying to do is produce consumption at cost figures so there is nothing overly complex about it.

I don't see the need for separate tables for closing stock and products. I used the one table for opening, purchases and closing stock. You then use an update query to push your closing stock across to become your opening stock and use the same query to re-set your purchases to zero.