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.

 

[Remou]

Here is some interesting reading:

These may help:

http://blog.wekeroad.com/2005/04/08/cursed-recursiveness-querying-a-recursive-join/

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

This may be better:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

While the links refer to databases other than Access, nearly everything is relevant to Sccess.

 

[MajP]

Yes, but you need to work on your tables first. Your data does not seem normalized and does not support this heirarchry.

If I understand what you are asking.

Motor: MTR30001
is part of
Filler:123456
which is on
line: abcde
which is in
area: 18631

If you are going to do this in one table, then you need to make the table self referencing. You need to add a parentID to your table which shows what piece of equipment a piece of equipment belongs.

ID ParentID
18631
abcde 18631
123456 abcde
MTR3001 123456

Now I assume that MTR3001 relates all the way up to 18631 line Area.

If this is what you want to show the only way I know how to do this is build a custom function using DAO or ADO to return the Highest parent and then link on the value returned by the function.

 

[fneily]

Can't check out your post because it's a RAR file and I use the more common ZIP.
Interesting, I use an auto hierarchy example like yours to show my classes the difference between an hierarchical database and a relational database such as Access.
You'll have to redesign the whole system based on the rules of a relational database. A start:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Just a start.

Maybe you could post your table structures.

 

[mitch37]

Thanks guys

Will take a look at this later....busy training some employees today. will let you know how it goes!!!

THANKS again

 

[mitch37]

i am busy playing around with the database using the sites you guys posted. i will upload in zip format when i am done if i have problems. i am sure i will but practice is the best way to learn

 

[MajP]

IMHO a Sql solution is always the preferred solution over writing code, but in nested queries is a complex issue. If the

amount of equipment is relatively small (thousands of records) you can do this very easy with a function. Example:

id	name	        parentID

1	Car A	
2	TransmissionA	1
3	GearA	        2
4	TruckB	
5	AxleB	        4
6	WheelB	        5
7	BoltC	        8
8	PlateC	        9
9	PanelC

So GearA, belongs to TransmissionA, and goes in CarA
WheelB, is on AxleB, and is on TruckB
BoltC, is in PlateC, in PanelC

a function that returns the top parent

Public Function getTopParent(varID As Variant) As Long
  Dim rs As DAO.Recordset
  Dim parentID As Variant
  Dim strSql As String
  strSql = "Select parentID from tblEquipment WHERE ID = " & varID
  Set rs = CurrentDb.OpenRecordset(strSql)
  parentID = rs!parentID
  If IsNull(parentID) Then
     getTopParent = varID
     Exit Function
  Else
    getTopParent = getTopParent(parentID)
  End If
End Function

The function used in a query

SELECT A.name, B.name AS TopParentName, B.id
FROM tblEquipment AS A, tblEquipment AS B
WHERE (((B.id)=getTopParent([A].[id])));

The output

name	TopParentName
Car A	        Car A
 TransmissionA	Car A
 GearA	        Car A
 TruckB	        TruckB
 AxleB	        TruckB
 WheelB	        TruckB
 BoltC	        PanelC
 PlateC	        PanelC
 PanelC	        PanelC

using the same idea you can build a function to return the level of the hierarchy

Public Function getLevel(varID As Variant) As Integer
  Dim rs As DAO.Recordset
  Dim parentID As Variant
  Dim strSql As String
  strSql = "Select parentID from tblEquipment WHERE ID = " & varID
  Set rs = CurrentDb.OpenRecordset(strSql)
  parentID = rs!parentID
  If IsNull(parentID) Then
     getLevel = 1
     Exit Function
  Else
    getLevel = 1 + getLevel(parentID)
  End If
End Function

used in a query

SELECT A.name, B.name AS TopParentName, B.id, getLevel([A].[iD]) AS [Level]
FROM tblEquipment AS A, tblEquipment AS B
WHERE (((B.id)=getTopParent([A].[id])))
ORDER BY B.name, getLevel([A].[iD]);

output

name	   TopParentName    Level
Car A	        Car A	    1
TransmissionA	Car A	    2
GearA	        Car A	    3
PanelC	        PanelC	    1
PlateC	        PanelC	    2
BoltC	        PanelC	    3
TruckB	        TruckB	    1
AxleB	        TruckB	    2
WheelB	        TruckB	    3

If you notice these functions are recursive, they keep calling themselves until they reach the top level. Then they fall out.

 

[mitch37]

