How to use one table twice in another table. (1 Viewer)

SailorMade

New member
Local time
Today, 02:50
Joined
May 18, 2015
Messages
2
I'm making an registration db, there every user registrate them self in an registration form. This registration is forming the user table that I use in an other table as a lookup for the users calculated surname and last name.
I need to use this user table twice in the same table, in other words you need to lookup two different users in the other table. So the primary key in the other table will have relations to two users at the same time.


One solution, is that the users have to register two times in two user tables user table1 and user table 2, but that seems pretty awkward.
Another solution may be to copy user record from user table 1 into user table 2 by using VBA. If so, then how to make the code in VBA?
Therefor I wonder if there is an other more simple way to accomplish the outcome?
 

ButtonMoon

Registered User.
Local time
Today, 09:50
Joined
Jun 4, 2012
Messages
304
It's perfectly OK to have more than one relationship between the same pair of tables. You don't need to do anything "special" to achieve this. Here's an example of my own where the Users table is referenced twice:

CREATE TABLE Marriage
(Husband INTEGER NOT NULL REFERENCES Users (PersonId),
Wife INTEGER NOT NULL REFERENCES Users (PersonId), ...);


Unfortunately Access does a poor job of displaying this in the Relationships Window. The Users table will be shown twice, once as Users and a second time as Users_1. That doesn't mean you did anything wrong, it just means Access isn't very good at drawing useful database diagrams!
 

stopher

AWF VIP
Local time
Today, 09:50
Joined
Feb 1, 2006
Messages
2,396
Hi

I can't quite tell from your description if you are talking about a one to many relationship or a many to many relationship.

Suppose you are talking about StudentRegistration and StudentCouncelor. Then one StudentCouncelor is responsible for many students (StudentRegistration). In this case, the StudentCouncelor PK would appear in the StudentRegistration table as a foreign key thus allowing many students to relate to one councelor.

Suppose you are talking about StudentRegistration and Course. Then each student can be enrolled on many courses and each course can have many students. This is Many to Many. To implement this you need to create a third table, often called a Junction table. In this example I would name it Enrolment but you could name it StudentCourse (a combination of the other tables). This table has the PK from both the StudentRegistration tables and also the Course table which are foreign keys to the respective tables but also together form the PK for the Enrolment table. Google database design junction tables if this is your case.

hth
 

stopher

AWF VIP
Local time
Today, 09:50
Joined
Feb 1, 2006
Messages
2,396
It's perfectly OK to have more than one relationship between the same pair of tables. You don't need to do anything "special" to achieve this. Here's an example of my own where the Users table is referenced twice:

CREATE TABLE Marriage
(Husband INTEGER NOT NULL REFERENCES Users (PersonId),
Wife INTEGER NOT NULL REFERENCES Users (PersonId), ...);


Unfortunately Access does a poor job of displaying this in the Relationships Window. The Users table will be shown twice, once as Users and a second time as Users_1. That doesn't mean you did anything wrong, it just means Access isn't very good at drawing useful database diagrams!
I did wonder if the OP was talking about mutilple relationships in which case I'd go you what you say. I'd add that you can drag and drop the same table onto the relationship screen twice to create the alias table. The alias table can also be renamed to more sensible text thus avoiding the underscore defaults.
 

Solo712

Registered User.
Local time
Today, 05:50
Joined
Oct 19, 2012
Messages
828
I did wonder if the OP was talking about mutilple relationships in which case I'd go you what you say. I'd add that you can drag and drop the same table onto the relationship screen twice to create the alias table. The alias table can also be renamed to more sensible text thus avoiding the underscore defaults.

You and Button are way more optimistic about the OP than I. I simply read it as word salad that hopelessly confuses basic terminology (table, form, primary key). Just read the OP heading and tell me without blushing you understand it ! :rolleyes:

Best,
Jiri
 

Users who are viewing this thread

Top Bottom