How to create multiple relationships between two identical tables in MS Access?

I have a Users table and a Reviews table. The ReviewedUserId and ReviewerUserId review table are both foreign keys that point to the primary key of the User (UserId) table. When I try to create a second relationship in Access between the User table and the testimonials table, it creates a second users and names table at Users_1.

First, is this ok in Access? In SQl Server, I can have two relationships between two tables without issue.

Second, is it possible to rename this alias table so that it doesn't need to be called Users_1?



source to share

2 answers

Do I need to have referential integrity at the table level? Can you just set up the relationship as a request? I would suggest that you can rename the "alias" table if you do it with a query. In fact, you can just write SQL and paste it directly into your MS Access query.

I very rarely set up table level relationships in Access these days, and I rarely even bind forms directly to tables or queries. I use unrelated forms, populate them with code, and use code / DAO to manage updates to their respective recordsets. All relationship behavior, then I use SQL and VB as needed.



User_1, User_2, etc. is how Access aliases tables when creating multiple relationships. If you are using code to create relationships you can choose your own names

Database.CreateRelation method

But I don't know how to change the alias in the relationship window.



All Articles