Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-12-06
11
Medium Priority
?
530 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
  • 5
  • 4
11 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

963 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