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]

Hello again fneily

I have started from scratch and modelled the tables as you suggested. Still used Autonumber quite a bit though, as some PK's really were quite difficult to define Unique Numbers for.

Something seems a bit weird though - especially the PARENTID in '''And your other concern was the "hierarchy". As MajP suggested, you'll have a self-referencing table -
tblEquipRelation
ERID
EquipID
ParentID Which will be filled with EquipID also'''


The 5W1H is a tool which is used to understand a problem. I use the form of this, just to record the data that is filled on on the sheet version of 5W1H.

I have attached the tables and relationships? Am i on the right track?

Hope you enjoyed your drink

 

[mitch37]

http://www.zshare.net/download/524737947daa5b9e/

 

[mitch37]

This may help...it shows how the equipment hierarchy works

 

[MajP]

I do not see anything that suggests a hierarchy in a data structure. I am guessing the names determine the hierarchy.
Like I said before you need a self referencing table, so you need to add the parentID field.

functionalLocationID     parentID

  PE                     
  PE-P                   PE
  PE-P-P                 PE-P
  PE-P-P-L1              PE-P-P
  PE-P-P-L1-DEC1         PE-P-P-L1
  PE-P-P-L1-DEC1-01      PE-P-P-L1-DEC1
  PE-P-P-L1-DEC1-02      PE-P-P-L1-DEC1
  PE-P-P-L1-DEC1-03      PE-P-P-L1-DEC1

Also the above data is sorted in a hierarchy order, but in a database table the order data is entered is meaningless. Sorting the above data into a hierarchy can be done using the function I showed above as long as you know a piece of equipments parent.

 

[fneily]

A couple of things - you still have EquipHierLev in tblEquipment. Take it out, not needed.
You have EquipmentNumber as text in tblEquipment, but ParentID as numeric in tblEquipRelation. Is it text or numeric?

The 5W1H is a sheet. You can create a form that looks like the sheet, but not a table structure. So the table 5W1H is not needed. All the data will be held in normalized tables. So if any field on the sheet is not in one of the tables, you have to find a place for it or create a new table to hold it. Then, through queries and functions you'll fill in the form that looks like the sheet.

Again, tblEquipRelation will be the self-referencing table you'll use with MajP code to show the hierarchy.

Vanilla Grand Marnier. Oh my. Add a blue-eyed blonde then Tag my Toe, I'm a dead man.

 

[mitch37]

lol i will try understand some of that when i get home this evening...not sure about coding though. Equipment number is text as it can contain text and numerals.

If i dont put in equipment Hierarchy Level, how will i record whether the equipment is "Line/Area;Equipment;Partitioning;Assemblies;or Parts"


Blue Eyed Blonde? South African or American?

 

[MajP]

Once you get your data structure correct you may want to use a tree view for working with you data. I have a lot of treeview utilities. Here is an example of 1000 USMC units. All data is from a single self referencing table.

 

[fneily]

Line/Area;Equipment;Partitioning;Assemblies;or Parts". This is more of a type then a hierarchy. So you can have a field known as EquipType or something.
An hierarchy is more specific:
air filter 2432 goes on engine 387489 in car aake334.

I like MajP idea. More learning to create but it looks more professional.

Picking up some Navan tomorrow. $45 a bottle. The other part may run be $100. Scandinavian.

 

[mitch37]

how simple is the tree view? Thanks for the tip..i like it.

fneily, do u collect bottles of alcohol?

 

[MajP]

Not very simple if you had to write it from scratch. However, I have encapsulated most of the code so you only have to change your field names. I will post a demo that you can try.

 

[MajP]

http://www.4shared.com/file/75260486/ed3c7781/Equipment_TreeviewUtilities.html

This is generic for any self referencing table.
1) build a query from your table. See my query.
2) fill in your field names and table names in the below method of the form.

Private Sub Form_Load()
  Call loadSelfReferencing(Me.xTree.Object, "qryTreeView", "equipmentID", "parentID", "NodeText", IDNumber, Null)
End Sub

3)replace "qryTreeView" with the name of your query or make your query called "qryTreeView"
4) Replace "equipmentID" with the name of your primary key
5) replace "parentID" with the name of the self referencing parent key
6) replace "NodeText" with the text you want to see in your tree view
7) If the primary key is text replace "IDNumber" with IDText
8) The highest node should not have a parentID. But if it does replace "Null" with the value for the nodes at the highest level. In your example "PE" does not have a parent .

Thats it.
By the way I am a whole lot cheaper than Fneily. A six pack of Bud and a bag of chips should do fine.

 

[mitch37]

Ok I am onto the tree thing. I hope i know where to put this code...lol...

How about I send you some local SA beer - A Castle Lager and a bag of barbeque spare rib Simba Potato Crisps?

http://www.beerstore.com.au/beerstore/uploads/beerImages/Castle_Lager_L.jpg

Or better still, i am originally from Zimbabwe - Would You like A Cold Zambezi?

Not a big drinker myself...i seem to have become the 'goodie goodie' these days, although whiskey and water is my thing. soemthing simple like J&B or Bells. Johnny Black would do too...but it doesn't help the budget



What do you do for a living and where are you based?

 

[mitch37]

stupid question...i used your code in a form window...but how do i make the form code actually come up as a form?

 

[MajP]

If would probably do it another way because there are several modules, form code, and form objects that you need. Trying to take the code and rebuild the form would be difficult.

To test it out start with the database I made.
1)From that database select "file", "get external data", "import" and find your database
2)Select all of your tables, forms, reports, modules.
Now you have a new database with all of my code and all of your database objects.

Now either build a "qryTreeView" like I did using the same field names, or modify the Form load event.
3) Once you get the tree to load you then can modify the forms format, and play with adding or removing features.

 

[fneily]

Zimbabwe. Whoa. I just had a debate that Mugabe makes Ian Smith look like a saint. History points this out.
New price for Navan - $30 per bottle.
My liver collects good liquors.
"What do you do for a living..." - international jewel thief and maintain Santa's database.
MajP should get another star for his treeview knowledge.

 

[MajP]

Mitch,
The treeview is a great control for working with hierarchies, but it is labor intensive and somewhat complex. I have encapsulated most of the code so it takes very little to get this to work. However be advised if you go down this path there may be a significant learning curve to overcome problems.

 

[mitch37]

so sorry to be a nuisance but what is wrong with my DB? it half works

 

[mitch37]

Sorry i have added some proper data...if i leave parent ID blank, it puts it in the tree, but if i put data in, it does not. Is there no automatic way to link these things?
Attaching the most recent db

 

[MajP]

The parentID specifies the relationship
If Line1 is the root node then its parent ID is blank (null). If everything else belongs to Line1 then its parentID is 2333. You need to remove the zero in Line1 parentID, and but in ParentID for the other empty parentID.

EquipmentID	EquipName	ParentID
2233	        Line 1	
2968	        Blender Line 1	2333
2969	        Blender Line 1	2333
2970	        Blender Line 1	2233
2971	        Blender Line 1	2233

However, that does not look like much of a hierarchy since everything belongs directly to Line1, there are no other levels. A hierarchy would be more like

EquipmentID	EquipName	     ParentID
2233	        Line 1	
2968	        Blender Line 1	     2333
2969	        Bolt A               2968
2970	        Switch C	     2969
2971	        Diode x  	     2270

Line 1
- Blender Line 1
--- Bolt A
--- Switch C
---------Diode x

 

[MajP]

By fixing the ParentID I get this. Like I said, it is not much of a hier.