People in Groups

[Mervyn Thomas]

I've got a simple database with people in membership placed into up to three
groups as indicated by 3 fields in a members table. There are 10 possible
groups in a seperate table and the group names can be selected up to three
times for the members
It's easy to design a report showing which groups people are in, but how do
you do a report which shows who is in group1, group2,etc ?
Sorry it seems a simple question but it has me stumped!

 

[Rob Parker]

Hi Mervyn,

I suspect that the problem arises because you have fields (3 of them, from
your description of the problem) in your people table to hold the group
information. And that's the root of the problem. There is a many-to-many
relationship between people and groups - each person can belong to more than
one group, and each group can have more than one person. You need a
PersonGroup table to manage this, which will have records consisting of the
PersonID and the GroupID (the primary keys in each of the other tables); it
may contain other data, but those are the two essential fields. From this,
it will be easy to generate queries (as the basis for reports) of which
people are in which group, or which group contains which people.

HTH,

Rob

 

[Mervyn Thomas]

Rob - Many thanks - can you help me a bit further? I formed a table as
suggested and did joins to the other tables but how do you actually produce
the records in this new table? _ I'm absolutely new to all this !
Apologies !
Mervyn

 

[Armen Stein]

Rob - Many thanks - can you help me a bit further? I formed a table as
suggested and did joins to the other tables but how do you actually produce
the records in this new table? _ I'm absolutely new to all this !
Apologies !
Mervyn

Hi Mervyn,

No apologies needed - we're here to help!

You can maintain the records in the PersonGroup table using a subform.
Your main form will be on the Person table. The subform (look at Help
topics) will be on the PersonGroup table, with the Master & Child
fields set to your Person primary key. The subform will allow you to
add, change and delete the Groups each Person is in.

Conversely, you can also have a Group form, with a subform showing the
list of member People. Same deal, but with the Group primary key as
the Master & Child fields. Very handy, and something very difficult
to do with your previous three-field design.

I'm glad you're changing your table structure. When I give
presentations on database design, I use the saying "A good database
designer knows only 3 numbers: Zero, One, and Many." Anytime you
track three of something, you are probably making a database design
error.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

Rob - Many thanks - can you help me a bit further? I formed a table as
suggested and did joins to the other tables but how do you actually produce
the records in this new table?

Typically you would use a Form for one of the "one" side tables (Groups or
People) with a Subform for the PeopleGroups table.

See some of the tutorials and other resources here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Mervyn Thomas]

Thanks for your detailed response - will work on it
Mervyn

Mervyn:

As well as inserting new rows via a subform as the others have described
you
can also insert rows on the basis of the data in your current table with
three append queries. What you do is join the current People table to the
Groups table on one of the three existing three group fields, then append
the
PersonID from the People table and the GroupID from the Groups table into
the
new PersonGroup table, so the first append query would be like this:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.FirstGroupField = Groups.Group
WHERE People.FirstGroupField IS NOT NULL;

then:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.SecondGroupField = Groups.Group
WHERE People.SecondGroupField IS NOT NULL;

and finally:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.ThirdGroupField = Groups.Group
WHERE People.ThirdGroupField IS NOT NULL;

Once you are happy that the rows have been correctly inserted into the
PersonGroup table you can delete the redundant FirstGroupField,
SecondGroupField and ThirdGroupField fields from the People table.

One other thing you should do if you have not done so already is create a
unique index on the PersonID and GroupID columns (fields) in the
PersonGroup
table. This will prevent duplicate rows being inadvertently entered. The
easiest way of doing this is by making the two columns the table's
composite
primary key, which you do in table design view by Ctrl-clicking on each
field,
making sure you click on the field selector (the little grey rectangle to
the
left of the field name), then right-click and select 'Primary key' from
the
shortcut menu.

If you are already using another column as the primary key you can index
the
fields uniquely by selecting indexes from the View menu. Enter a suitable
index name in one row of the left column, then enter the column names on
two
rows of the Field Name column. With the first row (the one with the index
name) selected enter Yes as the 'Unique' property. If you are using
Access
2007 the interface for doing this will differ, but should essentially be
the
same,

Ken Sheridan
Stafford, England

Rob - Many thanks - can you help me a bit further? I formed a table as
suggested and did joins to the other tables but how do you actually
produce
the records in this new table? _ I'm absolutely new to all this !
Apologies !
Mervyn

Hi Mervyn,

[quoted text clipped - 21 lines]

how do you do a report which shows who is in group1, group2,etc ?
Sorry it seems a simple question but it has me stumped!

 

[Mervyn Thomas]

Well - I've worked it using a people_group relationship table and populated
it using a form in the group table and a subform from the people_group
table. I was able to do this by putting a lookup on the peopleID in the
people_group table from some code I fould in a microsoft template BUT I
don't really understand the lookup query- it happens to return the index
field from the people table which is fine but I cannot figure out how to
retun a different field which has more meaning!. Ideally what I would like
to do is to select a name but also see other fieds to ensure I am selecting
the correct name. I cannot find any real help in Access for these things!

Thanks for your detailed response - will work on it
Mervyn

Mervyn:

As well as inserting new rows via a subform as the others have described
you
can also insert rows on the basis of the data in your current table with
three append queries. What you do is join the current People table to
the
Groups table on one of the three existing three group fields, then append
the
PersonID from the People table and the GroupID from the Groups table into
the
new PersonGroup table, so the first append query would be like this:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.FirstGroupField = Groups.Group
WHERE People.FirstGroupField IS NOT NULL;

then:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.SecondGroupField = Groups.Group
WHERE People.SecondGroupField IS NOT NULL;

