Solved

Provide product stock numbers and and sales re-order level activation.

Posted on 2007-12-06
11
507 Views
Last Modified: 2008-05-09
Hi all,
I'm developing an E-commerce solution website for Medium based company. I need to create Administrative functions as well. One of requirement from my client is to "Provide product stock numbers and and sales re-order level activation" in the Administrative pages.

What I'm asking is?...In order to create "Product stock numbers" and "Sales re-order level activation functions"..I've created only Product entity in my database related to this issue. (Product table)
I didn't create Stock entity seprately(stock table) in my database. I've thought product is enough.
So that I can calculate stock numbers by deducting the sold items from the product table and the sold items is equal to re-order level activation. so It automatically comes in. But now I think It's more process oriented and seems to be a difficult task. I'm not an expert in ASP too. so

Can u please explain an alternative or easy way to - "Provide product stock numbers and and sales re-order level activation in developing an E-commerce site."?

Thanks in Advance for ur help
Regards
www.teacostea.com
0
Comment
Question by:sivakugan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 20425851
sivakugan,

Your question is too broad, in both it's scope and complexity.
(it is basically two questions, see here: http://www.experts-exchange.com/help.jsp#hi107)


I will give you some advice on creating the: "product stock numbers and sales re-order level activation", portion of this question.

Open Access and create a new database using the "Inventory Control" template.
Open the Products form, and view the relationships between the tables, to get an idea of what it is going to take, to create this.

See if you can get this going first.


*Then* post another Q in the Miscellaneous Web Development, and/or E-Commerce Topic Areas inquiring about the Web/E-commerce portion of this.

Hope this helps

JeffCoachman
0
 

Author Comment

by:sivakugan
ID: 20426420
ok nice one boaq,....I'll do that...If u want, I can put these separately in another question..
but I need some time to do urs task. I'll post second question very soon.
bye for now.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20428632
No Problem.

Good Luck!
:)

JeffCoachman
0
Stressed Out?

Watch some penguins on the livecam!

 

Author Comment

by:sivakugan
ID: 20498014
Boaq,
I've went through as u said. It's quite nice to see the functionalities I need. In Product Summary Report it shows everything I need. I want to implement product stock numbers and sales re-order level activation.
It shows more than that.

I need to know how they implemented the re-order level activation?...I'll post the next question as a provide product numbers. so in this question please specify about re-order level acivation.

Thanks for ur help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20498319
sivakugan,

Did you view the form in design view, and examine the Control sources of the textboxes?
There is some info there.

There is also a fair amount of VBA Code that makes this work as well.
Check that out too.

You will have to try and adapt this to work in your database.

You can also download another sample:
http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT101426031033&av=ZAC000

Good Luck

JeffCoachman
0
 

Author Comment

by:sivakugan
ID: 20502763
Hello Boaq,

I need only one help from u to understand this issue.

In the Inventory control Template, There is a table called Inventory Transactions. What's its for?
Can u please explain about this?...I don't quite get this table.

Thanks in Advance
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20503410
sivakugan,

Great Question.

The Inventory Transactions table is what is known as a "Junction Table".

Since one Product can be in many Purchase Orders, AND one Purchase Order can contain many Products.
This table joins the two.
It contains the Primary keys from both tables and sets them as Foreign keys.
This type of relationship is known as a Many-To-Many relationship.

You can think of thae table as a "Purchase Order Details" table (shows each Line Item).

From a Inventory Management standpoint, every time a product is put into Inventory, it typically comes from a PO. This table shows the Details of that PO.

If you look at the Purchase Order form you will see an example of this.

JeffCoachman
0
 

Author Comment

by:sivakugan
ID: 20505651
Hi Boaq,

Thanks for replying. I'm looking around Inventory control and Order management system. both have similar tables. In order management , the table called "Order-Details" is same as in ,Inventory control the table called "Inventory Transactions". both are for break up the many to many relationship.

The issue is, In inventory control system that table has additional fields such as Units Ordered, Units Received, Units Sold, Units Shrinkage.

Do I need these additional fields to calculate the Re-order level?.......It's because In inventory control, The Product table has the field called "Re-order level" straight away.....

I thought I can calculate the Re-order level  in this way, which is Re-order level = sold items
so what ever I've sold it comes as a Re-order level?......Is it correct?...Am I Stupid?....

I would be grateful, If u can reply
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20507425
sivakugan,

<Do I need these additional fields to calculate the Re-order level?>
That depends on how you *need* to calculate Reorder Level in your application.

Some companies calculate the reorder level by simply taking the amount of Items Physically in stock and comparing that number to the reorder level.
Others take into account how many items are already Ordered.

For example:
Let's say your reorder level is 10.
You see that you have 9 physical items in inventory.
Time to order 10 more, right?
Well, not exactly. You might know that you will not be building any parts that need that item for another 6 months.
Your company might not have the money to buy any more.
This way of looking at it also does not take into account if you already have 100 items on order. (Because you are only looking at the bin with 9 items in it, and you know you need 10.)

Another way to look at it is:
Let's say your reorder level is 10.
You see that you have 9 physical items in inventory.
Time to order 10 more, right?
Not really, your reorder "level" is 10, but your reorder "Quantity" is 150!

A third scenario:
Let's say your reorder level is 10.
Your reorder Quantity is 150
You see that you have 9 physical items in inventory.
Time to order 150 items correct?

Maybe not.
Because you know that in right after the Holiday Season, you get several huge orders.
You may need to order 325.

So as you can see, your companies Business Rules will set how you should determine the reorder level.

So in some cases you might have to consider additional fields such as Units Ordered, Units Received, Units Sold, and Units Shrinkage.


The Microsoft Templates are only rough guides, not "Drop In" solutions.

Whether you use these additional fields to calculate reorder level or not, you still need them in the junction table.

JeffCoachman
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question