I work for a manufacturing company and have been trying to figure out the best way to create a production schedule. The production schedule would contain a product name, order quantity, a production date(when the manufacturing/bottling process would start), and customer name. I can create a schedule with all of that information but i cannot figure out how I can split an order quantity between several days.
For example,
Customer places an order for 10,000 units on a single Purchase Order.
That 10,000 unit order needs to be split up because we cannot produce that in a single day. So the schedule would show something like this,
Total amount ordered: 10,000
(Scheduled for 100% completion in 5 week days @2000 units per day)
To provide db:
copy the db, remove confidential data, run compact & repair, zip w/Windows Compression. Scroll down and use Manage Attachments Manager to select File, Then Click Upload .
Place cursor on blank line and then Click the attachment Icon to select the file you have uploaded.
Click Submit Reply
If you have trouble then send me a zipped copy to me at:-
How I would set this up is as follows;
1) Have a table (ProductionLine) that holds your production line data. I am guessing you may already have this, but it would include values such as product, batch prep time, time per item during a run, and maximum quantity per batch.
2) I would create a table (RunSchedule) that is a child to both the Order and to your Production line. This would hold quantity for a run and date/time scheduled to process.
You could then query your RunSchedule to see what is being produced where.
When assigning RunSchedule entries you would first do a total query to see how many units are already scheduled for a given order and show how many remain.
The type of schedule i want to build would look like a rolling five schedule for weekdays only.
I have tried this with the cross tab table but could not distribute total quantity order over several days.
This is easy to do in excel but i cant figure it out in access.
Not sure if you've had a resolution yet, but how I would do something like this is as follows;
1) Set up a way to input the "Start Date" for your calendar. Normally this would default to today but should be able to be updated for future calendars.
2) I would make 5 identical queries each of which has product, schedule date, and quantity. The difference is each would be based on your "Start Date" with the first using that date as is but each of the additional queries adding a day to it.
3) I would then join the queries (similar to a cross tab) so that you have the product and each of the 5 quantities.
4) For the report you would use the same initial date to set the header and add to it for each additional date you are reporting on. You would then just put the matching quantity below.
This is ONE approach that is fairly modular. If you need to add columns you add additional duplicate queries then join them in. The one big advantage is that you can code a function to return "Next Date" that would allow you to skip days not scheduled for production.
You need to describe you data better, and provide better data. I cannot see how to build this from what you provided. In building a production schedule normally there is a list of orders to fulfill (items and quantities), list of available resources, and then the buisness rules (constraints and restraints). I do not see any of that.
Here is a previous one I demoed for someone that breaks production amounts by shift and date. Since each shift can only produce so much based on available workers and hours. I also do not work certain days.
So here are the resources (available hours per shift)
tbShift. So shift 1 for example has 126 available production hours.
The buisness rules are that items are produced in order A to M. There are 3 shifts a day. Work is done 5 days a week.
So I have code to loop the orders, and loop the shifts and apply the available shift hours to a product. If the order is not filled the remaining hours passed to next shift to fill. If the order is filled in a shift they move to the next product.
Code:
Public Sub CreatePlan(StartDay As Date)
Dim RS_Plan As DAO.Recordset
Dim RS_Shift As DAO.Recordset
Dim strSql As String
Dim RemainingProductionHours As Long 'Current hours left to produce the item
Dim AvailableHours As Long 'Hours available from the shift
Dim UsedHours As Long 'Hours used by the shift
Dim ProductName As String
Dim ProductionDate As Date
Dim DayCounter As Long
Dim ShiftName As String
Dim reccount As Long 'Number of shifts
'Get the Production plan in a recordset
strSql = "Select * from tblProductionPlan order by OrderProduction"
Set RS_Plan = CurrentDb.OpenRecordset(strSql)
'Get the shifts in a recordset
strSql = "Select * from tblShift order by shift"
Set RS_Shift = CurrentDb.OpenRecordset(strSql)
'Get the start values
ProductionDate = StartDay
AvailableHours = RS_Shift!TotalTimePerShift
ShiftName = RS_Shift!Shift
'Count the number of shift records
RS_Shift.MoveLast
RS_Shift.MoveFirst
reccount = RS_Shift.RecordCount
'Clear current schedule. Need to make this table
CurrentDb.Execute "delete * from tblProductionSchedule"
'Loop all products
Do While Not RS_Plan.EOF
ProductName = RS_Plan!ProductName
RemainingProductionHours = RS_Plan!Quantity * RS_Plan!ProdTimePerUnit
'For each product loop the shifts
Do
'The shift has remaining hours to complete the production
If AvailableHours >= RemainingProductionHours Then
UsedHours = RemainingProductionHours
AvailableHours = AvailableHours - RemainingProductionHours
RemainingProductionHours = 0
'The shift only has a portion needed to complete production
Else
UsedHours = AvailableHours
RemainingProductionHours = RemainingProductionHours - UsedHours
AvailableHours = 0
End If
'You used some hours from a shift so input into a table the product, shift, hours used, and the date
strSql = "Insert into tblProductionschedule (ProductName, ShiftName, ShiftHours, ProductionDate) values ('" & ProductName & "', '" & ShiftName & "', " & UsedHours & ", #" & ProductionDate & "#)"
Debug.Print strSql
CurrentDb.Execute strSql
'The shift has no more available hours so move to the next shift
If AvailableHours = 0 Then
'If You are at the last shift move to the first shift the next day
If RS_Shift.AbsolutePosition = reccount - 1 Then
RS_Shift.MoveFirst
'If the next day is saturday move to the next monday
'Delete this if you work weekends
If Weekday(ProductionDate) = vbFriday Then
ProductionDate = ProductionDate + 3
Else
ProductionDate = ProductionDate + 1
End If
Else
'You are not at the last shift so just move to the next shift
RS_Shift.MoveNext
End If
'Since you move to a new shift get the name and available shift hours
AvailableHours = RS_Shift!TotalTimePerShift
ShiftName = RS_Shift!Shift
End If
'Loop until the hours used equal the hours needed to produce it.
Loop Until RemainingProductionHours = 0
RS_Plan.MoveNext
Loop
End Sub
This build the Production Schedule. So as you can see product_A required 100 hours and was completely filled in first shift since they have 126 hours available. They had 26 hours to put against Product B, but shift 2 had to finish the remaining 89 hours.
I've not had a call to develop any thing to do with production scheduling. MajP's model would be what I would be aiming for but with a few extra factors provided.
An order to supply might contain more than one product. The ability to re-order priority of orders involving shorter delivery. The production time for a Shift could vary depending on use of overtime. Allowance could be made for actual production varying from expected production per Shift through say, absent workers or machine breakdowns.
Then there would be particular business rules. For example, a particular type of machine is required in the process of making particular products with a bottle neck problems.
Yes, this is definitely a very toy problem. But as I said you normally have requirements (things to build or accomplish), resources, and rules. Most of the assignment problems I work are optimizations, so it is not just assigning available resources but assigning the most optimum and efficient. That can get very complicated requiring 10s of thousands of lines of code or more.
You need to describe you data better, and provide better data. I cannot see how to build this from what you provided. In building a production schedule normally there is a list of orders to fulfill (items and quantities), list of available resources, and then the buisness rules (constraints and restraints). I do not see any of that.
Here is a previous one I demoed for someone that breaks production amounts by shift and date. Since each shift can only produce so much based on available workers and hours. I also do not work certain days.
........
Product_M[/CODE]If you want more help, please provide some meaningful data. However, this is conceptually what you need to do.
MajP, the Method you described is Brilliant! That is Basically what i am looking to do.
Steps Of Product scheduling:
1. Enter a customer's Product details with the QTY ordered using a Form.
2. Take that product and schedule the QTY for production
3. Have a simplistic way to split that qty over a desired amount of days.
4. Have the ability to go into the schedule and edit the date scheduled or remove the product from schedule.
5. Create a report to email to other production leaders.
I am making this schedule for a Nutraceutical Manufacturer(sports/dietary supps manufacturing). What they want is a way to schedule and update the "Bottling and Packaging" part of the manufacturing process. There are several others - Powder Blending, Capsule Filling, Tablet Pressing, Drink Mix filling and so on. Right now it would be a nightmare to schedule the entire manufacturing process and i am not proficient enough to make such a program in MS Access. (maybe one day though).
I have attached a file that i have made and that file is currently being used by the production manager. I am definetly going to follow your advise and make what you described as your version of a prod. schedule.
Good luck, and if you need help I may have other examples. Be advised as pointed out this was a super trivial example and these can get real hard in real life. Normally the determining the resources available at any given time and all the rules surrounding the assignment of resources can be very complicated.
I neglected to post what mike60smart suggested i do to solve this issue. Attached is a copy of the work he did. He coded some logic into the Customer Schedules form that splits an order qty by the number of days you input. I like this concept. Would there be a way to view every product scheduled in a centralized location that's not a table? That way i could see total production for each day, and, fill days that are empty. I am guessing that i should do a Query to the table that the schedules are stored in, but i wanted to ask for you all first.
So, what I am asking is this,
step 1: (this is already in the DB thanks be to mike60Smart)
1. input new order into production
2. schedule it on the spot
step 2: (this step is what my question is about)
1. Go to the "Schedule Management" page to view all products scheduled (not in the DB)
2. Adjust schedule according to production flow.
Anyways, I appreciate everyone's response to my question. I have three great ideas to work with thanks be to mike60smart, MajP, and Mark_. I want to keep this thread open for a few more days to see what anyone else may suggest. I think i have enough knowledge to make this program.
Two quick questions;
1) Do you have different production lines for different products?
2) Is your goal to have a something that, effectively, pushes back all future production for one line if you find you've had an issues with producing a given run?
