I am new to Access, so bear with me. I think this is possible, but I
am not sure how to get this going.
I am trying to develop a database that will allow me to create a
manufacturing schedule based on our customer demand and our capacity in
minutes. I have created a table (Demand) that will take our customer
demand, blow through our Bill of Materials and will leave me with a set
of demand that gives me a production work center, the item that I need
to produce, the quantity, and the per unit time to produce the product.
Essentially the table looks like this:
Work_Center
Order
Line
Seq
Item
Due_Date
Qty
Unit_Prod_Time (in minutes)
I have another table (Capacity) that will show the available production
time per day for each work center. The table looks like this:
Production_Date
Work_Center
Capacity (in minutes)
I want to be able to create a new table (I am guessing that I need to
do this through code) that will loop through each line of my demand
data, calculate the total time needed to produce, and compare that to
the capacity for that work center and for the next day available. If
there is enough capacity available in the day to do that order, I want
a line written into that new table that will assign the given
production date to the line of demand. I need to keep a running total
of scheduled capacity vs. the actual capacity so when a line does not
fit, I can assign it to the next date.
To add a little more complexity to it, if I have already scheduled 100
of 400 available minutes of capacity and my next demand line requires
350 minutes for 35 pieces, I want to be able to split the demand line
to assign 30 pieces due today and the remaining 5 to be due the next
day.
I can do this using Excel fairly easily, but I would like to keep the
application within Access if at all possible.
I am guessing that I need to use a module and create a new table, but
am not real sure how to get started. Thanks in advance for any help.