Access - Make Links Reciprocal
November 22, 2022 9:57 AM Subscribe
I have linked fields in access -- projects and partners. This partner is on this project. That project has these partners. But access doesn't know these are reciprocal relationships. Like I can on the projects table select which partners are on that project OR on the partners table select which projects that partner is on, but whichever I do, the other table won't reflect it. How do I make the tables reflect each other correctly? Feel free to point me, instead, to a youtube or linked-in learning course that will cover these sort of moderately-advanced access functions (or that will give me the correct language to google them).
Response by poster: Ok, I see that Data Integrity KIND OF does what I want. It creates a new column that is basically a back-link. But it seems to only do one-to-one. I need a many-to-many version of this since the same partner can work on multiple projects and projects can have multiple partners.
I don't think this is just a viewing issue. I think I need the correct data to feed into the main database table and keep it correct.
posted by If only I had a penguin... at 10:21 AM on November 22, 2022
I don't think this is just a viewing issue. I think I need the correct data to feed into the main database table and keep it correct.
posted by If only I had a penguin... at 10:21 AM on November 22, 2022
Do you know about join tables? Let's say you have a Partners table, with an id field, and a Projects table, with an id field. You then need a join table like PartnersProjects, containing partner_id and project_id, each of which is a foreign key to partner.id and project.id. Then each row in PartnersProjects represents a connection of one project to one partner, and you can have many partners connected to many projects (you can add constraints so each partner can only be assigned to a given project once, etc).
posted by Alterscape at 10:25 AM on November 22, 2022 [3 favorites]
posted by Alterscape at 10:25 AM on November 22, 2022 [3 favorites]
Response by poster: Hmmm....I have a vague memory of Join Tables from having worked with with MySQL years ago. I will watch some videos on those and see if that sparks some inspiration.
posted by If only I had a penguin... at 10:26 AM on November 22, 2022
posted by If only I had a penguin... at 10:26 AM on November 22, 2022
Yes, what Alterscape said is the correct relational database solution.
The idea is that the job of the Partners table is to keep track of information about Partner entities -- e.g. ID, name, job title, salary, whatever. The job of the Projects table is to keep track of information about Project entities -- e.g. ID, client name, start date, end date.
However, it's neither table's job to keep track of the links between Partners and Projects, because if it was, you'd run into the exact issue you mentioned -- if you want to be able to query in both directions, you'd have to store the mapping information in both tables, and then you have the difficult problem of keeping that information in sync.
So instead, you use a THIRD table, called a join table or a junction table, whose job it is to keep track of the relationships between Partners and Projects. Each row in the join table represents a single Partner <> Project mapping, and the table has two columns, Partner ID and Project ID. When you want to add a mapping between a Partner and Project, you add a row to the table. When you want to remove a mapping, you delete the corresponding row from the table. When you want to query which Partners are on a Project, or which Projects a partner is on, you run a query which combines the information in the ProjectsPartners table with the information in the individual Projects and Partners tables. (In SQL this is done using a JOIN statement; Access has equivalent functionality but I'm not sure what it's called.)
(note that all my experience is with SQL databases, so some of the terminology I've used here might be slightly different from that used in Access, but Access is a relational database as well so the basic principles are the same.)
posted by mekily at 12:24 PM on November 22, 2022 [1 favorite]
The idea is that the job of the Partners table is to keep track of information about Partner entities -- e.g. ID, name, job title, salary, whatever. The job of the Projects table is to keep track of information about Project entities -- e.g. ID, client name, start date, end date.
However, it's neither table's job to keep track of the links between Partners and Projects, because if it was, you'd run into the exact issue you mentioned -- if you want to be able to query in both directions, you'd have to store the mapping information in both tables, and then you have the difficult problem of keeping that information in sync.
So instead, you use a THIRD table, called a join table or a junction table, whose job it is to keep track of the relationships between Partners and Projects. Each row in the join table represents a single Partner <> Project mapping, and the table has two columns, Partner ID and Project ID. When you want to add a mapping between a Partner and Project, you add a row to the table. When you want to remove a mapping, you delete the corresponding row from the table. When you want to query which Partners are on a Project, or which Projects a partner is on, you run a query which combines the information in the ProjectsPartners table with the information in the individual Projects and Partners tables. (In SQL this is done using a JOIN statement; Access has equivalent functionality but I'm not sure what it's called.)
(note that all my experience is with SQL databases, so some of the terminology I've used here might be slightly different from that used in Access, but Access is a relational database as well so the basic principles are the same.)
posted by mekily at 12:24 PM on November 22, 2022 [1 favorite]
This thread is closed to new comments.
For reference: I am not a SQL pro but I've used it from time to time, usually in the context of data backing a web application of some kind or other. I think of a VIEW as a named query that you can save and recall easily. It does look like Access supports CREATE VIEW on Access databases, so maybe that's a way in?
Note: There may be a more Access-y way to do it, but that's how I'd approach it in a more fully-fleshed-out SQL server.
posted by Alterscape at 10:07 AM on November 22, 2022