Equipment Hierarchies 3

[mitch37]

Hello guys

I am an Industrial Engineer (In my first year of work) and I have obviously not done much databse programming. I am designing a database for a client that stores equipment data and failures. The equipment has a 'hierarchy' and cascades down from the line/area down to the smallest component, with a set naming convention.

Bearing in mind that I use MS Access with minimal VB code (i use the built in functions), is there any way i can store data so that I can draw a report on the lowest level and it books it links it to the highest level that it is connected to?

I have added what has been done so far, even though it is slightly confidential...but it does not have data in it so I guess it is ok.

 

[mitch37]

thanks...there is more of a hierarchy if the plant puts in all the data they have. i just managed to get one spreadsheet with that data so it's a start

 

[mitch37]

Hey guys

After a bit of attention elsewhere, i have returned to this database and got it mostly working, but the tree view is now giving me trouble.

Have attached it...any 'brainboff' willing to help a friend out?

 

[MajP]

I can give you the code you need, but I still do not understand this. There seems to be a natural hierarchy, yet you do not seem to use it. I think at least everything belongs to Line1 which has an ID of 32, but everything shows a parent ID of 2233 which is not even in the hierarchy. If you are not really using a hierarchy why not use a form with a subform because all you really seem to show is a list.

I would think you would want data that looks like this.

-Factory
---Line 1
-----Blender Line 1
-------Blender Lubrication
-------Blender Electrical
-------Blender Pneumatic
...
-----Bottle Washer Line 1
-------Bottle Washer In Feed
-------Bottle Washer Carrier System
-------Bottle Washer Discharge
...
-----Capper Line 1

---Line 2
-----Blender Line 2

Now you could show all of the lines in a tree.
Each line would have branches for
:Blenders, Washers, Cappers,Casers, Crate Washers,...
Then you click on washers and you would see all the parts for that washer.

Unless you can explain some kind of hierrarchy above the line level, then I would think a tree view is a waste of time. From your structure all of these pieces of equipment are just listed under a single node. That appears to be just a simple parent child relationship not a hierarchy. It looks like you could easily make a natural hierarchy, but you just do not have it.

 

[mitch37]

Hi MajP

I am doing this on bosses' orders lol...i could explain to him what you are saying. What i think he wanted was something that could be linked to tpm and breakdown/root cause analysis that goes from e.g. plant - section - line - asset - subasset-component.

Perhaps the parts i got in my list, are not sufficient enough to 'get the feeling' of what is going on?

 

[mitch37]

by the way Maj, have I done okay with the db seeing i started with no experience...OR IS MY EFFORT COMPLETE AND UTTER CRAP? Lol

 

[MajP]

I am not sure if it makes sense or not to do a hierarchical structure. I would think yes, but just not seeing where you are going. I am not sure if you really have a hierarchy or just parent child relationships.

From what I am seeing you are not using the heirarchy, but maybe this is just a snapshot. Can you explain where the hierarchy is. Maybe it is just related tables. Maybe your relationship is something like.

tblPlant
plantID
information about a plant

tblSection
sectionID
foriegn key to a plant
information about a section

tblLine
lineID
foreingkey to a section
information about a line

tblAssets
assestID
foreign key to a line
(maybe this is a self referencing table since assets can have levels of subassets.)

You have a parent key of 2233. What is that? Is that in another table? Should all of these be assets of line 1? Then they should all have a parent ID of 32. Is this just the asset layer of line 1, and there would be sub assets, and components in this table.

 

[mitch37]

ok, i don't know how 2233 got there but i have fixed that. The relationship you suggested above is correct. Basically, if the user clicks on 'a plus sign' next to 'plant', it expands to show all the lines (that are not all listed in the database yet). if he clicks on the plus next to line1, it expands to show all equipment/assets in line1, then if he clicks on e.g blender line 1, it expands to show its components, or in this case 'EquipDescrip'.

Have i done it drastically wrong?

 

[MajP]

Is this the relationship?
Line1
-Blender Line 1
--Blender / Famix Line 1
--Blender Electrical
--Blender Lubrication
--Blender Main Body
--Blender Pneumatics
--Blender Pumps
--Dearation Tank
--Plate Heat Exchanger
--Pressureless Tank
--Product Holding Tank
+Bottle Washer Line 1
+Capper Line 1
+Caser Line 1
+Crate Washer Line 1
+Date Coder Line 1 No 1
+Date Coder Line 1 No 2
+Decapper No 1 Line 1
+Decapper No 2 Line 1
+Depalletiser Line 1
+EBI Line 1
+Empty Bottle Conveyor Line 1
+Empty Bottle Reject Conveyor Line 1
+Empty Crate Conveyor Line 1
Line2
Line3

Is this the relationship
Lines
Assets (ie.Blender Line 1)
Componenets (i.e Blender Electrical)

What you have will work if that is the exact relationship, but if you have other levels such as

Line
Asset
sub Asset
Componenet
subcomponent

your data structure does not support it. Need these answers before I can give you the right answer.

 

[mitch37]

