Solved

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

Posted on 2007-12-06
11
495 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 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now