CODEWITHSUNDEEP
ms-accessms-access-2007
Prabhu
Prabhu

Reputation: 13355

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

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

Firstly, is this normal in Access? In SQl Server, I can have two relationships between two tables with no problem.

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

Thanks.

Upvotes: 4

Views: 9909

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

User_1, User_2 etc is the way Access aliases tables when creating multiple relationships. If you use code to create the relationships, you can choose your own names

Database.CreateRelation Method

But I do not know of any way to change the alias in the relationship window.

Upvotes: 3

FrugalTPH
FrugalTPH

Reputation: 573

Do you have to have referential integrity enforced at the table level? Can you just set up the relationships as a query? I'd guess that you can rename the 'alias' table if you do it via a query. In fact, you could just write the SQL and paste it right into an MS Access query.

I very rarely set up table level relationships in Access nowadays, and I also rarely even link forms through directly to tables or queries any more. I use unbound forms, populate them with code, and use code / DAO to control the updates of the relevant recordsets. All the behaviour of relationships, I then enact using SQL & VB as required.

Upvotes: 2

Related Questions