and finally:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.ThirdGroupField = Groups.Group
WHERE People.ThirdGroupField IS NOT NULL;

Once you are happy that the rows have been correctly inserted into the
PersonGroup table you can delete the redundant FirstGroupField,
SecondGroupField and ThirdGroupField fields from the People table.

One other thing you should do if you have not done so already is create a
unique index on the PersonID and GroupID columns (fields) in the
PersonGroup
table. This will prevent duplicate rows being inadvertently entered.
The
easiest way of doing this is by making the two columns the table's
composite
primary key, which you do in table design view by Ctrl-clicking on each
field,
making sure you click on the field selector (the little grey rectangle to
the
left of the field name), then right-click and select 'Primary key' from
the
shortcut menu.

If you are already using another column as the primary key you can index
the
fields uniquely by selecting indexes from the View menu. Enter a
suitable
index name in one row of the left column, then enter the column names on
two
rows of the Field Name column. With the first row (the one with the
index
name) selected enter Yes as the 'Unique' property. If you are using
Access
2007 the interface for doing this will differ, but should essentially be
the
same,

Ken Sheridan
Stafford, England

Rob - Many thanks - can you help me a bit further? I formed a table
as
suggested and did joins to the other tables but how do you actually
produce
the records in this new table? _ I'm absolutely new to all this !
Apologies !
Mervyn

Hi Mervyn,

[quoted text clipped - 21 lines]
how do you do a report which shows who is in group1, group2,etc ?
Sorry it seems a simple question but it has me stumped!

 

[Armen Stein]

Well - I've worked it using a people_group relationship table and populated
it using a form in the group table and a subform from the people_group
table. I was able to do this by putting a lookup on the peopleID in the
people_group table from some code I fould in a microsoft template BUT I
don't really understand the lookup query- it happens to return the index
field from the people table which is fine but I cannot figure out how to
retun a different field which has more meaning!. Ideally what I would like
to do is to select a name but also see other fieds to ensure I am selecting
the correct name. I cannot find any real help in Access for these things!

Hi Mervyn,

You're on the right track.

Did you put a lookup in the PeopleGroup table itself? Most developers
strongly recommend against using lookups in tables - they're confusing
and unnecessary. You can just build comboboxes in your forms instead,
and use joins to lookup tables in queries to see the related records.

To include a field in a combobox but not display it in the dropdown
list, set its width to zero.

To display values from *other* columns of a combobox on your form
after you've selected an item, set your controlsource to refer to it
like this:

=MyComboboxName.Column(2)

Note that the number 2 in this example could be whichever column you
want to display. The column numbers *start with zero*, so Column 2 is
really the third column. This works even if you set the display of
the column width to zero.

Access Help works best when you already know what you're looking for.
Are you using any Access books? Check out their ratings on Amazon.
Most of them have sample code you can download.

Hope this keeps you moving,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Mervyn Thomas]

Thanks for the last few responses - that just about cleans everything up and
I have learned much
Thanks again
Mervyn

Well - I've worked it using a people_group relationship table and
populated it using a form in the group table and a subform from the
people_group table. I was able to do this by putting a lookup on the
peopleID in the people_group table from some code I fould in a microsoft
template BUT I don't really understand the lookup query- it happens to
return the index field from the people table which is fine but I cannot
figure out how to retun a different field which has more meaning!.
Ideally what I would like to do is to select a name but also see other
fieds to ensure I am selecting the correct name. I cannot find any real
help in Access for these things!

Thanks for your detailed response - will work on it
Mervyn

Mervyn:

As well as inserting new rows via a subform as the others have described
you
can also insert rows on the basis of the data in your current table with
three append queries. What you do is join the current People table to
the
Groups table on one of the three existing three group fields, then
append the
PersonID from the People table and the GroupID from the Groups table
into the
new PersonGroup table, so the first append query would be like this:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.FirstGroupField = Groups.Group
WHERE People.FirstGroupField IS NOT NULL;

then:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.SecondGroupField = Groups.Group
WHERE People.SecondGroupField IS NOT NULL;

and finally:

INSERT INTO PersonGroup(PersonID, GroupID)
SELECT PersonID, GroupID
FROM People INNER JOIN Groups
ON People.ThirdGroupField = Groups.Group
WHERE People.ThirdGroupField IS NOT NULL;

Once you are happy that the rows have been correctly inserted into the
PersonGroup table you can delete the redundant FirstGroupField,
SecondGroupField and ThirdGroupField fields from the People table.

One other thing you should do if you have not done so already is create
a
unique index on the PersonID and GroupID columns (fields) in the
PersonGroup
table. This will prevent duplicate rows being inadvertently entered.
The
easiest way of doing this is by making the two columns the table's
composite
primary key, which you do in table design view by Ctrl-clicking on each
field,
making sure you click on the field selector (the little grey rectangle
to the
left of the field name), then right-click and select 'Primary key' from
the
shortcut menu.

If you are already using another column as the primary key you can index
the
fields uniquely by selecting indexes from the View menu. Enter a
suitable
index name in one row of the left column, then enter the column names on
two
rows of the Field Name column. With the first row (the one with the
index
name) selected enter Yes as the 'Unique' property. If you are using
Access
2007 the interface for doing this will differ, but should essentially be
the
same,

Ken Sheridan
Stafford, England

Mervyn Thomas wrote:
Rob - Many thanks - can you help me a bit further? I formed a table
as
suggested and did joins to the other tables but how do you actually
produce
the records in this new table? _ I'm absolutely new to all this !
Apologies !
Mervyn

Hi Mervyn,

[quoted text clipped - 21 lines]
how do you do a report which shows who is in group1, group2,etc ?
Sorry it seems a simple question but it has me stumped!