at the moment that is the exact structure. i guess in future it may go down to a subcomponent. how much more difficult would that be? if it's complex, the specified structure should be ok, seeing that is what i was asked to do?

I think i owe you another six pack of Bud and a bag of chips.Ever heard of Biltong? I think Americans call it Jerky?

 

[MajP]

I think I got it. Your problem is you have designed this like a spread sheet not a relational database. This is going to cause you a lot of trouble. You are trying to dump everything into a flat table. You need to read up on database normalization and basic relational database design.

Your table has repeating data, and different types of elements. If it was me these are my tables. Now I do not know the type of relationships above a line. You mentioned sections, and possibly multiple plants. You will have to describe that more.

tblLines ( a table that hold unique information about the production line. Each line has a unique primary key)

tblLines
lineNumber (primary key)
lineName
functionalLocation
other fields unique to a line

lineNumber	lineName	functionalLocation
1111111	        line 1	        Z050-020-01-010
1111112	        line 2	        xxxx
1111113	        line 3	        yyyyy

now my table equipment list the major equipment on a line
Each piece has a unique primary key (name) and a foriegn key relating it back to the line

tblEquipment
equipmentName (primary key)
functionalLocation
lineID_fk (relates to tblLines)

EquipName	FunctionalLocation	LineID_FK
Empty Crate Conveyor Line 1	Z050-020-01-010-230	1111111
Empty Bottle Reject Conveyor Line 1	Z050-020-01-010-241	1111111
Empty Bottle Conveyor Line 1	Z050-020-01-010-240	1111111
EBI Line 1	        Z050-020-01-010-102	1111111
Depalletiser Line 1	Z050-020-01-010-021	1111111
Decapper No 2 Line 1	Z050-020-01-010-061	1111111
Decapper No 1 Line 1	Z050-020-01-010-060	1111111
Date Coder Line 1 No 2	Z050-020-01-010-161	1111111
Date Coder Line 1 No 1	Z050-020-01-010-160	1111111
Crate Washer Line 1	Z050-020-01-010-080	1111111
Caser Line 1	        Z050-020-01-010-171	1111111
Capper Line 1	        Z050-020-01-010-130	1111111
Bottle Washer Line 1	Z050-020-01-010-070	1111111
Blender Line 1	        Z050-020-01-010-110	1111111
Blender Line 2		1111112

now your equipment table becomes my component table, because that is all that you really have in there. You get rid of a lot of repeating information because that information is contained in the equipment table or Line table, which you can link to

tblComponent
componentNumber (This should be the primary key)
componentName
equipmentName_fk (relates a component to equipment table)

componentNumber	ComponentName	EquipName_fk	
30005442	Plate Heat Exchanger	Blender Line 1	
30001918	Blender / Famix Line 1	Blender Line 1	
30003964	Pressureless Tank	Blender Line 1	
30003963	Dearation Tank	Blender Line 1	
30002644	Blender Pumps	Blender Line 1	
30002643	Blender Pneumatics	Blender Line 1	
.....

Now if I would think you tag a failure against a component not against the whole piece of equipment. If you want components to have subcomponents you can put them in the component table, but add a field called "parentComponentID".

So if plate heat exchanger has a "heat exhanger element" then that component would have a parentComponentID of 30005442.

Does this make more sense? Do you see how data is no longer repeated. Like information (entities) are maintained in their own table.

 

[mitch37]

i have a couple of hours to spend on this this morning. as i said, i have had no VB training but will give this a go....

u seem like the biggest Access Guru!!!

 

[MajP]

This has nothing to do with vb just database theory and design. Here is a primer you should read

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

There are a plenty of other ones on the web. Your original equipment table seems to violate a few of these rules. "Repeating data", "Mixed entities", "No primary key for higher level equipment". Your other tables seem to show problems as well. example (action1, action2, action 3...action 5, as fields not rows)

These are fixable, but if you do not get the skeleton right all the fancy forms and reports do not really help. You got to get this structure correct. You can not take a straight dump from Excel without manipulating the data. Spreadsheets and relational database use very different paradigms for data structure.

So I need to fully understand your relationships.

I think I understand

A plant has production lines, production lines have large pieces of equipment, each piece of equipment has components, a component may have a subcomponent. A failure is associated to a component.

What about plants? Is there only one plant or multiple plants? You mentioned sections. How is that related to a plant and a line.

 

[mitch37]

Hi Maj

I am battling quite a bit...would it be easier to start from scratch. Do I put a parentID in tblEquipment, tblLine and tblComponent?

 

[mitch37]

sorry i posted the previous mail before i read your reply. there is a possibility of multiple plants in the future but i have decided for now i am going to set it up for the one plant.The section and assemblies part also seems a little strange to me, because the data i got shows something different. i am going to work with what they are currently doing, as i dont see them changing their system.

Anyway...once i get the structure right, will it be easy to code the expandable tree?

 

[MajP]

http://www.4shared.com/file/95186202/2f40c403/EquipHierarchyV3.html

Take a look at this. Look at my tables, and then look at my query "qryLineEquipComponent" to see how it all comes back together.

