Looking for some direction. (1 Viewer)

[Sluggo]

Good morning, new member here.

I was hoping someone could help me with an example or terminology for a database that keeps records of the serial numbers that have gone into the equipment that we assemble.
Since the equipment has several sub assemblies; graphically this would look like a tree diagram with a root, branches (up to 6 deep), and ~300 nodes.
We currently store this data in a flat file (excel) but this has the major drawback that it is not easy to swap or replace nodes should service occur on the equipment.

Please let me what other additional information would be helpful. I can not post the flat file for privacy reasons.

Thanks,
Joe

 

[Minty]

You can use a Treeview in Access, which might be the perfect tool for this type of BOM with many levels.
Have a read here:

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

Edit: Welcome to AWF by the way!

 

[Sluggo]

I will look into this.
Thank you for the quick reply.

 

[The_Doc_Man]

Hierarchical databases have been brought up before. You might try the link in the "Similar Threads" list from MajP. He has some pretty good experience with that kind of thing.

 

[CarlettoFed]

You could attach the file by replacing the sensitive data with other random ones, this to have a precise vision of what we are talking about.

 

[MajP]

Most recent one I have assisted on

Solved - Help to build a treeview

But I don't know like you. If you prefer, act freely and spontaneously. I still have a lot to learn in this world of programming

www.access-programmers.co.uk

 

[Sluggo]

Here is some sample data that has been stripped down.

docs.google.com/spreadsheets/d/1PZieyPIjWKgaTLCMVLoWuDPNf5rR68EOOUxGEG0A1uM/edit?usp=sharing

