Database Layout for Work Instructions

[Jeroen3131]

Hello all,

I'm currently constructing a Work Instructions database to normalize all the Work Instructions related documents. Currently there are numerous documents with different layouts. I want to reduce that to one standard format.

I've already made a start on my database but during the process I'm reconsidering to change the (technical) layout of my database. Before continuing I would love to recieve some expert opinions. This is the part I already have: Work Instruction Database v5.0.accdb.zip. For those who don't have Access available I'll try to explain as much as possible.

First of all I'm going to explain the definitions I use:

Work Instructions
A Work Instruction is a Document/Report which consists of:

1. Front Page
2. Safety Sheet
3. Entry Quality Check
4. Multiple Work Steps
5. Final Quality Check
6. List of Common Faults

1. Front Page
The Front page is an automically generated/updated based on the data from the Work Steps.
Displayed on this page are:

• Editor Name
• Revision number (from the Work Instruction)
• Revision numbers for each Work Step
• Document Title
• Department name
• Opeartion Name (For instance: "Welding")
• Operation Number

2. Safety Sheet
A Safety Sheet informs the employee which safety measures and equipments should be used for the specific Work Instruction.

Here is the Form that i made for creating a Safety Sheet (unfinished):

3. Entry Quality Check
The purpose of the Entry Check is to reduce quality faults by showing employees how a product should and how it should not look when they recieve it. To keep the Instructions compact, I decided to include a max. of 3 GOOD and 3 BAD pictures.

4. Work Steps
Each Work Instruction consists of multiple Steps. In each Step the employee performs an action. Several parts and tools are used and must be documented in these Steps. A Work Step includes:

• DepartmentID
• Step Name
• Model Type
• Variant Type
• Operation
• A picture of the performed action
• English description of the action
• Dutch description of the action
• Partnumbers of used Small Parts (e.g. bolts,nuts etc.)
• Quantity of the Small parts used
• Small Picture of each used Small part
• Revision number for each step
• A max of 5 Icons which display the used Tools
• Partnumbers of used Big Parts/Components (e.g. Engine, wheel etc.)
• Quantity of the Big parts used
• Small Picture of each used Big part/Component
  
  Here is the Form I made for Creating a Work Step:
  
  

5. Final Quality Check
This is actually the same as the Entry Check but only with a finished product.

6. List of Common Faults
A simple list of Faults which occur during the Work Process for the specific Instruction.

Database Criteria

There are some key elements which must be incorporated into this database. These are:

• After editting a WorkStep/Safety Sheet/Entry Check/Final Check, the old version must be saved.
• Each time a document is editted, the initials of the editor and the edit date are saved. Also the revision number will be increased with each save.
• The user must be able to: Create, Edit and View each type of document (Steps, Instructions, Safety Sheets etc.)
• A form where the user can Add, Edit and View Parts

A secondary goal is to style the "Create/Edit"forms to match the looks of the final report/view versions. The idea is that a employee can create it's own instructions instinctivly.

I've already created a Login Screen where the user selects (or adds) its name. A temporary value will be created and this is used in each Form to add the users initals after each revision.

After logging in the Main form appears. From here the user can navigate trough all the other forms. See:

Also I've already created a Create/Edit/View Form for the Parts:

So far the basic Layout of my Database.

Now a bit more Technical background.

These are the relationships in my Database:

