Re: Relationship question

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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Daniel" <Daniel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3F4016DD-5DD9-4709-9618-CEBC1614ECBF@xxxxxxxxxxxxxxxx
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


"Jeff Boyce" wrote:

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

"Daniel" <Daniel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:166AC9E1-82F9-4FEE-8829-543799345646@xxxxxxxxxxxxxxxx
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" wrote:

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
http://mvp.support.microsoft.com/

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

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

"Daniel" <Daniel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4759E47D-C668-438E-8FA4-0947873C399B@xxxxxxxxxxxxxxxx
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








.

Loading