Notes:
- All of our parts are given a 6 digit part number. Typically following is a Revision, Batch Number, and ID to which generates a unique serial number for each item. (I've used five random numbers here)
- All of our data is currently in hard copy and is being digitized. But being equipment that can have parts replaced means that the entry is only accurate at the time of entry.
- Row 1 is a generic grouping.
- Row 2 is the title of the each assembly.
- Row 3 is the part number of the given assembly.
- Row 4 contains the part numbers of the children to the Row 3 assemblies.
- Row 5-10 are example entries.
- Column A is a variant of the whole product. (But is defined by column B)
- Column B is the part number and serial number for the Root Assembly.
- Column C is the date the row was entered.
- Column D,G,P, etc are just part numbers for the documentation/records (hard copies)

- The sheet is formatted this way as it followed the ordering of the hard copies and made data entry easier.

- The relationships can be difficult to follow as there appear to be many broken links, some parent/child relationships exist only because of the physical location of the hard copy (same binder).
- I've tried to highlight some of the relationships as an example.
- Column Z is assembly "824963 - Stage 3", it contains assembly "825168 #####" -> Column FO shows the sub components of assembly "825168".

 

[CarlettoFed]

From the excel file it is certainly not possible to understand much how a device is managed, perhaps you should show it in tree format.

 

[MajP]

I cannot make heads or tails out of that either. Do something in an Excel table that looks like this. Add additional columns if there are other pieces of information to display per node.

Solved - Help to build a treeview

Bom dia. Primeiramente peço desculpas pela linguagem. Como alguns de vocês sabem, sou médico e estou tentando desenvolver um APP em MS access para o meu dia a dia. Existem duas classificações em nosso meio, que são: 1) a Classificação Internacional de Doenças versão 11 (...

www.access-programmers.co.uk

 

[Sluggo]

I am currently drawing up the tree diagram in a program like Visio.
This will help me show management where we would need to make document changes in order to fix any broken relationships.
I will share it here as soon as I can.

 

[MajP]

You only need to ensure that an entity has a parent ID key. It does not need to store more information about its relation farther up in the hierarchy. See the first thread referenced by @Minty. In that I discuss storing hierarchial data.

 

[Sluggo]

Here is what the hierarchy would look like graphically. (Starting in the top left)

Each box is an assembly of serialized parts.
The header contains: Assembly Part Number, Assembly Name, Record Document Part Number

Below the header is the list of serialized components that make up the assembly.
Red - Serialized part with no sub components.
Blue - Assembly with serialized sub components.
Green - Assembly with serialized sub components. (But there is no input on the record to make the relationship, this is for my management.)

 

[Sluggo]

You only need to ensure that an entity has a parent ID key. It does not need to store more information about its relation farther up in the hierarchy. See the first thread referenced by @Minty. In that I discuss storing hierarchial data.

The hierarchical data storage does seem like the best option in this case, but it also appears that Access does not have a lot of native functions for this type of structure.

Is it correct that most of the functions would have to be done in code?
As in little to no SQL, no report/query wizard?
My coding isn't great.

 

[Pat Hartman]

This is not as hard as you think it is.
Basically, you have ONE table with product information. ALL products get added to this table. You need some attributes that define whatever the color-coding is explaining. Then you have a SECOND table which is a junction table that joins the product table twice with the parent part and the child part. This allows parts to have a m-m relationship so that any given part can be used in a different assembly. You have to mark parts as end items, assemblies, details as you add them. This will help with restricting the combo on the popup form as you create the relationships.

You have to add all the parts individually before you can start defining the BOM so rather than using a subform to define the child records, I would use a popup form. Once all the parts are added, start at the top and work your way down to define the relationships.

The maintenance is a standard mainform with a subform or popup whichever you prefer. You work, two levels at a time. Then you use the TreeView for the pretty display of the BOM you posted. If you use a popup rather than a subform, you might be able to add navigation features that allow you to work through the BOM more efficiently.

I don't have any db sample to post. The last time I worked with a BOM, my BE was IMS DB which was IBM's hierarchical database. The BOM for Boeing's 747 turned out to be remarkably shallow. It was a long time ago but I think it only went 9-10 levels deep. That was probably because we were dealing with an assembly BOM so many of the assemblies were "purchased" or built off-site and so didn't need to be exploded.

 

[Sluggo]

In the other thread "Hierarchical Data, Recursion, Tree-View, and a another Great Custom Class" there is a number of examples that MajP has made.
Is the download from post #137 (Version 15) a good example, or is there a more stable version?

 

[Sluggo]

This is not as hard as you think it is.
Basically, you have ONE table with product information. ALL products get added to this table. You need some attributes that define whatever the color-coding is explaining. Then you have a SECOND table which is a junction table that joins the product table twice with the parent part and the child part. This allows parts to have a m-m relationship so that any given part can be used in a different assembly. You have to mark parts as end items, assemblies, details as you add them. This will help with restricting the combo on the popup form as you create the relationships.

This sounds slight different from the example given by Minty & MajP, using 2x tables instead of 1x.

 

[MajP]

I went through the thread and got rid of the multiple code examples. Here is a better version 15 (should have called it 16)

https://www.access-programmers.co.uk/forums/attachments/treeview-demo-v15-zip.107109/

This sounds slight different from the example given by Minty & MajP, using 2x tables instead of 1x.

There is a reason for this and depends on your data. Are your parts all serialized or not. You say they are, but your diagram makes that uncertain.

If items are generic (not serialized) then you may have a many to many situation where two tables is needed.

This is because a generic wheel can be assigned to many assemblies because it simply identifies that a wheel will be used, but a serialized wheel represents a discrete item and can only be assigned to a single assembly.

You say all your items are serialized

Batch Number, and ID to which generates a unique serial number for each item. (I've used five random numbers here)

The diagram shows a part number with a quantity ( for example X2). So that does seem more like a generic part.

 

[MajP]

Since the equipment has several sub assemblies; graphically this would look like a tree diagram with a root, branches (up to 6 deep), and ~300 nodes.

FYI. That demo cited in thread 6 is 30K nodes up to 8 levels deep. I have another demo of 50K nodes up to 200 levels deep. I learned an important trick in that long thread and you never want to load the whole tree at that size. My code allows you to do a Full load or Light Load. The full load for small trees loads all the nodes. The light load only loads the root nodes then loads the immediate child nodes only when a branch is expanded.

 

[Sluggo]

Thanks for the feedback MajP.

All of the parts are uniquely serialized.
We are not concerned with recording non-serialized parts.
The (X2) does mean there are two of the same part, but they are uniquely marked.
I was just condensing the lists.
There are still issues on the diagram. This is because it is halfway from where we are, to where we want to be.
There should be no instance where I need a m-m relation, so this clarifies things. Thank you.

I tried the V15 that I pointed to and notice the long load time of ~1000 nodes.
Will try the new version you linked.
I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries.
Too many mouse clicks.

I will explore this and let you know how it goes.

Thank you all.

 

[CarlettoFed]

How to interpret these writings
824761|826233|826234
Stage Final
826877
How to interpret these writings
824762|826230
How to interpret these writings
824771
DI
825708