Relationship question

[Daniel]

I have 2 simple tables tblContact & tblGroup and I have only 2 groups. What
is the difference between these 2 designs and which one should I use?

tblContact
CONTACT_ID (Primary KEY)
GROUP (no Look up)
FirstName
LastName

tblGroup
Group (Primary Key)

……..

tblContact
CONTACT_ID (Primary Key)
GROUP_ID (no Look up)
FirstName
LastName

tblGroup
GROUP_ID (Primary Key)
Group


Thanks for any assistance,
Daniel

 

[Jeff Boyce]

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Daniel]

Each contact belong to 1 or to both groups, may be to more in future. Which
way would be better in this case?

Regards
Daniel

 

[Daniel]

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups I
am actually using 2 different tables, tblGroup1 and 2. I will try to clarify
things a bit more: I made a test using the first design option I mentioned in
my first msg, to see if I can get these functions to work. The database would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200 persons
and all these functions seem to work fine, no errors (somehow strange for my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database. I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep, Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel

 

[Jeff Boyce]

Daniel

I'm not sure I'm understanding... it sounds like you have a group table for
each group. This is not a good design. Please re-read my earlier response
for a common design approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups
I
am actually using 2 different tables, tblGroup1 and 2. I will try to
clarify
things a bit more: I made a test using the first design option I mentioned
in
my first msg, to see if I can get these functions to work. The database
would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200
persons
and all these functions seem to work fine, no errors (somehow strange for
my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database.
I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep,
Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one
group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the
person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Daniel]

Dear Jeff,

Please bear with me a little bit more with this question. Now I am very glad
I asked.

One person in tblPerson who works for example in the Education department,
and there he works in Administration section. I need to be able to assign to
this person both the department and the section where he/she works.

The design I have now is like this:
-1 table for the persons where I included the name, e-mail address, the
postal address and the phone number.
- 2 tables, one for the departments and one for the sections. On the main
form I assign to each person both the department and the section. This seems
now a very bad and poor design.

How can I integrate this knowledge in the design you described? Would you
sketch an example? I appreciate it very much.

Regards,
Daniel

Daniel

I'm not sure I'm understanding... it sounds like you have a group table for
each group. This is not a good design. Please re-read my earlier response
for a common design approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups
I
am actually using 2 different tables, tblGroup1 and 2. I will try to
clarify
things a bit more: I made a test using the first design option I mentioned
in
my first msg, to see if I can get these functions to work. The database
would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200
persons
and all these functions seem to work fine, no errors (somehow strange for
my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database.
I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep,
Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one
group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the
person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I have 2 simple tables tblContact & tblGroup and I have only 2 groups.
What
is the difference between these 2 designs and which one should I use?

tblContact
CONTACT_ID (Primary KEY)
GROUP (no Look up)
FirstName
LastName

tblGroup
Group (Primary Key)

....

tblContact
CONTACT_ID (Primary Key)
GROUP_ID (no Look up)
FirstName
LastName

tblGroup
GROUP_ID (Primary Key)
Group


Thanks for any assistance,
Daniel

 

[Daniel]

Does this approach make sense?

tblPersons
Persons_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number, no Look Up)
Section_ID (Data type: Number, no Look Up)
FName, LName, E-mail, Phone, Address

tblDepartment
Departement_ID (Data type: AutoNumber, Primary Key)
Department

TblSection
Section_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number)
Section

Relation between tblPersons and tblDepartment
Persons_ID to Department_ID one-to-one

Relation between tbl Department and tblSection
Department_ID to Department_ID on-to-many

Regards,
Daniel

Daniel

