hierarchy problem

[Bananabender]

Hello,

I'm a access newbie, used to work with excel.
I read some threads that most of the ones that want to start Access with excel experience like to do things wrong. I'm really trying to be open minded...

What I want is to set up an database for my CAD drawings.

To me the number of a CAD drawing is either an unique part(number), an assembly (of parts or other assemblies) belonging to a unique machine.

As a newbie to Access I strucle with the fact that the partnumber can be in different positions in the hierarcy.

it can be directly under the machine or be in different assemblies, see picture.


I've tried several setups, but I cannot get my head around it. Does anybody have a suggestion?

Thanx in advance, A.J.

[CJ_London]

no picture attached. To attach, click the insert image button and select the file. Copy/paste won't work on this forum

Also help if you clarify what the db is doing - is it storing a link to the cad drawing files? something else? perhaps identifying the relationship between a unique part and various assemblies? And can unique parts or assemblies be used in more than one unique assembly

Provide some examples of what a CAD drawing number looks like for a unique part and assembly.

By the sound of it you are wanting what is called 'recursion'.

[mike60smart]

Hi AJ
Welcome to the Forum
As CJ has suggested can you upload an example of how you record details of a specific CAD Drawing.

[Bananabender]

Hi, Thanks for the fast reply.

I indeed did a copy paste . The image should be visable now.

Now I keep track of my partnumbers in excel and sort of group them together in a extra row. In the beginning that was okay, but now that the file is growing the overview and search is getting a problem.
Because I want to start from scratch again all possibilities are open.

The structure from the image is what I came up with and is actually a simple version of what I really would like.

In the end I want the Access file to be my ERP, with customers, invoicing, stock, proccurement, etc. At the moment I also use Excel with Userforms and some VBA for a lot of that.

What I've got working until sofar is a unique customer list with different locations and to this location as I call it an installation.
An (unique) installation can exist of different (unique) machines.

From there I would like the structure of the image.
The problem I encounter is that the (unique) Partnumber can be directly under a machine but also in an assembly.

I've tried a junction table with more then two fields. That looked promising, but if there is no assembly the partnumber doesn't show up in the query a made.

At this time i did'nt think about the CAD drawing number yet... and yes a link to the drawing is the goal. But first things first.

[mike60smart]

Can you upload a sample Excel File?

[mike60smart]

Hi

You said "simple version of what I really would like."

Best to let us know exactly what you need.

I have the following layouts for Machine 1 & Machine 2 as per your diagram :-

Ignore my screenshots I made a small error on both

These are the correct versions

Machine 1 New & Machine 2 New

[CJ_London]

With respect you are all over the place. We don’t know your business, the way it works or what the app is required to do. You’ve now added in customers and ERP type functionality.

there are plenty of ERP examples on this and other forums. I recommend you investigate these and recursion - see similar links at the bottom of this thread.

It’s certainly possible - I’ve created a number of ERP applications over the years from small specialised businesses to multinationals. But typically there will be a large number of business rules that need to be documented and understood.

[ArviLaanemets]

It looks like you need some BOM list type of table to get all this together. On fly:
You need a table where all purchased, produced, and sold parts and materials (I myself prefer term Article) are registered. Like
tblArticles: ArticleID, ArticleName, ArticleType, ..., ArticleStatus
Whenever something is done at some workstation (machine, assembly line, or whatever), you get some source article(s) and do some operation(s) with them. After all those operations are done, all source articles are spent, and you get some another article - a produced one. This produced article must be presented in tblArticles, and it is either moved to another workstation as source article there, or to storage to use later, or to sell, in case this article is a final product;

Then you must somewhere register articles, for which you want to have BOM list registered. You can have either a separate table for this (e.g. tblProducts: ProductID, ArticleID, ...), or you can have in tblArticles a field, which indicates the Article registered as product;

