Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2015, 03:04 AM   #1
SailorMade
Newly Registered User
 
Join Date: May 2015
Posts: 2
Thanks: 4
Thanked 0 Times in 0 Posts
SailorMade is on a distinguished road
How to use one table twice in another table.

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?

SailorMade is offline   Reply With Quote
Old 05-18-2015, 04:03 AM   #2
ButtonMoon
Newly Registered User
 
Join Date: Jun 2012
Posts: 304
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: How to use one table twice in another table.

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!
ButtonMoon is offline   Reply With Quote
The Following User Says Thank You to ButtonMoon For This Useful Post:
SailorMade (05-21-2015)
Old 05-18-2015, 04:12 AM   #3
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: How to use one table twice in another table.

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 is offline   Reply With Quote
The Following User Says Thank You to stopher For This Useful Post:
SailorMade (05-21-2015)
Old 05-18-2015, 04:17 AM   #4
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: How to use one table twice in another table.

Quote:
Originally Posted by ButtonMoon View Post
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.
stopher is offline   Reply With Quote
The Following User Says Thank You to stopher For This Useful Post:
SailorMade (05-21-2015)
Old 05-19-2015, 06:10 PM   #5
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 739
Thanks: 14
Thanked 126 Times in 123 Posts
Solo712 will become famous soon enough
Re: How to use one table twice in another table.

Quote:
Originally Posted by stopher View Post
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 !

Best,
Jiri

__________________
Dyslexics of the world untie !
Solo712 is offline   Reply With Quote
The Following User Says Thank You to Solo712 For This Useful Post:
SailorMade (05-21-2015)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Append Data from Table A + Table B to Table C cooh23 General 3 05-11-2015 08:24 PM
Populate table name in sep created table, each time a table is added snoopydoopy Queries 1 10-23-2014 04:43 AM
form for table A to search fields from table B and populate into table A johnseito General 16 08-06-2013 06:25 PM
Relationships between table - 3 fields in 1 table related to another same table laiching Forms 2 01-16-2012 06:20 PM
[SOLVED] Naming a table created with a make table query from a field in the table? AllanN General 4 07-03-2002 04:52 PM




All times are GMT -8. The time now is 08:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World