I'm not sure I'm understanding... it sounds like you have a group table for
each group. This is not a good design. Please re-read my earlier response
for a common design approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups
I
am actually using 2 different tables, tblGroup1 and 2. I will try to
clarify
things a bit more: I made a test using the first design option I mentioned
in
my first msg, to see if I can get these functions to work. The database
would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200
persons
and all these functions seem to work fine, no errors (somehow strange for
my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database.
I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep,
Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one
group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the
person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I have 2 simple tables tblContact & tblGroup and I have only 2 groups.
What
is the difference between these 2 designs and which one should I use?

tblContact
CONTACT_ID (Primary KEY)
GROUP (no Look up)
FirstName
LastName

tblGroup
Group (Primary Key)

....

tblContact
CONTACT_ID (Primary Key)
GROUP_ID (no Look up)
FirstName
LastName

tblGroup
GROUP_ID (Primary Key)
Group


Thanks for any assistance,
Daniel

 

[Jeff Boyce]

Daniel

The thing that bothers me about the design is that a person cannot be
connected to more than one department/section.

Without knowing more, I'd probably opt for having a person table that only
holds person information (name, address, DOB), and a "junction" table that
resolves any potential many-to-many situations with persons and their
assignment(s). This table would look very much like the person table you
offered, only:

trelPersonAssignment
PersonID (from a person table)
DepartmentID (from a Department table)
SectionID (from a Section table)
DateOfAssignment
DateLeftAssignment

This design would allow for a person to be assigned to more than one
department, and to have multiple assignments to the same department over
time.

This may NOT be what works in your situation, though.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Does this approach make sense?

tblPersons
Persons_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number, no Look Up)
Section_ID (Data type: Number, no Look Up)
FName, LName, E-mail, Phone, Address

tblDepartment
Departement_ID (Data type: AutoNumber, Primary Key)
Department

TblSection
Section_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number)
Section

Relation between tblPersons and tblDepartment
Persons_ID to Department_ID one-to-one

Relation between tbl Department and tblSection
Department_ID to Department_ID on-to-many

Regards,
Daniel

Daniel

I'm not sure I'm understanding... it sounds like you have a group table for
each group. This is not a good design. Please re-read my earlier response
for a common design approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups
I
am actually using 2 different tables, tblGroup1 and 2. I will try to
clarify
things a bit more: I made a test using the first design option I mentioned
in
my first msg, to see if I can get these functions to work. The database
would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200
persons
and all these functions seem to work fine, no errors (somehow strange for
my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database.
I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep,
Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel


:

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one
group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the
person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I have 2 simple tables tblContact & tblGroup and I have only 2 groups.
What
is the difference between these 2 designs and which one should I use?

tblContact
CONTACT_ID (Primary KEY)
GROUP (no Look up)
FirstName
LastName

tblGroup
Group (Primary Key)

....

tblContact
CONTACT_ID (Primary Key)
GROUP_ID (no Look up)
FirstName
LastName

tblGroup
GROUP_ID (Primary Key)
Group


Thanks for any assistance,
Daniel

 

[Daniel]

I thought you will never answer, sorry about that. Sometimes I think I am
complicating things when explaining more. So in the meantime (also learning
from your input) I redesigned my database this way:

I changed the name of the department to GROUP (!) and ignored Section from
the design as a separate identity. Now when I populate the GROUP table, I
need a main GROUP and sub GROUPS for identification. For example if a person
works in the Education department and there e.g. in Administration section,
this contact will be assigned 2 lables on the main form, via a combo box:
EduDep
EduAdmin
On the main form I added command buttons for example for sending mail based
on a parameter query, this way one can send mail to the whole dep by entering
edudep or to a subgroup (section!) by entering eduadmin. I will certainly
look now at the design you mentioned, because although the design I have now
works, it still has some limitation, thank you very much.

TblCONTACTS
CONTACTS_ID (Primary Key - Autonumber)
FirstName; LastName; Addresse

TblGROUP
GROUP_ID (Primary Key - Autonumber)
GROUPNAME (Text)
CONTACTS_ID (Number)

Relations:
CONTACTS_ID (tblCONTACTS) one-to-many CONTACTS_ID (tblGROUP)

Join types
Enforce Referential Integrity, Cascade Update Related Fields and Cascade
Delete Related Records are all selected

Details of the subformGROUP on the main form
Source Object: sfrmGROUP
Link Child Fields: CONTACT_ID
Link Master Fields: CONTACT_ID

Record Source: qryGROUP

Combo Box on the subform
Control Source: GROUPNAME
Row Source Type: Table/Query
Row Source: SELECT GROUP.GROUPNAME FROM [GROUP] GROUP BY GROUP.GROUPNAME
ORDER BY GROUP.GROUPNAME;

The main form:
The Record source of the main form is a query based on tblCONTACTS

Regards,
Daniel

Daniel

The thing that bothers me about the design is that a person cannot be
connected to more than one department/section.

Without knowing more, I'd probably opt for having a person table that only
holds person information (name, address, DOB), and a "junction" table that
resolves any potential many-to-many situations with persons and their
assignment(s). This table would look very much like the person table you
offered, only:

trelPersonAssignment
PersonID (from a person table)
DepartmentID (from a Department table)
SectionID (from a Section table)
DateOfAssignment
DateLeftAssignment

This design would allow for a person to be assigned to more than one
department, and to have multiple assignments to the same department over
time.

This may NOT be what works in your situation, though.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Does this approach make sense?

tblPersons
Persons_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number, no Look Up)
Section_ID (Data type: Number, no Look Up)
FName, LName, E-mail, Phone, Address