On the treeview if you click a component it demonstrates how to open the form from the treeview.

The tree view code is really, really complex. But since I have encapsulated it in a class model it is completely reuseable and has a lot of features. It only took me a couple of minutes to build the tree view using my recycled code.

 

[mitch37]

wow...i wish i could repay you somehow...it's a bit busy today but guess what i'll be doing this weekend? haha!!

thank you!!!!!!!!!!!!

 

[MajP]

Do not get too enamored with the fancy interface. Again it is only as good as the underlying data structure. Especially people who do a lot of Excel have a tendency to design Access starting with presentation and some final report format in mind. This usually ends up with an completely inflexible database and everything afterwards gets very difficult to add to. If you focus on data structure first you can design all kinds of forms to get the data in, and design queries to easily calculate, find, and show the data.

So you need to really understand your data relationships and buisness model, and not focus on the structures of the current Excel spread sheets. You will be able to get the data out of these sheets, do not worry. Understand all of your relationships between levels of "equipment", failures, actions, employees, etc.

 

[mitch37]

i understand. i am an excel guru myself (not being proud am i...haha), so access is new territory. i have actually done a basic course which explains basic relationships, but theory and practice is quite different.

 

[MajP]

So work on cleaning up your tables this weekend and then lets look at them. You can use this to document your tables:
faq700-6905

The important fields for each table are the: Primary key and the foreign key to other tables. The other fields just have to uniquely describe the entity in the table. Here are some other suggestions:
Use a good naming convention. I start all tables with tbl, forms with frm, queries with qry, etc. There are some vb and sql standards. I use my own but most people can look at my names and instantly know what I am talking about. As long as you are consistent. Do not use any spaces or special characters in your names. I do something like
tblEquipment
equipmentName_pk
dtmDateInstalled (date field)
blnCriticalEquipment (boolean field)

delQryRemoveComponent (delete query to remove component)
frmAddNewComponent (form to add new component)
subFrmComponents (a subform of components to link to a main form of Equipment)

This looks like it could become a large project. I find that bosses will ask for a single function, but once you build that requirements start to pile on. So you need to build this with flexibility and growth in mind. With that said, if there is the possibility of multiple plants and sections design the tables to be able to account for that design.

 

[mitch37]

Hey Maj..how are you this week. I have tried to clean up my tables. Having a prob wit the relationship between equipment and components - it should be one to many.

here is the documentation:

DocumentTables

tblAction
ActionID_pk dbLong PrimaryKey Indexed
Action01 dbText
Action02 dbText
Action03 dbText
Action04 dbText
Action05 dbText
ActionDescription dbMemo
FailureID_fk dbLong ForiegnKey Indexed

tblComponents
ComponentNumber_pk dbLong PrimaryKey Indexed
ComponentName dbText
EquipID_fk dbLong ForiegnKey Indexed
ParentComponentID dbLong Indexed

tblEquipment
EquipID_pk dbLong PrimaryKey Indexed
EquipName_pk dbText
FunctionalLocation dbText
LineNumber_fk dbLong ForiegnKey Indexed

tblFailures
FailureID_pk dbLong PrimaryKey Indexed
DateofFailure dbDate
TimeofFailure dbText
FailureCategory dbText
PersonID_fk dbLong ForiegnKey Indexed
LineNumber_fk dbLong ForiegnKey Indexed
EquipID_fk dbLong Indexed
ComponentNumber_fk dbLong ForiegnKey Indexed
ActionID_fk dbLong Indexed

tblLines
LineNumber_pk dbLong PrimaryKey Indexed
FunctionalLocation_fk dbText Indexed

tblPerson
PersonID_pk dbLong PrimaryKey Indexed
Name dbText
Surname dbText
Position dbText

tblTeamMembers
TeamID_pk dbLong PrimaryKey Indexed
TeamDescription dbMemo
PersonID_fk dbLong ForiegnKey Indexed


DocumentRelations

Name: tblComponentstblFailures
Table: tblComponents
Foreign Table: tblFailures
PK: ComponentNumber_pk FK:ComponentNumber_fk

Name: tblEquipmenttblComponents
Table: tblEquipment
Foreign Table: tblComponents
PK: EquipID_pk FK:EquipID_fk

Name: tblFailurestblAction
Table: tblFailures
Foreign Table: tblAction
PK: FailureID_pk FK:FailureID_fk

Name: tblLinestblEquipment
Table: tblLines
Foreign Table: tblEquipment
PK: LineNumber_pk FK:LineNumber_fk

Name: tblLinestblFailures
Table: tblLines
Foreign Table: tblFailures
PK: LineNumber_pk FK:LineNumber_fk

Name: tblPersontblFailures
Table: tblPerson
Foreign Table: tblFailures
PK: PersonID_pk FKersonID_fk

Name: tblPersontblTeamMembers
Table: tblPerson
Foreign Table: tblTeamMembers
PK: PersonID_pk FKersonID_fk



Where to now? I dont want to continue knowing that one of my relationships is wrong. I also decided to leave out 5W1H , as there is a set template for it, and i could put a link to the file