If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting & Grouping On Multiple Fields
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 |
#2
|
|||
|
|||
Sorting & Grouping On Multiple Fields
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 a 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. -- Dave Hargis, Microsoft Access MVP "Ty Archer" wrote: 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 |
#3
|
|||
|
|||
Sorting & Grouping On Multiple Fields
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" wrote: 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 |
#4
|
|||
|
|||
Sorting & Grouping On Multiple Fields
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. "Klatuu" wrote: 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 a 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. -- Dave Hargis, Microsoft Access MVP "Ty Archer" wrote: 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 |
#5
|
|||
|
|||
Sorting & Grouping On Multiple Fields
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. "Andy Hull" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|