Presentation of links between table entries (in self-referential table)

[Aeonat]

Hi,

I have a very simple database I am throwing together which has one table with three fields: Ref, Type, Related

Ref is a list of the unique codes for drawings. Type is the type of drawing (plan, cross-section, detail). Related is a multi-entry field which refers back to Ref, so I can show which other drawings are referred to by this drawing (for example a plan drawing may have a couple of cross sections through it).

I am very open to re-organising the database to make this work, but what I want to do eventually is:


- show a diagram with every drawing reference and the links to other drawings (in the style of a relationship diagram, or similar)
- if impossible, I would like to be able to pick a drawing and find all other drawings which are related to it (both directly and indirectly; so if a plan has two cross sections, and each cross section shows two details, then I would like to be given a list of 7 drawings when querying any of those 7).

I would very much appreciate your help; I am fairly new to access so have been struggling with my search terms, so I apologise if this has been asked before.

Thanks in advance,

Aeonat

[aytee111]

The easiest way would be to make another table for the Related field so that one Ref can point to multiple records on that new table. Then for all your searching you can search on the one field in the second table and display any variation of drawings.

Don't get this:

so if a plan has two cross sections, and each cross section shows two details, then I would like to be given a list of 7 drawings when querying any of those 7

Are you missing a plan table?

[Aeonat]

Don't get this:

Are you missing a plan table?

Sorry, perhaps that was a bit misleading - whether they are plans, cross sections or details is irrelevant to the problem since the Ref value is unique. The point was simply that drawings refer to other drawings which themselves refer to other drawings - so looking up one Ref value will not give me a complete list of related drawings.

[aytee111]

So there will be a table of unique Refs which are all the drawings. The Ref will be the primary key and it will contain type. Then there will be a table of Related, containing the main Ref and pointing to the related Ref. This will have an autonumber primary key. When you search on a drawing use this second table and look in both Ref fields - either it will be a main Ref will all its related or it will be a related itself. From the main Ref it will be all the drawings associated with the search item in a downward direction, or it will be the secondary Ref and will show all the main Refs it is associated with, in an upward direction.

Entering the data will be in a downward direction, adding all the drawings associated with the main Ref.

[Aeonat]

Thanks for the help! I don't fully understand however - I have created the tables so I have:

[Main]![Ref]
[Related]![Ref]
[Related]![Related] which points back to [Main]![Ref] with multiple entries

I then set up a query on field [Related]![Related.Value] with criteria:
=[Main]![Ref]
or=[Related]![Ref]

But get a data type mismatch error, presumably since Related.Value is a subset of a list, which is not the same type as a simple Text field.

[aytee111]

No lists, all fields will be a direct pointer to the other table so the data types will be the same.

Ref 1 points to Ref 2 and Ref 3 - two records
Ref 2 points to Ref 3 and Ref 4 - two records
So a search on Ref 2: as main ref will bring up ref 3 and ref 4, as related ref will bring up ref 1

[Aeonat]

Thanks Aytee111, I got that working, but unfortunately, the links don't go on indefinitely to define the full tree of information.

I think I found a solution however - if I have the Ref and Related Refs (as a list field), I can then query everything using [Ref] and [Related Ref].Value and import it to Fusion Tables (https://support.google.com/fusiontables/answer/2571232) where I can add a Network Chart to visualise all of the links.