Question Stock control DB with reordering level...

[OrionTC]

Hi i am looking to create a new stock control DB and i want to be able to create a report which flags up any stock under the reorder level and keep a track of when the media has been used...

the stock table contains the following:

ClientName
CD
DVD
Floppy

for example.
when a client orders say 10 cd's i want to be able to add them into the database with a description and keep a track of when/where these cd's are used. i.e. 10 cd's added to stock, and on the 9th feb 2 were used, that now leaves 8, and when that amount reaches a certain level determined by me then flag it up under a reorder stock report

the only complication is different clients have different stock levels. so ClientA might need a minimum of 5 CD's onsite while ClientB might need 0 CD's....

any help would be greatly appreciated!

 

[HiTechCoach]

It is possible to do what you want with Access.

Inventory control is probably one of the most difficult tasks to program in any software language. Access and VBA included.

For a good basic overview with Access see: Inventory Control: Quantity on Hand

What is sounds like you need is multiple warehouse inventory contyrol. From what you have described I would treat each customer as a separate warehouse.

 

[OrionTC]

thanks, ill give it a look.

anymore advice would be appreciated.

 

[HiTechCoach]

Have Realistic Expectations:
You should expect this to take at least 4 months to a year to develop if you work on it a minimum 30 hours a week. You might be able to develop it quicker if you have very strong programming skills and lots of inventory control experience.

IMHO, the most important tip for inventory control: Use transactions to adjust any stock levels. Do not modify values in records. Create new records and sum them up to get the total. I find this really does help speed up the development process.

 

[OrionTC]

maybe im in way over my head, this was supposed to be a quick side project to get away from a spreadsheet used currently to keep track of stock, but like i brought up its almost useless since you have no audit trail and needs to be right 100% of the time to work.

i then thought about making something a little easier with a copy/delete query into another table when a record is edited? would this be achievable?

i could create a quantity and quantity used expression to calculate the current stock, then on each successful record edit it would have to copy the old record into an audit table to keep a track of what the stock was before.

there doesn't have to be any fancy multi user environment, it's not for a shop or anything, simply for a small team to keep track of media used.

the only problem i have is putting all this together, i created a relationship between the stock table and the stock used table via a primary key but then i couldnt get the 2 tables to talk to each other when quantity was used....

sorry to be a pain!

 

[jdraw]

There are some data models and business rules here

http://www.databaseanswers.org/data_models/inventory_control_for_retail/facts.htm

Getting a good model and a validated set of up to date business rules is KEY.

But, as others have said, Inventory Control is not a trivial subject.

 

[HiTechCoach]

maybe im in way over my head, this was supposed to be a quick side project to get away from a spreadsheet used currently to keep track of stock, but like i brought up its almost useless since you have no audit trail and needs to be right 100% of the time to work.

i then thought about making something a little easier with a copy/delete query into another table when a record is edited? would this be achievable?

I can't recommend doing that.

i could create a quantity and quantity used expression to calculate the current stock, then on each successful record edit it would have to copy the old record into an audit table to keep a track of what the stock was before.

If you as using a Audit trail table and have to copy the old record into an audit table to keep a track of what the stock was before then you must not be using transactions. Sounds like this is editing a field's value, not properly using transactions.

With Inventory Control using transactions an Audit trail happens automatically. The transactions are the Audit trail.

What you are doing might seam simpler than creating transactions, but it has been my experience that it really is not.

The best way to insure 100% accuracy is to use transactions. I know of know way to shortcut this. After 30+ years if studying Accounting and Inventory Control there is still no better way that using transactions. This method has proved to be the best way for over 2000 years. Yes I did mean two thousand (2000) years.

there doesn't have to be any fancy multi user environment, it's not for a shop or anything, simply for a small team to keep track of media used.

Team usually mean more that one so it sounds like multi user.

Good Accounting principles and Inventory control really does not depend on the number of users.

the only problem i have is putting all this together, i created a relationship between the stock table and the stock used table via a primary key but then i couldnt get the 2 tables to talk to each other when quantity was used....

I would recommend that you post a sample datasbe with your tables. Is that possible?

 

[OrionTC]

i changed the sensitive data and changed each client to client1-2-3 etc.

