tables

[Petrus VAN DEN cRUYCE]

I have a database in witch i have different tables: companys, persons,
In company i have a field president, a field, chairman and the those
persons already have their data are in persons.
I made a link between the two tabels on the field president and now i
want to fill the field president with a link to the table persons
What 's the best way to do this?

 

[John W. Vinson]

I have a database in witch i have different tables: companys, persons,
In company i have a field president, a field, chairman and the those
persons already have their data are in persons.
I made a link between the two tabels on the field president and now i
want to fill the field president with a link to the table persons
What 's the best way to do this?

Your table structure is WRONG.

Fields are expensive - records are cheap! The correct structure for this is to
have a field for the role of the person in the Persons table. This could
contain either the person's role - President, CEO, CFO, IT Director - or a
link to a small table of Roles. If you wish to see the company information and
the president's data, you would create a Query joining the two tables with a
criterion selecting the desired role.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Petrus VAN DEN cRUYCE]

Op 25/01/2012 17:23, John W. Vinson schreef:

Your table structure is WRONG.

Fields are expensive - records are cheap! The correct structure for this is to
have a field for the role of the person in the Persons table. This could
contain either the person's role - President, CEO, CFO, IT Director - or a
link to a small table of Roles. If you wish to see the company information and
the president's data, you would create a Query joining the two tables with a
criterion selecting the desired role.

Thanks for the the answer. But the same person can have more than one
role in a company and can have roles in different companys. So that's my
problem.

 

[John W. Vinson]

Op 25/01/2012 17:23, John W. Vinson schreef:


Thanks for the the answer. But the same person can have more than one
role in a company and can have roles in different companys. So that's my
problem.

In that case you need yet another table. You have a many to many relationship
between Persons and Companies. The way to handle a many to many is with a
"junction table":

Companies
CompanyID <primary key>
CompanyName <text>
<other info about the company as an entity in its own right>

Persons
PersonID <primary key>
LastName
FirstName
<other biographical data as appropriate>

Positions
CompanyID <link to Companies>
PersonID <link to Persons>
RoleID <link to Roles, what role does this person play in this company>
<any other info about this person with regard to this company, e.g. date the
person assumed this role>

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com