tblDepartment
Departement_ID (Data type: AutoNumber, Primary Key)
Department

TblSection
Section_ID (Data type: AutoNumber, Primary Key)
Department_ID (Data type: Number)
Section

Relation between tblPersons and tblDepartment
Persons_ID to Department_ID one-to-one

Relation between tbl Department and tblSection
Department_ID to Department_ID on-to-many

Regards,
Daniel

Daniel

I'm not sure I'm understanding... it sounds like you have a group table for
each group. This is not a good design. Please re-read my earlier response
for a common design approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I am mixing things up. I mentioned 2 groups but for these 2 groups
I
am actually using 2 different tables, tblGroup1 and 2. I will try to
clarify
things a bit more: I made a test using the first design option I mentioned
in
my first msg, to see if I can get these functions to work. The database
would
be used for mass mailing, Word merge and address labels printing. I have
tested the database on different machines using a sample of about 200
persons
and all these functions seem to work fine, no errors (somehow strange for
my
knowledge of Access). So I was very happy. Now I am not sure if the design
for the group tables are correct so that I can begin to use the database.
I
saw another database and saw a different design>> option 2.
The database works this way, e.g. for mass mailing. On the main form, I
created a button, OnClick a [Parameter] query opens. This parameter uses
descriptions in tblGroup1. tblGroup1 contains Edu for Education dep,
Finance,
etc. So when I need to send e-mail (or for Word merge) to all those who
belong to Education dep, I type edu in the parameter query. I hope my
question is now more clear...my English is unfortunately not that great..

Regards
Daniel


:

That will really depend on what your real world situation is. How are
"groups" and "contacts" related?

Can you have the same person designated the contact for more than one
group?

A common approach when you have persons, groups, and
person-as-contact-for-a-group is to use three tables, something like:

tblPerson
PersonID
FName
...

tblGroup
GroupID
GroupName
...

trelContact
PersonID
GroupID
ContactPhoneNumber (in case this can be different than the
person's
regular phone#)
...


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I have 2 simple tables tblContact & tblGroup and I have only 2 groups.
What
is the difference between these 2 designs and which one should I use?

tblContact
CONTACT_ID (Primary KEY)
GROUP (no Look up)
FirstName
LastName

tblGroup
Group (Primary Key)

....

tblContact
CONTACT_ID (Primary Key)
GROUP_ID (no Look up)
FirstName
LastName

tblGroup
GROUP_ID (Primary Key)
Group


Thanks for any assistance,
Daniel