Inventory Control

[Guest]

I an very new at microsoft access. I am trying to make an inventory control
page that will take the last entry date of on hand inventory and forcast how
much to order based on the total of upcomming sales. I have created imput
tables that input both on-hand product and products needed. I can't figure
out how to combine that data into almost a savings account of products. Any
help would be great.

 

[Guest]

Jim:

Do you have Min/Max numbers, stored anywhere? Where are you getting the
"Upcoming Sales" numbers from?

With that information, it should be easy to do what you want.

Sharkbyte

 

[Guest]

I have two forms & two tables: 1. Inventory Entry (Inputing On-Hand inventory
as well as Inventory In-Bound) 2. Material Requisition (where the sale has
already been made and is schedualed to be shipped out on a certain date, also
inputing what inventory is needed for the job.) As far as Min/Max numbers I
have no idea.
Jim

 

[Guest]

Jim:

Your structure is still a little confusing, but here is how I would try and
set things up:

tblInventory
ProductID (PK)
QtyOnHand
Min
Max
LeadTime

tblOrders
OrderID (PK)
ProductID (PK) (FK)
QtyOrdered
DateRequired

Simplistically you can calculate how much to order by using:
([QtyOrdered]-[QtyOnHand]). However, with the structure above, you have
created many more options.

You can set, and maintain, an inventory amount. Running your forecast will
give you an order amount, but reaching the [Min] quantity could also trigger
an ordering event.

When you use your forecast, use something like:
(([Max]-[QtyOnHand])+[QtyOrdered]).

You can also utilize your [DateRequired], Date(), and [LeadTime], to
determine when you need to run your forecast, and possibly move this function
to a more automated status.

Good luck.

Sharkbyte