Link multiple records in same table (1 Viewer)

tomasm

New member
Local time
Today, 05:22
Joined
Nov 20, 2017
Messages
18
Hello,

I have a table of projects and I am trying to link some of the projects together. For example, if I am viewing project 1 on a form, I want to see projects 1a, 1b, and 1c listed as related projects. 1a, 1b, and 1c are in the same table as project 1. I also want to see 1a, 1b, and 1c listed on the form when viewing project 1. What is the best way to set up this type of relationship? And how can I enter related projects when adding a new project using a form?
 

isladogs

MVP / VIP
Local time
Today, 12:22
Joined
Jan 14, 2017
Messages
18,186
From the way you've described this, it sounds like you have a master or 'parent' project and subsidiary or child projects.

You could add 2 fields to your table:
- Parent - Yes/No
- ParentName - text

So Project 1 would have Parent = True and ParentName blank (as it's the parent)
Projects 1a/b/c etc would have Parent = False & ParentName = Project1

Then when you select Project1 a list box can show all records with the ParentName field = Project1

Hope that makes sense to you
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 19, 2002
Messages
42,981
The table structure is incorrect. There should be one table with the "1" part of the key and any columns that relate to the "parent" record. Then the child record would have a foreign key pointing to the "1" part and it would have a second field that contains the "a" part.

I prefer autonumber primary keys so I would use an autonumber PK for the child table but in order to enforce the business rules of the relationship, I would add a unique index comprised of the two fields. The Child table would contain what ever columns are dependent on the letter part of the ID field.

If you elect to stick with a single table, you will end up duplicating data in several rows which leads to data anomalies.
 

tomasm

New member
Local time
Today, 05:22
Joined
Nov 20, 2017
Messages
18
Yes, I want to show parent/child relationships due to split and combined projects. How would this look on a form? If there was only one child project that would be a simple text box to manually type in a number but how do I handle multiple entries?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 19, 2002
Messages
42,981
You would use a main form to show the parent record and a subform in DS or continuous view to show the child records.
 

tomasm

New member
Local time
Today, 05:22
Joined
Nov 20, 2017
Messages
18
3 months later....

I created a second table with a field for projects linked to the main table projects field and another field displaying related projects (similar to an orders/products many to many relationship). The second table is added as a subform so now I can enter and view related projects and other information via this subform.

However, this solution does not address a particular scenario:

I have project 1 on the main table and add project 1a as a related project via the subform. Project 1a is not yet an active project and therefore is not added to the main table. Later, when project 1a is added (via the main form) to the main table I will not know that project 1a was previously related to project 1. In other words, I want to somehow see that 1 and 1a are related when I add 1a to the main form.

Since I can't think of how to do this, I thought of creating a search button that would allow me to search for "project 1a" across both the main table and related projects table. But so far I haven't found a way to search multiple tables at once.

Any tips or suggestions is appreciated, thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Jan 23, 2006
Messages
15,364
eccoboy,
I think part of the issue you are facing is a clear understanding of the requirements.
It seems you have some conditions that relate to a project being "initialized" and another "status" type field indicating "active". So a Project isn't necessarily a Project. You appear to be separating these Projects by status and storing them in different tables.
You know you environment and business better than readers, so it would be helpful to you and us if you further described a Project and how it evolve from "initial idea" through to active project.
If, as it seemed earlier, you have a main/master project and a variable number of related "child records/projects", then 2 tables and a form/subform construct would be the typical Access set up (where the main project[the 1 side] is on the form and child records [the many side] on the subform).

What exactly causes Project 1 to be related to 1a and 1b?
 

tomasm

New member
Local time
Today, 05:22
Joined
Nov 20, 2017
Messages
18
Sorry, "active" is the wrong word. I was trying to simplify.

I have a list of construction projects and sometimes they get split or combined. When split, I'll have two or more new project numbers. These new project numbers may be added to my list or not (I'm only tracking certain projects, depending on how they are funded). So one of these child projects could be added to my list immediately, in the future, or not at all. When combined, I'll have two or more projects on my list combined to create a new project that, again, may be added to my list immediately, in the future, or not at all.

The previous setup was one table of project numbers with a notes field. If a project was split/combined then any project numbers involved were added to this notes field as plain text. So when searching the table for a project number you could not only see if a record already exists, but also could see if the project in question was part of any split/combine.

I'm trying to get rid of this notes field and make this into a proper database. Hopefully my description above makes sense.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Jan 23, 2006
Messages
15,364
I'm attaching this link to info on database planning and design. I recommend you work through a tutorial (or 2) identified by the RogersAccessLibrary in the link. You will get a better appreciation of the benefit of writing down your requirements and building a model.
The other info is all good reference material.

Good luck.
 

Users who are viewing this thread

Top Bottom