You also need a table, where all units which to all this producing (machines, assembly lines when they can be counted as single unit, assembly workplaces when the assembly line can't be counted as single unit, etc.). I prefer the term Workstation.
tblWorkstations: WorkstationID, WorkstationName, ...;

Now you can cretate BOM lists for your products. Possible structures can differ - I'll describe one which will be easy to use. Let's have the table starting as
tblBOM: BomID, ProductID, RowNumber, WorkstationID, ...
RowNumber is a structured text field, where fixed number of character groups determine the position of row in BOM list, and the order how the product is produced. It may be composed from certain number of numbers (like with 2 numbers in every group '00' as product itself, '01' as to register the first source article added to get the final product, '02' as to register the second article added to get the final product, etc. for all 1st level components, '0101' as to register the first source article added to 1st article from level 1, '010101' as to register the first source article to 1st article from level 2, etc.). With 2 numbers in every group, you are limited to 99 source articles for every article at every level. I myself use 2 32-bit characters for every group instead - making this limit over 1000.
The fields for source article ID's, quantities, etc. are added, to make the tblBOM usable. and then you can fill the table, registering structures of all your products there (btw. as you see, BOM lists have reverse order - at top is what you get finally, and the starting steps are at bottom);

As I understood, you wanted to get a list of designs. I assume a design is attached to certain article, so
tblDesigns: DesignID, ArticleID, LinkToSource
Now you can create a query, which lists all designs needed for produce certain product.

PS. You can also have a table, where all operations possible on differnt workstations are registered
tblOperations, OperationID, WorkstationID, OperationDescription, ...

And then, you can have another table, where for every row of BOM list, all operations made to produce an article there are listed
tblBOMOperations: BomOperationID, BomID, OperationID, ...

[Micron]

Have never developed an ERP so can't help there but also don't see how a BOM fits the requirement, which I understand is to create a drawing db. Maybe I missed the point. IMO if you had an equipment location table you would simply link the drawing to the location. By location, I don't mean simply physical locations; it's just what we called it.
This method allows you to associate physical items as well as other entities to any place in the hierarchy. Those other "things" can be costs/expenses, safety related items such as safe work permits, accident data, whatever you want. An example of a location string might be (to the best of my recollection)
KENL.NPSS.TRUC.0165.STER which translates to
- physical property location
- non production sales or services
- Truck Garage
- unit number
- steering system

You could have as many levels as you need but at some point it should be enough to work off of a list of drawings. For example, If I wanted to locate a drawing for L2 steering bogey assembly it would be at the last level but there would be a list of drawings because there are several of these assemblies. Maybe I'm out to lunch, but shouldn't a drawing that is comprised of items for which there are other drawings have a list of those drawing numbers as part of the drawing? So you'd have a way to locate those drawings, not by location but by drawing number shown on the drawing?

[madpiet]

oh ouch, if you need to do recursion in Access, it's gonna be painful. I think I did something like that a long time ago to sort out courses and their prerequisites, and it was not fun at all. I had to use cursors/recordsets to do it.

If you have the option, do it in SQL Server or Oracle, because they support recursion. (Look up "Common Table Expressions" and "BOM" or "bill of Materials". but maybe Allenbrowne.com has an example of how to do it in Access/VBA if you're stuck with it.

[CJ_London]

if talking about recursion - good link here
https://www.access-programmers.co.uk...part-2.327904/

[Bananabender]

Thank you all,

[Bananabender]

Thank you all,

I've not should have mentioned my wish about what I like the Access Db to do evntually, It's irrelevant at this point. I'm sorry for the confusion.

The start is getting my drawings sorted out adressed to the machines as shown in the hierarchy picture.
As I understand it is rucursion I need and that is not for beginners like me, unless you are willing to invest a lot of time to create a steep learning curve.
I underestimated Access and thank you for making that clear to me.
Maybe it is better I look for some standard programm, or hire a database specialist.

greet A.J.

[CJ_London]

I've not should have mentioned my wish about what I like the Access Db to do evntually, It's irrelevant at this point.

It is always relevant - otherwise you may go in a particular direction ow, only to have to backtrack or make significant changes when you start another 'phase'. If you have a good idea what those other phases are going to look like, at least in respect of their reference to your drawings, you can design accordingly.

If you know the maximum depth you need to go - in your example, machine 1 has a depth of 4 and machine a depth of 2, you can avoid recursion and just use sql

[ArviLaanemets]

The start is getting my drawings sorted out adressed to the machines as shown in the hierarchy picture.

In case this is you want really at the moment, then all you need, is
tblWorkstations: WorkstationID, workstationName, ...;
tblDrawings: DrawingID, DrawingName, DrawingLink, ...;
tblWorkstationDrawings: WorkstationDrawingID, WorkstationID, DrawingID

You need a single form based on tblWorkstations, with unbound combo to select a workstation, and controls to display info about selected workstation
You need a continuous form based on query which links info from tblWorkstationDrawings and tblDrawings (tables are joined by DrawingID), and you ave to add this form into main (workstations) form for as subform. WorkstationID field is user to link both forms.

Whatever workstation you select, all drawings linked to this workstation are displayed in subform as subform rows. The subform row must have a textbox control which has DrawingLink as source, and on click must open the linked drawing.
The subform will also work as tool to link new drawings to workstation. You add a new row, and the control linked to WorkstationID is automatically filled with WorkstationID value from main form. You need a button in subform, clicking on which runs a script, which allows to select a drawing from some folder (preferably in your LAN), and updates the link in DrawingLink field of tblDrawings, and then refreshes the subform.