If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Manufacturing Process Tracking
I am attempting to rework a database to track a manufacturing
processes. We generate a serial # for each part. There are 10 operations we would like to track. For each operation (10, 20, 30, etc.) we want to track date completed and the name of technician that completed. I am struggling to develop the correct structure. Currently everything is in 1 table with checkboxes for each process completion and then the 2 columns that correspond. I have been requested to keep the user interface the same. This means that in a from, for each serial # they see all the process listed along with a checkbox for status and then text boxes for date and name. Any input on how to design this correctly would be greatly appreciated. My initial thoughts were below tblSerialNumber SerialNumber (PK) Info1 Model # tblStatus SerialNumber (relationship to tblSerialNumber PK) Operation (text) Status (checkbox) CompletedDate OperatorName I believe that this will not work as well though because everytime I created a serial# I would have to create 10 records in the status table so the user could use the same interface (check the box when complete and fill out text boxes). This doesnt seem efficient. I will also be creating status reports based on totals ready for each operation, details of each Serial# at each OP, Etc. Thanks again! |
#2
|
|||
|
|||
Manufacturing Process Tracking
The first question is how you plan on using the data?
Will you be tracking trends of how long each process takes or which individual takes the longest for each process? You can have a macro/event to append the new records upon add a new serial number - efficient. -- KARL DEWEY Build a little - Test a little " wrote: I am attempting to rework a database to track a manufacturing processes. We generate a serial # for each part. There are 10 operations we would like to track. For each operation (10, 20, 30, etc.) we want to track date completed and the name of technician that completed. I am struggling to develop the correct structure. Currently everything is in 1 table with checkboxes for each process completion and then the 2 columns that correspond. I have been requested to keep the user interface the same. This means that in a from, for each serial # they see all the process listed along with a checkbox for status and then text boxes for date and name. Any input on how to design this correctly would be greatly appreciated. My initial thoughts were below tblSerialNumber SerialNumber (PK) Info1 Model # tblStatus SerialNumber (relationship to tblSerialNumber PK) Operation (text) Status (checkbox) CompletedDate OperatorName I believe that this will not work as well though because everytime I created a serial# I would have to create 10 records in the status table so the user could use the same interface (check the box when complete and fill out text boxes). This doesnt seem efficient. I will also be creating status reports based on totals ready for each operation, details of each Serial# at each OP, Etc. Thanks again! |
#3
|
|||
|
|||
Manufacturing Process Tracking
Inserting the 10 rows into tblStatus is no problem; it merely requires the
execution of a simple SQL statement. But first you need a tblOpertaions table with 10 rows, with a column Operation as its primary key containing the 10 operations as text values, and a column OperationNumber containing values 100 to 1000 in multiples of 100 in the order you want the operations to be sorted on a form. By using multiples of 100 you can easily insert more operations if necessary without having to renumber the other rows. Relate this to tblStatus on Operation and enforce referential integrity and cascade updates. For your form, which must be in single form view, base it on the following query: SELECT * FROM tblSerialNumber ORDER BY SerialNumber; and include in it a continuous view subform based on the following query: SELECT tblStatus.* FROM tblStatus INNER JOIN tblOperations ON tblSerialNumber.Operation = tblOperations.Operation ORDER BY OperationNumber; Link the subform to the parent form on SerialNumber. In the AfterInsert event procedure of the parent form execute the following code: Dim cmd As ADODB.Command Dim strSQL As String Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText strSQL = "INSERT INTO tblStatus (SerialNumber,Operation) " & _ "SELECT " & Me.SerialNumber & ", Operation " & _ "FROM tblOperations" cmd.CommandText = strSQL cmd.Execute Me.sfcStatus.Requery where sfcStatus is the name of the subform control, i.e. the control which houses the subform, not its underlying form object (unless both have the same name of course). When you add a new serial number record in the parent form the SQL statement will insert ten rows into tblStatus and requery the subform to show the rows. The above assumes SerialNumber is a number data type. If its text amend the relevant line of code to: "SELECT """ & Me.SerialNumber & """, Operation " & _ to wrap the value in quotes characters. BTW 'checkbox' is not a data type, but a type of control. The data type is called Yes/No in Access, though a more technically appropriate term is Boolean. Ken Sheridan Stafford, England " wrote: I am attempting to rework a database to track a manufacturing processes. We generate a serial # for each part. There are 10 operations we would like to track. For each operation (10, 20, 30, etc.) we want to track date completed and the name of technician that completed. I am struggling to develop the correct structure. Currently everything is in 1 table with checkboxes for each process completion and then the 2 columns that correspond. I have been requested to keep the user interface the same. This means that in a from, for each serial # they see all the process listed along with a checkbox for status and then text boxes for date and name. Any input on how to design this correctly would be greatly appreciated. My initial thoughts were below tblSerialNumber SerialNumber (PK) Info1 Model # tblStatus SerialNumber (relationship to tblSerialNumber PK) Operation (text) Status (checkbox) CompletedDate OperatorName I believe that this will not work as well though because everytime I created a serial# I would have to create 10 records in the status table so the user could use the same interface (check the box when complete and fill out text boxes). This doesnt seem efficient. I will also be creating status reports based on totals ready for each operation, details of each Serial# at each OP, Etc. Thanks again! |
#4
|
|||
|
|||
Manufacturing Process Tracking
wrote:
the name of technician that completed. You should also use a table for the tech name with a foreign key in the operations table. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#5
|
|||
|
|||
Manufacturing Process Tracking
Thanks for the help. I am working on getting this setup. When
finished I will post so it can be helpful to others that may have same issue in the future. Karl - the part is a low volume production where each OP may take several days. I need to be able to track how many parts are available at each OP in order to balance manpower. We also use it to provide traceability of when each operation was completed and by whom. Ken - Thanks for the code. I did get one error on the SELECT tblStatus.* FROM tblStatus INNER JOIN tblOperations ON tblSerialNumber.Operation = tblOperations.Operation ORDER BY OperationNumber; In the 3rd line there is tblSerialNumber.Operation This would not work so I changed it to tblStatus.Operation. If I already have these two linked is there any harm in duplication? Again, thanks for the help. I once knew just enough to get myself into trouble but didnt use it for a while and now I have to get back on the horse. Maybe someday I will be a guru like you fellas! Daniel |
Thread Tools | |
Display Modes | |
|
|