Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 6th, 2006, 05:04 AM
Registered User
 
Join Date: Jun 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kamsach
Default Creating a custom sheet

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.
 
Old September 6th, 2006, 07:10 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old September 6th, 2006, 11:49 PM
Friend of Wrox
 
Join Date: May 2005
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Custom Site Definition pragaw BOOK: Professional SharePoint 2007 Development ISBN: 978-0-470-11756-9 13 June 19th, 2009 11:22 AM
creating custom page ajindal General .NET 2 July 19th, 2006 05:17 AM
Need help for creating custom component chocolate Biztalk 1 March 23rd, 2006 06:21 AM
Creating Web Custom Controls Lerik C# 2005 0 January 6th, 2006 04:55 AM
creating an event for a custom class? lkubler BOOK: Beginning Access 2003 VBA 3 September 20th, 2005 03:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.