Manufacturing Process Tracking

[danielwalden]

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!

 

[KARL DEWEY]

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.

 

[Ken Sheridan]

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

 

[Tony Toews [MVP]]

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/

 

[danielwalden]

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