473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Database Layout for Work Instructions

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): CreateSafetySheet.jpg

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:
    CreateStep.jpg
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: Main Page.jpg

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

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.PartPicture, 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
Nov 13 '14 #1
17 4828
twinnyfo
3,653 Expert Mod 2GB
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 tblStepComponents. 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.
Nov 13 '14 #2
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!
Nov 13 '14 #3
twinnyfo
3,653 Expert Mod 2GB
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 programmatically. 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....

Expand|Select|Wrap|Line Numbers
  1. Field        Description
  2. ProcessID    AutoNumber, PK
  3. ProcessName  Text, Brief description: "Build Widget"
  4. ProcessDesc  Text, longer explanation of Process:  "User Parts A, B and C to construct Widget."
Then in your tblSteps:

Expand|Select|Wrap|Line Numbers
  1. Field       Description
  2. StepID      AutoNumber, PK
  3. ProcessID   FK to tblProcesses
  4. StepNumber  Integer, sequentially lists each Step in order of completion.
  5. ...
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.
Nov 13 '14 #4
jforbes
1,107 Expert 1GB
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 tblWorkInstructionsas a Header with a one to many relationship to tblSteps. Include a RevisionNumber for your tblWorkInstructions and include this in the link between tblWorkInstructions 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.
Nov 13 '14 #5
Twinnyfo, My idea was exactly what you described with your Table "tblProccess" 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 :/ .
Nov 14 '14 #6
twinnyfo
3,653 Expert Mod 2GB
Jeroen,

Keep plugging away! Let us know of your progress and we're glad to assist as snags arise.
Nov 14 '14 #7
jforbes
1,107 Expert 1GB
Haha, Yep, Twinnyfo and I are thinking alike. After getting a little time to read Twinnyfo's post, my reference to a tblWorkInstructions 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.
Nov 14 '14 #8
twinnyfo
3,653 Expert Mod 2GB
@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 WorkInstructions and the list of Steps (with the most recent revision number)? For example, just a table that says:

Expand|Select|Wrap|Line Numbers
  1. Instruction   Step  Revision
  2. Build Widget   1       3
  3. Build Widget   2       1
  4. Build Widget   3       17
  5. Build Widget   4       2
  6. Build Widget   5       9
  7. Change Tire    1       8
  8. Change Tire    2       12
  9. Change Tire    3       117
  10. Change Tire    4       1
  11. Change Tire    5       24
  12. 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!
Nov 14 '14 #9
jforbes
1,107 Expert 1GB
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 tblWorkInstruction(tblProcesses)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.
Nov 14 '14 #10
@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:

Expand|Select|Wrap|Line Numbers
  1. Work Instruction     WI rev      Step   Step rev.
  2. Build Widget        0              1          2
  3. Build Widget        0              2          5
  4. Build Widget        0              3          2
  5. Build Widget        0              4          1
  6. Build Widget        0              5          1
  7. Build Widget        1              1          2
  8. Build Widget        1              2          5
  9. Build Widget        1              7          1
  10. Build Widget        1              9          6
  11. Build Widget        1              5          1
  12.  
Nov 17 '14 #11
jforbes
1,107 Expert 1GB
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.
Nov 17 '14 #12
twinnyfo
3,653 Expert Mod 2GB
Jerome,

Have you been able to make any additional progress on this project?
Nov 19 '14 #13
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
Nov 19 '14 #14
twinnyfo
3,653 Expert Mod 2GB
OK thanks! Let us know if you hit any other snags.
Nov 24 '14 #15
Update -

I've concluded that working with subforms isn't the way to go for my databse project. Each time the user selects something in a subform the records "above" the selected record are automatically saved.

Now I've completely ditched all the subforms. All my forms are now unbound and only when the user presses A save button, the values from the controls are inserted into their appropriate tables with several SQL INSERT INTO statements.

I've also added a revision counter to each form. When a Step is created it is automatically set to 0. Each time the user edits the Step with the EditStep Form, the counter is incremented with 1. By using a DMax function I retrieve the latest revision number. The problem here is that it retrieves the max revision from the list of all the existing steps (including those from other Work Instructions). Therefore I've added a "WorkInstructionnumber" in the DMax criteria for retrieving only the data from the WI to be editted.

I Also implemented a UserID (temporary variable which is set when the user logs in) and a revision date (a simple Now() function). This enables me to see exactly which user created or editted a Step/WI and when they did that.

So far so good, my database is 95% ready. I'm only struggling with a minor Listbox with Up and Down buttons.

Conclusion:

After this period I've concluded that the database layout, as described in this thread, is correct and suitable for my application. Using unbound forms seems to me the best option to avoid the Autosave property of continuous subforms.
Dec 3 '14 #16
twinnyfo
3,653 Expert Mod 2GB
I've concluded that working with subforms isn't the way to go for my databse project. Each time the user selects something in a subform the records "above" the selected record are automatically saved.
Great conclusion to a long thread! That method can often be the best way do manage a situation like yours. I'm glad we could be part of the discovery process.

Let us know if you need any other assistance!
Dec 3 '14 #17
Hi Jeroen,

I wonder what is the status of your "MS-access work-instructions project". I'm searching for similar functionalities.


Kind regards,
Sylvester
Mar 18 '16 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Summasummarum | last post by:
Hi ng, I need some input/suggestions for a very small layout. The situation: Some groupings of thumbnails. For every picture (thumbnail) there is a "big" picture. Thats it basically :) On the...
0
by: David Collier | last post by:
Warning - question from someone who doesn't know much! We have a number of outstations at remote locations, connected back to a central server over GPRS. We have concluded that we would like...
0
by: Curious Tom | last post by:
I'm using ASP.Net and I'm wondering if code generated using the grid layout will run on netscape browsers? Also what version of netscape? I've read that Grid Layout uses DHTML and the IE and...
3
by: Domagoj Cajic | last post by:
First i want to say that i just subscribed to this mailing list and this is my first post. :) im new to postgresql and i installed it recently on linux redhat 9 system following the instructions...
2
by: Stefan Schneider | last post by:
Hi, I have to copy an existing database from one Windows-Server to another one. The source database is a 8.1.0 database on a Windows 2000 Server, the destination database will be a newer 8.x...
5
by: Inquest750 | last post by:
Can anyone tell me where I can get instructions to design an Access database to work directly with a website? The website will hopefully contain a member base and that is what i would use access...
5
by: Dave | last post by:
This is my first .NET project (although I am experienced with VS6 and InterDev). I am simply trying to set up a basic ASPX project and connect to an Access database (following the instructions in...
5
by: =?Utf-8?B?Y2RiaWdncw==?= | last post by:
Hi, I've been tasked to write a windows app that allows people to enter transactions. For each transaction, there can be an unknown number of items, and as a person enters an item, the program...
1
realin
by: realin | last post by:
hello guys, I am just unable to make a simple 2 Box model layout layout work perfect on the browsers IE and ffx. My resulting webpage responds variably on either of them.. The problem is that...
3
Procman
by: Procman | last post by:
Hi, I work for a city Senior Citizens Program and I’m creating a new database to replace and old database and several separate and outdated processes (pen and paper) work processes. Many of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.