(I've reached my max attachments so I had to use a tinypic url)

Most of the Controls on my forms are straight forward One to many relations. A little bit different are the two Continuous forms on my CreateStep Form.

Continuous Form: subForm1 & subForm2
Record Source: SELECT qrySteps.StepID , qrySteps.PartID , qrySteps.PartPi cture, qrySteps.Qty FROM qrySteps;
Link Master Field: StepID
Link Child Field: StepID

Both subforms have:

Textbox: QtyBox
Control Source: Qty

Combobox: cboPart
Control Source: PartID
RowSource: tblParts

The Continuous subforms work perfect when I create a new Step. Now I'm trying to make the Edit Form work, where the user selects a step by StepID and the form should be filled in automatically. The revision number will be increased by one and the user can make changes to the control fields. If the user is finished he can press a "Edit Step" button to save the data to a new record (new StepID) or he can choose to cancel the edit by using the macro Undo Record (if he presses back to Main Page). I use a .Bookmark and .FindFirst to select the data for editting on the Edit Step Form. The data on the Continuous forms is also loaded but when the user presses Save, the editted data in the subforms overwrites the original data, it doesn't add new records to tblStepParts.

Before resolving this issue i really want to know If my database is set up correctly for this.

I really hope someone can help me with this and that others can also learn from this.

Kind regards,

Jeroen

[twinnyfo]

Jeroen,

Honestly, your question is kind of broad, almost too broad to provide a detailed, honest response. However, I understand your concerns, as building a DB incorrectly the first time can create countless headaches in the long run. So, while broad, your question is valid for this forum (I think).

The first thing I see is a small bit of redundancy in tblSteps. You list OperationID and DeptID. However, if I understand tblOperations and tblDeptartment (some would argue based on your naming convention that this Table should be names tblDepartments--plural) correctly, once you have the OperationID, you will know the DeptID. So, DeptID could be removed from the tblSteps. If the User needs to know the Department while using the Form, it is an easy join in the query (preferred) to support the Form or as a quick lookup in a control.

The second observation is Icon1-5. What happens if you have more than 5 icons? It could be rare, but there is no contingency for it. What if there are fewer than 5 icons? Then you have wasted space. Now, if there are always 5 icons and only 5 icons--without fail--then this construction can work and is probably the best way to manage it. A better way may be to have a Table (tblIcons) with a Foreign Key to StepID to allow incorporation of more of fewer icons. Depending on how you use these icons in your Forms could create challenges, though, so weigh what you want to do with how complex you want your Form to be.

Additionally, I think the Table tblSteps should have a field for "StepNumber ". Thus, if you have a Process that takes 13 Steps, you can order those Steps appropriately. Right now, I don't see a method for doing this (unless I am missing something).

Finally, I'm not sure I fully understand the purpose of the Tables tblStepParts and tblStepComponen ts. It looks like the PartID and Quantity are all captures in tblComponents. But, again, I may be missing something obvious.

Concerning your desire to edit Steps, retaining previous versions, I do think a good way to do this is to have your Main Step Form showing what the current Step is. Then, when the User click "Edit Step" from that Form, a new Form pops up (retaining your original location in the current Step. This new Form would be unbound and populate based on the Step ID of the current Step. The User's initials would automatically populate, as well as the incremented revision number. As the user makes changes, if they save the record, it appends the current data to a new record in the Steps Table.

I think discussion is also warranted on how to view the most recently revised Steps as the "current Step". If you have a revision number (or a Date on which the Step was revised), perhaps your Query to populate your Steps Form could incorporate the Max() function to pull only the latest dates for each StepNumber (see above on StepNumber).

I hope this feedback is along the lines of what you were looking for. Good luck on your project and we'll assist further if we can.

[Jeroen3131]

Hello Twinny,

The tblDepartment should indeed be tblDepartmens, it's a typo. You are correct on the fact that if you have the OperationID, you also know the DeptID. I'll remove DeptID from tblSteps.

Regarding the 5 Icons: I've used them because it seemed to me to be the easiest way. There is a max of 5 Icons for each Step but It's true that the database will contain empty spaces.

I've created a tblStepParts and tblComponents to populate my 2 continuous subforms. On the 1st (tblStepParts connected) subfrom the user can select small Parts like bolts etc.
The second subform is for bigger parts like an engine. If load a record onto the form I want the small parts (picture, qty and partnumber) displayed on the center of my EditStep form. The bigger components (pictures etc.) have to be displayed at the right side of my form. So I introduced these tables only for visual positioning on my form.

Adding a StepNumber to the table tblSteps is unnecessary, I think. If the user wants to create a Work Instruction, he selects the StepNames from a List in the order he wants. If I would sort the Steps on StepNumber I won't be able to change the work proces. For instance: if the current work proces is A, B, C I can't change the order to A,C,B.

I really do like the idea of opening a second (unbound) Form. It seems to me an easy and robust solution without dificult VBA programming. The only difficulty might be setting the continuous subforms. I don't have a idea yet how I could realise that.

The idea of selecting the latest steps based on Revision number was already in the back of my head. This should limit the selection list for the User by a lot.

Thanks for providing helpfull feedback!

[twinnyfo]

Adding a StepNumber to the table tblSteps is unnecessary, I think. If the user wants to create a Work Instruction, he selects the StepNames from a List in the order he wants. If I would sort the Steps on StepNumber I won't be able to change the work proces. For instance: if the current work proces is A, B, C I can't change the order to A,C,B.

This is exactly what I am talking about. There is nothing indicating (as far as I can see--I might still be missing something) which steps are A, B or C. Plus, if you have a "SortOrder" or "StepNumber ", these can always be adjusted programmaticall y. For example, just including "Up" and "Down" buttons to adjust the order. However, I don't want to force an idea onto your project.

I really do like the idea of opening a second (unbound) Form. It seems to me an easy and robust solution without dificult VBA programming. The only difficulty might be setting the continuous subforms. I don't have a idea yet how I could realise that.

I would think the second, unbound form would be built much the same way as the original form. All the subforms would still be based on the StepID.

This also introduces something in the back of my head....

If StepID is unique (i.e. the Primary Key) for the Table, then how do you identify "which Step belongs to which Process"?

For example, if StepID is an AutoNumber (not saying that it shouldn't be), then, if you have Process A, how do we know which Steps belong to that Process? Granted, I may still be a bit confused by how you have envisioned your DB.

However, if you maintain StepID (which it should be), then shouldn't there be a Field designating the Process to which the Step applies, then a Field designating which order the Steps are performed? I'm just trying to clarify some of the details that are not clear. I know I am probably making things more confusing and unclear.

Perhaps an Example:

You have a Table tblProcesses. This Table lists the Processes to which the steps apply. So....

[B][U]Field[/U][/B]        [B][U]Description[/U][/B]
ProcessID    AutoNumber, PK
ProcessName  Text, Brief description: "Build Widget"
ProcessDesc  Text, longer explanation of Process:  "User Parts A, B and C to construct Widget."

Then in your tblSteps:

[B][U]Field[/U][/B]       [B][U]Description[/U][/B]
StepID      AutoNumber, PK
ProcessID   FK to tblProcesses
StepNumber  Integer, sequentially lists each Step in order of completion.
...

Then, your subforms listing details about the Steps is Master/Child based on ProcessID and StepNumber.

This might over complexify things, and again, I just might not be seeing how the Steps are independently related to separate processes.

[jforbes]

I don't have a lot of time to respond to this today, but I wanted to introduce these concepts before it was too late.

I'm not seeing a Header type Table for your Work Steps, you mention that each WorkInstruction is made up of multiple Work Steps. I would include tblWorkInstruct ionsas a Header with a one to many relationship to tblSteps. Include a RevisionNumber for your tblWorkInstruct ions and include this in the link between tblWorkInstruct ions and tblSteps. This way you can include an Action (button) from your Form to Create a New Revision of the Work Instructions that would Increment the RevisionNumber and copy all the Steps for the Existing Revision and include them in the new Revision. This will allow you to keep your History.

I would like to expand on this, but I don't have time today.

Also, Twinnyfo's suggestion to include an OrderBy field (StepNumber) for your Steps will save you from a headache in the future when somebody decides to insert a step between two existing steps.

[Jeroen3131]

Twinnyfo, My idea was exactly what you described with your Table "tblProcces s" and adding a FK to tblSteps.

I haven't created the Process table yet in my database and maybe thats why I forgot it to mention it in my 1st post. Sorry for that!

Jforbes, can you explain what you mean with a Header type Table? I suppose you mean it's like a "Top-level" table, the same as Twinnyfo's tblProcesses?

I'm also not sure with what you actually mean with "include this in the link" but I understand the concept of incrementing the RevisionNumber and copying the Steps to the New Revision Work Instruction.

Also I like Twinnyfo's idea of having a ordered List with Steps for a Work Instruction. The user can select a stepname and press a Up or Down button to order the Steps.

Thanks for the Inputs, too bad I'm really busy at the moment so I can't immediately implement these ideas in my database :/ .

[twinnyfo]

Jeroen,

Keep plugging away! Let us know of your progress and we're glad to assist as snags arise.

[jforbes]

Haha, Yep, Twinnyfo and I are thinking alike. After getting a little time to read Twinnyfo's post, my reference to a tblWorkInstruct ions is the same as Twinnyfo's tblProcesses. Sorry for not catching this earlier, I was in a hurry as I am now. =)

I would still include a Revision Field/Counter that isn't quite a Foreign Key between tblProcesses and tblSteps as it would increment the field on tblProcesses as revisions are being created, but will link like a Foreign Key when you build your Form and SubForm relationship so that when you are viewing a Process/WorkInstruction only the current revision of Steps are displayed in the SubForm.

[twinnyfo]

@jforbes,

I would still include a Revision Field/Counter that isn't quite a Foreign Key between tblProcesses and tblSteps as it would increment the field on tblProcesses as revisions are being created, but will link like a Foreign Key when you build your Form and SubForm relationship so that when you are viewing a Process/WorkInstruction only the current revision of Steps are displayed in the SubForm.

Do you mean, that this would list the WorkInstruction s and the list of Steps (with the most recent revision number)? For example, just a table that says:

[B][U]Instruction[/U][/B]   [U][B]Step[/B][/U]  [B][U]Revision[/U][/B]
Build Widget   1       3
Build Widget   2       1
Build Widget   3       17
Build Widget   4       2
Build Widget   5       9
Change Tire    1       8
Change Tire    2       12
Change Tire    3       117
Change Tire    4       1
Change Tire    5       24
Change Tire    5       48

Then you just link all the parts to that table? I guess I am not certain I completely followed you, but if the above is kinda what you were thinkin', I think I kinda like the concept. It eliminates the need to "find" the latest revision, and all tables would (should) talk nicely to each other.

Good thinks!

[jforbes]

That would serve the purpose of what I was thinking, and would probably be the most Normalized way of doing it.

My original thougt was a bit less normalized by adding a Revision Number to both the tblWorkInstruct ion(tblProcesse s)and tblStep. When the records are first created they would all default to 1 (the current revision number). Then when it is decided that edits need to be made, a button would be clicked which would increment the Revision Number on the Work Instruction and then create a copy of all the current Steps while setting their Revision Number to the New Revision Number. The user could edit away, add steps, change fields on the step and delete as needed. So from a Form standpoint, it would be a simple link between the MainForm and SubForm by including the Revision Number as part of the SubForm link.

Personally, at this point I would include a way to lock the Work Instruction and Steps for that revision so that the only way things could then be edited is by creating a new Revision.

This may be taking things much further than what Jeroen is looking for, but it wouldn't be that difficult to implement while providing quite a bit of flexibility going forward.

[Jeroen3131]

@jforbes,

This is ,(I think), exactly what I'm looking for. My initial thought was also to add a Revision number for both tables and increment them by pressing an "Edit" button. But now I see there is another possibility (as shown in Twinnyfo's table). However, I can't see what the advantages are.

Also, I need to have a revision counter for each Work Instruction. If there has been made a fault during editing, one of the previous Work Instructions should be retrievable.

For instance:

Work Instruction     WI rev 	 Step   Step rev.
Build Widget		0		      1	      2
Build Widget		0		      2	      5
Build Widget		0		      3	      2
Build Widget		0		      4	      1
Build Widget		0		      5	      1
Build Widget		1		      1	      2
Build Widget		1		      2	      5
Build Widget		1		      7	      1
Build Widget		1		      9	      6
Build Widget		1		      5	      1

[jforbes]

Sounds like you are on the right path. Either way you do it should work for you, both have their advantages.

The Advantages to what Twinnyfo is suggesting it that it would be fully normalized and you will only add data to you database that is needed. This would eliminate redundancy in the case that an unmodified Step is duplicated repeatedly. It's a little more complex, but just a little and knocking it out early on is a lot easier that going back and implementing it later.

[twinnyfo]

Jerome,

Have you been able to make any additional progress on this project?

[Jeroen3131]

Hey Twinny,

Yes, I have. I've created the tables and added the relationships just as we discussed in the previous posts. I've had an idea to use a form with a subform in another subform (nested subform). So I have a main form which is related to StepID, a subform which is related to StepRevision and the other nested subform is related to the selected parts and quantities. I'm still figuring out if everything works, I got it working for 75% now. Still need to do some testing, after testing I'll tell you guys what i exactly did but for now I'm very busy.

Kind regards

[twinnyfo]

OK thanks! Let us know if you hit any other snags.