Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-12-06
11
Medium Priority
?
523 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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