Report question - grouping?

[StevenB]

I have an Access 97 database that I use to track ice hockey statistics.

The database is 4 tables in all, and I've included the structure of the 4 tables at the end of the message. Apologies to those who hate underscores.

I've created a number of reports based on this database. Right now I'm working on reports that pull player statistics from the database. Generally, there are two key criteria that play into that, which are in the Game_Info table. They are Season and Game_Type. This is because when we talk about statistics, we usually want to specify the season and the type of game (such as Regular Season vs Playoff games).

I've already created a form that asks for those two criteria, and from there can launch a report that gives all of the player statistics given those criteria. (For example, all player statistics for the 2000-2001 Playoffs)

My problem now is that I'm trying to create a report that will give me the entire history for a given player. This would be as follows:

---------
Player name: Steve Battisti

Regular Season Games Goals Assists
1999-2000 5 10
2000-2001 10 15
Total (Obviously a calculated field)

Playoff Games Goals Assists
1999-2000 5 10
2000-2001 10 15
Total 15 25

Tournament Games Goals Assists
1999-2000 5 10
2000-2001 10 15
Total 15 25
----------

Unfortunately, I haven't a clue how to approach this. I'm certain it has something to do with the Sorting/Grouping, but I can't even figure out how to configure the query to base this on.

Any thoughts would be greatly appreciated. I'd be happy to send along a copy of the db to anyone who is interested. The entire thing is only 350K zipped.

Thanks!

Steve Battisti

Table: Game_Info (Data about specific games, such as who played and when, and some overall statistics)
Columns
Name Type Size
Game_ID (PK) Number (Long) 4
Home_Team Number (Long) 4
Visiting_Team Number (Long) 4
Game_Date Date/Time 8
Game_Time Text 50
Game_Location Text 50
Season Text 50
Home_Goals_P1 Number (Long) 4
Home_Goals_P2 Number (Long) 4
Home_Goals_P3 Number (Long) 4
Home_Goals_OT Number (Long) 4
Away_Goals_P1 Number (Long) 4
Away_Goals_P2 Number (Long) 4
Away_Goals_P3 Number (Long) 4
Away_Goals_OT Number (Long) 4
Home_Shots_P1 Number (Long) 4
Home_Shots_P2 Number (Long) 4
Home_Shots_P3 Number (Long) 4
Home_Shots_OT Number (Long) 4
Away_Shots_P1 Number (Long) 4
Away_Shots_P2 Number (Long) 4
Away_Shots_P3 Number (Long) 4
Away_Shots_OT Number (Long) 4
Game_Type Text 50
Recap Memo -
Notes Text 50

Table: Player_History (Detailed record of each action that occurred in any game)
Columns
Name Type Size
Player_Action_ID (PK) Number (Long) 4
Game_ID (FK) Number (Long) 4
Player_ID (FK) Number (Long) 4
Action_Type Text 50
Assist1 Number (Long) 4
Assist2 Number (Long) 4
Start_Time Date/Time 8
End_Time Date/Time 8
Period Text 50
Penalty_Type Text 50
Penalty_Minutes Number (Long) 4
Penalty_Result Text 50
team Number (Long) 4

Table: Player_Info (Information about individual players)
Columns
Name Type Size
Player_ID (PK) Number (Long) 4
Team_ID (FK) Number (Long) 4
First_Name Text 50
Last_Name Text 50
Shoots Text 50
Height Text 50
Weight Number (Long) 4
Birth_Date Date/Time 8
Sex Text 50
Birthplace Text 50
Jersey_No Number (Long) 4
Position Text 50
Nickname Text 50
Phone_Home Text 50
Phone_Work Text 50
Pager Text 50
Phone_Cell Text 50
Street_Address_1 Text 50
Street_Address_2 Text 50
City Text 50
State Text 50
ZIP Text 50
E-mail Text 50
First_Year Text 50
Start_Date Date/Time 8
End_Date Date/Time 8

Table: Team_Info (Information about different teams)
Columns
Name Type Size
Team_ID (PK) Number (Long) 4
Team_Name Text 30
Team_City Text 30
Team_Nickname Text 20
Team_Level Text 20

 

[JoeMiller]

It looks like you need a Crosstab Query to first sum up the data and then use the Crosstab to supply the data for your report. The help section will help you in learning about Crosstabs.
Joe Miller
joe.miller@flotech.net

 

[StevenB]

Thanks for the suggestion on crosstabs. I spent a few minute goofing around with it, but I'm confused about something.

Let's say that, broadly, I want the left-hand columns to be unique Player_IDs from the Player_Info table, and then I want the data for each Player_ID to be grouped by the Season and the Type. So far so good, but what if I have 20 different pieces of data that fall under each of those categories?

For example, for Player A, let's say I want total goals for 1999, total goals for 2000, total goals for regular season, total goals for playoffs. Then I want the same information for assists as well, plus penalty minutes. I'm not going to end up with a separate crosstab for each of those, am I? I'd end up with 15 or 20 cross-tab queries just to get the data I need.

Or am I missing something here?

Thanks,
Steve

 

[JoeMiller]

After reviewing your table setups it looks like you'll have to go with 15 to 20 different crosstab queries to get at the info you need. Not an ideal situation. Were the tables structured differently it would be no problem to get this down to a couple crosstabs to get all the info you need. The problem lies in how you set up the like fields and changed their "suffix."

IE:
Home_Goals_P1
Home_Goals_P2
Home_Goals_P3
Home_Goals_0T
Away_Goals_P1
Away_Goals_P2
Away_Goals_P3
Away_Goals_0T

If you had a "Goals" table with fields like so in it:

Goal_ID (PK)
Game_ID
Player_ID
Home_Or_Away
Period_Number
Goals

You could make a "Shots" table in the same fashion as the Goals table.

Now when you set up the crosstab, it could automatically calculate all your goals by player, by period, by home or away etc... I don't know how much time is invested to look at restructuring the database or if you should just go ahead and make the multitude of crosstabs. This is a common mistake in database design, we've all done it so don't feel bad! And remember this phrase when designing tables: Go deep not wide!

Joe Miller
joe.miller@flotech.net

 

[StevenB]

Hmm, I may be in decent shape yet. See, those fields you referred to are not actually the ones I want to count. In my Player_History table, I've made a unique record for each action (goal, assist, penalty) that is committed for a given game. So my history table actually looks pretty similar to your "Goals" table, with a unique identifier "Player_Action_ID", then foreign keys "Player_ID" and "Game_ID". The only complication is that I then have an identifier, Action_Type, that I need to use. (Something like: select all Player_Action_IDs where Action_Type="Goal&quot

Does this improve the outlook at all?

Steve

 

[JoeMiller]

Why yes! Yes it does.. make your crosstab query and add your Action_Type as a Row Heading for the crosstab. Now lets assume you have two action types, goals & shots, when Access displays the data you'll see something like this:

[tt]
Player_ID Action_Type 1999 2000 Total
John Smith Goals 15 10 25
John Smith Shots 30 20 50
Riki Rocket Goals 50 35 85
Riki Rocket Shots 100 100 200
[/tt]


Now when you do your report and base it on the crosstab you can do the grouping on Player_ID and get what you're after. As for looking at only "Goals," Crosstab queries work just like other queries and you can put "Goals" in the criteria for Action_Type and it will only show you goals.

Hope that helps!
Joe Miller
joe.miller@flotech.net