{fneily (Instructor) 1 Dec 08 8:24
Can't check out your post because it's a RAR file and I use the more common ZIP.
Interesting, I use an auto hierarchy example like yours to show my classes the difference between an hierarchical database and a relational database such as Access.
You'll have to redesign the whole system based on the rules of a relational database. A start:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Just a start.

Maybe you could post your table structures.
"""


Thanks fneily

I am going to attach the db in zip format. i have tried to make my relationships 'OKAY'....hmmmm

 

[fneily]

After looking at your database, some little stuff first: Don't use spaces in between words. May cause typos when writing code. Use a hyphen, eg. Equipment Number make Equipment_Number.
Don't use the word Name as a field name. Access uses that word.
Not sure what table SW1H is.
You use autonumber alot for your primary keys. I suggest you don't do that. Make up your own.

So you may want to construct your database thusly:
tblPerson
PersonID Primary Key
Fname
Lname
Position

tblEquipment
EquipID Primary Key
EquipName
EquipNum
EquipDescription

tblActions
ActionID Primary Key
ActionDescription

tblTeam
TeamID
TeamDescription

So those are your main tables. Now you want to track failures. You'll make a junction table:
tblFailures
FailID Primary Key
DateOfFailure a Date/time stamp. no need for 2 fields
PersonID who reports it
EquipID
Category

There may be more then one action to fix the problem. So you'll have a Failure Detail table which is in a one-to-many relationship with tblFailures.
tblFailureDetails
FDID Primary Key
FailID Foreign Key
ActionID Foreign Key
DateOfFix
PersonID Person who fixed it.
TeamID From your SW1H.
Summary

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

tblTeamMembers
TMID
TeamID
PersonID

 

[fneily]

Oops. Take TeamID out of tblFailureDetails. Don't need it - can connect to tblTeam.

 

[Remou]

You use autonumber a lot for your primary keys. I suggest you don't do that. Make up your own."

I have not had problems with autonumbers in later editions of Access, so this may be a little harsh, yesno?

 

[fneily]

As you know, when you delete records autonumbering justs keeps continuing in the sequence, the way it's suppose to be. But I've found this confuses beginners in Access. And there's nothing wrong with anyone creating their own primary keys 'cause it reinforces the notion of uniqueness or a record. At least that's what I found. So it's just my habit.

 

[mitch37]

fneily you are officially a hero in my book. i think it may be best for me to start from scratch and try build this thingy tomorrow. when you say i shouldnt use autonumber, are you saying that the user should enter a different number every time? or maybe i should actually try use something unique like a worker ID or an Equipment number?

Is there anything i can do to 'thank' you for your help?

 

[fneily]

Autonumber is up to you. It's just that, let's say, your records are using autonumber and right now you have 1,2,3,4.
Then you delete 3. The next number is 5, not 3 so now the records are 1,2,4,5. Now I know users don't usually see this field because relationships are "behind the scene". But if you just see 4, you can't tell what record that is. For example, I usually use a person's first three letters of their lastname and a number. So you have John Smith, John Smith and John Smith. Autonumber would be 1,2,3. Mine would be Smi1, Smi2, Smi3. The primary key is unique and belongs to someone with lastname beginning with Smi. It's just easier, I found in 11 years of teaching, for people to see relationships. Smi1 is a member of team Porsche(primary key). In autonumber, you'd see 2 is a member of 6.
An obvious example, here in the U.S. is SSN(Social Security Number). Again, it's up to you. And you make a good point, if you have an equipment number that's unique to that item and you create an autonumber, then you'd have duplicate data which is incorrect table design.
Been looking for some Cherry Grand Marnier. Not sold here in the U.S.. If you have it there, send me a liter or five.

 

[Remou]

There can be problems with using natural as opposed to surrogate primary keys, particularly in the case of SSN, where mistakes can be made, furthermore, such data should really be encrypted. I see your argument, fneily, but I would always prefer to use autonumbers and to hide the key from the user.

 

[fneily]

Remou - I have no problem with that. But you're an Access deity. Have a class of 20 Access virgins, or people in a company assigned to do an Access database with no prior experience, and show them how tables are related through primary and foreign keys. They can get zombie eyes. So we have no debate here.
Good point on the SSN. However, again from my experience, even at HCFA(now CMS)(Medicare/Medicaid agency), the SSN is rarely encrypted. Should be, but isn't.

 

[Remou]

Hmm, deity, virgins ... what happens next

 

[fneily]

Access to Bacchanalias!

 

[mitch37]

lol this thread is getting interesting. Cherry Grand Marnier? Lol...never heard of it but will keep a look out!!!

 

[mitch37]

http://www.navanworld.com/soon_to_come_1.htm