×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Question - expressions in queries?

Question - expressions in queries?

Question - expressions in queries?

(OP)
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

RE: Question - expressions in queries?

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.

RE: Question - expressions in queries?

(OP)
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

RE: Question - expressions in queries?

(OP)
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]")

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close