Relationship Links from one record to another in the same Table? is it possible?

[paul61]

Hi there,

Please could I ask for your advice/suggestions on how to create a Link between two records in one Table? I currently have links between 3 tables (see attached Relationship screenshot). But I want to merge the 3 Tables into 1, but keep the functionality to add, edit and delete the link if required.

Currently, my database has 3 separate Tables which are linked as per the attached Relationship screenshot.
tblManufacturer / frmManufacturer
tblDistributor / frmDistributor
tblOtherSuppliers / frmOtherSuppliers



The most useful function is that the User can create a Link (via a dropdown menu on a SubForm which is on frmManufacturer) to show a list of Distributors for that particular Manufacturer.

There are many records to view and amend, but the new Users do waste a lot of time looking for which Form to open to find a particular company. So, I want to merge the 3 Tables into 1 large Table so that all records are found in the one and only Table. But I still need the function to be able to add and amend any Link between any two companies.

Is it possible to make a relationship link between two records on the same Table? Or if not, are there any suggestions on how I can make this work with one Table?

Thank you very in advance for any suggestions.

Stay safe.
Paul

[CJ_London]

Bit confused about what you are trying to achieve and you’re proposed solution of one large table

Why not just use a combo box?

But to answer the question about a record in a table related to another record in the same table, this is easily done and quite a common requirement

If it is a one to many relationship just include a ‘parentFK’ field in your table. Otherwise you will need a separate link table for a many to many relationship

[Micron]

not sure I understand as this doesn't make sense

Users do waste a lot of time looking for which Form to open to find a particular company.

You should only have 1 form for any particular function - you have one form that you populate as required, based on some sort of user action. Same goes for queries and reports. Plus you don't create links on the fly so not sure what that's about. Maybe you're referring to related records, but that is handled more or less in the background if your design is correct.

You will cut out a lot of questions if you can copy, compact, zip and post a db sample here. If need be you can consider randomizing sensitive data

https://www.accessforums.net/showthread.php?t=77482

[orange]

Paul,
Can you tell us more about the application? What do the tables represent? What is the "linkage" you need and what is it that the users need to create these links?From a database perspective, collapsing meaningful entities into a single table (could be supertype arrangement) is not necessarily a good strategy.
However, we need more info on the business and processes to understand and advise.

Please describe - in simple English - an overview of the Business and your need/use of manufacturers, distributors and suppliers.
I note there is no product or service involved - so am a little confused on the business and needs.

[paul61]

Firstly, thank you to Ajax, Micron and Orange for taking the time to review and comment on my issue. That is really appreciated so thank you again.


I have attached a zipped copy of my db which may, or may not, be self explanatory!!!


But basically, the User is inputting details of different companies (some are Manufacturers, some are official Distributors (of those Manufacturers) and some are just random Other Suppliers.


I will need to know who are (for example) the Distributors of a Manufacturer? This is currently achieved by the following actions:-
1a. Accessing frmManufacturer. Finding the Manuf by using the top combo box. Using the combobox on the Dist Sub Form to insert the Dist.
Or this can also be done by
1b. Accessing frmDistributor. Finding the Dist by using the top combo box. Using the combobox on the Manuf Sub Form to insert the Manuf.


So, I want to merge the 3 Tables (Manuf,Dist,Supp) into 1 Table. But am not sure if/how if the Links between say, Manuf & Dist (can be achieved).


Please do let me know if this is enough detail to understand the issue. I didn’t want to write paragraphs and paragraphs to bore people.


Thanks again.

[Micron]

At first glance it appears as I mentioned - IMO you have too many forms and tables for what you are doing. However, I'm not sure to what level as I don't know the nature of the business. Certainly only 1 table for contacts I would think, not 2. On one hand I'm wondering why you care if B is a distributor for A - especially since these relationships are not under your control and you're not likely to be notified when they change. On the other hand, your business may need to have these relationships defined somewhere. Maybe what you should have is one tblSuppliers with all company names in it (or tblCompanies if you prefer). To track if B is a distributor of A and B is a supplier to C you'll likely need junction table(s). tblSuppliers would mean 1 form for all companies as you seem to be asking for but with subforms to create the business relationships if need be.

Your first post lists the tables you want to combine, but there are 2 tables in between - tblManufacturerDistributors and tblManufacturersSuppliers (slightly different spelling on manufacturer, which might cause issues when coding) so what happens to those?

Perhaps you should provide a treatise on what the business process is that this db is going to support - that is, if you want advice on whether or not you're on the right track with design. If you've already provided that in another thread, then maybe cut and paste here or provide a link to it. If such advice is not desired, then the answer to this

Is it possible to make a relationship link between two records on the same Table?

is yes. To do that properly you'd do so in a junction table, not the main table. AFAIK, there is no way to do this in a main table without repeating all field data (e.g. company name, address, etc.) and you wouldn't do that, right? Last but not least, you have committed spreadsheet-itis on tblManufacturer and other tables by repeating like fields (address, country, for example).

You probably don't want to be told this, but I think the right thing to do is start over - perhaps while users continue with the current db. The more they ask for in terms of capability, the more difficult it will become to implement changes and you'll likely be posting here for solutions to problems that arise because of design.

[orange]

Paul,

A manufacturer builds/creates/produces a Product (or Item).
It would seem that if A manufactures a widget, and B and X are distributors of that widget to others.
A Manufacturer often requires raw materials, components, sub assemblies from supplier(s) S, K and W to produce widgets.

And A, B, X,W,S,K are all companies.

So whatever business processes you have that requires these companies and their categorization is critical for readers to assist with database structure, relationships,....