ok so firstly we have tblstocklevel/frmstocklevel in conjunction with tblcomments/frmcomments. we open the form stock level and select the client, when we use an item of media we adjust the amount in the correct field, ie DVD's from 5 to 3. Then the update field makes the changes to the record, during this we would add into the comments field under the specific client what was used, "2 DVD used for blabla". The problem i have with this one is notifying when a certain media type are under stocked, and as mentioned before each client has a different stock threshold, client 1 might need 5 DVD's while client 2 may need none at all....so i couldnt create a global stock threshold since the clients that have 0 tapes on a certain media type would flag up. (maybe this is due to my table creation but at the time it worked for what i wanted it to).

the second issue i have comes from the tblcagestock and tblcagestockused. this is one i mentioned with the expression. the cage stock is a different physical location used to keep specific client media rather than 20 CD's they are now 20 CD's for server123, so they are more specific hence why they are kept in a different physical location. currently we use a spreadsheet for this but as i said there is no audit trial with that so i have tried to impliment this into the database, so ideally i would enter "20 new DVD's for client 1, server 12345" and if i used 5 DVD's the stock would now count 15 with a trial showing i used 5 on this day. the other slight problem comes from prelabelled media, some of the media has barcoded labels for example L0010 - L0020 so in the description would be L0010 - L0020 and if 5 were used it would now read L0015-L0020 since i have used the first 5....

apologies if this is confusing i have tried to explain the best i can.

if this would be extremely time consuming we may have to make do with the spreadsheet and employ a little more manual labour into the spreadsheet

i should also add, no multi user environment is required, yes we have a team(2-3 people) but only one person is ever tasked with adjusting the information at any one time.

 

[jdraw]

Not everyone has acc 2007. Can you send a copy as mdb?

 

[OrionTC]

Not everyone has acc 2007. Can you send a copy as mdb?

absolutely. done.

 

[HiTechCoach]

Great! I have got it downloaded.

 

[OrionTC]

glad you have it, any ideas on what i was doing wrong? (probably a lot)

 

[HiTechCoach]

It looks like you have created a spreadsheet in a relational database.

Until you properly normalize your table like a relations database you will only find this to be very difficult.

With a proper database design, the creation of your forms and reports will get a lot easier. Very little VBA code required.

I will address one item at a time.

ok so firstly we have tblstocklevel/frmstocklevel in conjunction with tblcomments/frmcomments. we open the form stock level and select the client, when we use an item of media we adjust the amount in the correct field, ie DVD's from 5 to 3. Then the update field makes the changes to the record, during this we would add into the comments field under the specific client what was used, "2 DVD used for blabla".

If you would use transaction records then one entry would do event described above.

.... when we use an item of media we adjust the amount in the correct field, ie DVD's from 5 to 3 ...

This is not good/proper accounting procedures. This open up so much room for errors, fraud, etc. I don't think it would pass an audit.

Here is an example:
If you use two DVD then you would create a transaction record like this:

Transaction Date: 11/23/2010
Item: DVD
Quantity: -2

To get the data into inventory you would have:

Transaction date: 11/1/2010
Item DVD
Quantity: +5


To get the current stock level you would sun the transactions ( 5 - 2 = 3)

This way you have a build in audit trial.


Looking at your design, what you are calling Comments is almost what you need for the transaction.

 

[HiTechCoach]

I will build you a database with some tables to give you an idea.

I will post it shortly.

 

[HiTechCoach]

Here are the basic tables you could use to handle what you need.

With this design you can easily add new items without having to make any table design change like the way you have it now.

I also included a way to track the desired stock level by item for each customer.

 

[OrionTC]

thankyou very much i will take a look now

 

[OrionTC]

ive had a look and i get what you mean by the table design and i agree it looks a lot better im just very confused how i am supposed to turn the ID's into useful information.

for example:

tblcustomeritems is various ID's but to the user doesnt mean a lot. is it possible to have this information viewed by like ID1 (client amazon) ID2 (client dell).

 

[HiTechCoach]

ive had a look and i get what you mean by the table design and i agree it looks a lot better im just very confused how i am supposed to turn the ID's into useful information.

for example:

tblcustomeritems is various ID's but to the user doesnt mean a lot. is it possible to have this information viewed by like ID1 (client amazon) ID2 (client dell).

The user really never needs to see the autonumber primary keys fields. I keep them hidden from the user.

On the forms you would use a combo box to diplay a field that is meaningful to the user.

 

[jdraw]

Boyd,
Can you make an attachment in mdb format?
Thanks.

 

[HiTechCoach]

Boyd,
Can you make an attachment in mdb format?
Thanks.

Sure thing.

I have attached the original one and the one I created in 2000 format.