Stored Procedure for Access

[KRSharp]

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.

[Ray Watson]

This seems a quite straightforward though possibly time consuming task.
However, I dont know why you would want to create a new table for the
results.
I get the impression you want to make many tables with different names for
each SET of results.
Many new Access users think they will do it like Excel, where you have a
workesheet called
results121106.x ls, results131106.x ls, etc

Generally, the best solution is to have a blank table with all the fields
required for the calculations, do the calculation, and put the results in a
new record.

To flag what day the results are for, have a field called DateCalculated, or
BatchNumber or any other type of dimension description, so you can extract
data for reports based on when the results were calculated or when they are
to be used. You can even erase all the records afterwards if you want.

The procedure to do this can be as simple as an append query or as flexible
as a VB procedure that opens all the tables and writes the new records. My
bet is you will need either a number of queries, but I think a VB function
or procedure would give you more flexibility.

Do a search in this group on opening tables with code.

A rough example of code is included below -

dim DAOWork_Center as dao.recordset
dim cdb as dao.database
set cdb = currentdb
set DAOWork_Center = cbd.openrecords et("select * from Work_Center where
Due_Date >= '1/12/2006',dbopensna phot)
do while not DAOWork_Center. eof

' Look up other tables, calculate values, put them in the results table
here

cdb.execute "insert into Resultstable fields ......... etc"

DAOWork_Center. movenext
loop
I hope this gives you some ideas

Cheers


<KR*****@gmail. comwrote in message
news:11******** **************@ j44g2000cwa.goo glegroups.com.. .

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.