Question - expressions in queries?

[StevenB]

Howdy folks, I'm looking to create a query that does some calculations on my database.

Essentially, I'm trying to create a report for a hockey database that pulls all of the player names from one table, and then looks at another table to calculate the number of goals scored, etc.

I have no problem getting the list of players, but I'm not sure how to go about the second piece. I'm guessing I can create an expression within the query. Here's an example of the logic I'm trying to build in:

Total Goals:
Should look at the PlayerHistory table and bring back a count of the number of times the ActionType='Goal' was recorded for that particular player. (PlayerHistory has one unique row for each individual action that occurs in a game: goal, assist, etc.)

So, if PlayerHistory had 5 rows where Steve Battisti had an ActionType of 'Goal', then the query should return a '5'.

Any ideas?

Thanks!

Steve Battisti

 

[JerryDennison]

How would you like to have one query that gives you the total for all the different types for every player? You have the perfect candidate for a crosstab query. Using the CrossTab wizard set your Rows to Player, your Columns to ActionType and your value to Count. You'll have one row per player with one column per action type and the total times each player had that action.

 

[StevenB]

That sounds great! I've never used a cross-tab query before, so I'll give it a try. It may take me a while though...

Thanks for the tip!

Steve

 

[StevenB]

Jerry,

Thanks for the suggestion. The crosstab query is pretty cool! Actually, though, after talking with a co-worker I found another way of doing it that worked just as well.

I used the Dcount function as follows on the report:

=DCount("[Action_Type]","qryPlayerStats","[Action_Type] = 'Goal' and [Player_ID] = [txtPlayer_ID]&quot

It counts each time the Action_Type of Goal appears for that player.

Pretty neat!

It was good to get exposure to the crosstab query thing though...