Sorting & Grouping On Multiple Fields

[Ty Archer]

Hello, can anyone help me with this? I have four fields in a table call
Team1, Team2, Team3 & Team4. Each of the fields are linked to a lookup table
that has 22 team names. Persons in the database can be a part of up to four
team.

I wish to group my report by the 22 teams. My problem is how do I tell my
report to check all four fields for each of the 22 team names before it
starts to group. For example a person can be in a team call "Hawk" and that
is listed in the field call Team1, while another person can also be in "Hawk"
but have that listed in the field call Team3.

Thanks very much for you kind help

 

[Klatuu]

The real problem is not the reporting issue, it is your data structure. It
needs serious improvement. It should not matter how many teams there are or
how many a person can belong to. If there is a limit of 4 teams, that should
be controled in your data entry form. And, Lookup fields are, to quote John
Vinson, MVP, Lookup fields are a "misfeature". They really should not be
used.

Now, what you should have is a Teams table that lists all the team names.
You also need a Person/Teams table that has the name and team of each person.
So the relationships are:

Person -> One To Many -> Person/Team
Team -> One To Many -> Person/Team

Then your report should be based on a query that includes all 3 tables.

 

[Andy Hull]

Can you give more info about what you are trying to do?

And perhaps a small amount of sample data with expected results.

It looks like you could benefit by adding a table to store the many to many
relationship of people to teams.

I'm imagining you have the following tables...

tblPeople: PersonID, Name, Team1, Team2, Team3, Team4

tblTeams: TeamID, TeamName

I would recommend adding the following...

tblPeopleTeams: PersonID, TeamID

With this table sitting between tblPeople and tblTeams (and correctly
populated) you should be able to group by Team Name.

If this design proves useful you should probably also remove the fields
Team1, Team2, Team3 & Team4 from tblPeople but leave them while you test the
above approach as changes in design like this an impact many parts of an
existing database.

Regards

Andy Hull

 

[Ty Archer]

You are absolutely correct. The problem is with the structure (it was not my
design), but since there are so many records in the database I wanted to see
if there was another way of creating the report without changing the
sturcture which would mean a lot of work on my part. However, I guess I will
have to bite the bullet and make the change. Thanks for you input.

 

[Ty Archer]

What happens when I create the report, it gives me multiple groups with the
same name. ie. the "Hawks" group would appear twice. But you are correct
about changing the design to include additional tables, which I thought I
could avoid, because it means more work on my part. But I guess it has to be
done. Thanks very much for you input.