Hi All,

I am trying to create a report that gives me collective data. For example in a table I have a list as:
Team Captain Players_Name
Cambers Ball Alan Faye Sam
Cambers Ball Alan Faye Arthur
Cambers Hockey Alan Faye Rick
Cambers Hockey Alan Faye Ryan
Cambers Cricket Jo Brigger Fred
Cambers Cricket Jo Brigger Owaise

My resulting report should only give me the Team's name, The captain's name and the count of the players in each team. I am currently doing the project in ASP.

The resulting report should look like this
Team Captain Count
Cambers Ball Alan Faye 2
Cambers Hockey Alan Faye 2
Cambers Cricket Jo Brigger 2

Could you please help me out with this? Im stuck on this for a long time now.let me know if you need any info to help further.

Thanks in advance.

This probably should be in a SQL forum but no matter. You actually should have this table broken up into, at least, 2 tables.

Team Table
----------
TeamPK Team Captain
1 Cambers Ball Alan Faye
2 Cambers Hockey Alan Faye
3 Cambers Cricket Jo Brigger

Players Table
-------------
PlayerPK PlayerName PlayerTeam_fk
1 Sam 1
2 Arthur 1
3 Rick 2
4 Ryan 2
5 Fred 3
6 Owaise 3

Now just join the tables and do a count it should work fo ryou

"The one language all programmers understand is profanity."

Kamsach: the problem you are having falls under the category of Rules of Data Normalization for Relational Databases. This problem is not uncommon. There are 5 rules of data normalization: 1NF-Eliminate Repeating Groups; 2-Eliminate Redundant Data; 3NF-Eliminate Columns Not dependent on Key,etc. this information can be found in any and most books referencing Relational Databases, MS Access, MS SQL Server, ASP.NET databases with VB.NET, just to name a few.

I suggest the following tables: the CaptainTbl,TeamTbl,NewPlayerTbl, should have its own table (according to 1NF).

CaptainTbl
CapIDPK CapName
1 Alan Faye
2 Jo Brigger
Adding a captain and/or changing the captain on an existing team is easy. Just make that change to one table as opposed to searching through a table for every occurrance of the captains name.

NewTeamTbl
NewTeamIDPK TeamName
1 Cambers Ball
2 Cambers Hockey
3 Cambers Cricket

TeamCaptainTbl
TCPKID NewTeamIDPK** CapIDPK**
1 1 1
2 2 1
3 3 2
**Note: The Primary Keys (PK) in the NewTeamTbl and CaptainTbl become Foreign Keys (FK) in the joined Table TeamCaptainTbl. This table eliminates redundancy.

NewPlayerTbl
PlayerIDPK PlayerName
1 Sam
2 Author
3 Rick
4 Ryan
5 Fred
6 Owaise

PlayerTeamTbl
PTIDPK PlayerIDPK*** NewTeamIDPK***
1 1 1
2 1 2
3 2 1
***This join table allows for a player to be a member of more than one team. It also allows for the Captain to be a player in addition to being on more than one team. Using the Player team table will get a true count of the number of players per 1 or all teams from your query.

Other information about the player(s) may be added here, for example the players email address. But to add the position the player holds, create a Position Table, then add the Position Table PK ID to the PlayerTeamTbl to give u the position the plays on that particular team. Also this table allows for a player to play more than one position on the same team....

This should help.