Count query as well as select query

[covcitydv]

Hello All,

I'm currently developing a football team website and I'm created a player profile bit of the website linking to my Access Database.

Currently I have the following :

selectCommand="SELECT [playername], [favouriteposition], [picture] FROM [player] WHERE [playername] ='Smith J'

This means I can display John Smiths Name, Fav Position and his Picture from my player table.

I also want to count his stats (goals, starts, subs) but am not sure how to do this.

I have a table called playermatchevents which records matchID, playerID, shirtnumber, goals, starts, subs, mom

goals is a straightforward Number datatype field, whereas starts,subs and mom are a Yes/No datatype field.

Basically I want to keep my select query, but I also want to add to it that it COUNTS the number of starts, subs, goals and mom that that player has.

Pardon me if I haven't used the correct wording, I'm still quite new to all of this.

I would be very grateful for any help.

Thanks,

covcitydv

 

[ajetrumpet]

I wonder if it's possible to use functions to return this data? Say, if a form type display?

What you're asking to do is impossible within one query, and it would probably take at least 3 different queries, one on top of the other, to get all the data that you want together, into one display. If you could display this data from a form, it would be a lot easier, as you could use Visual Basic to do it instead of the built-in query function of Access. When compared to coding, the query mechanism of the program takes a back seat.

Just for reference, I will tell you, that in order to do this, you would have to get a count of every field you want, separately, one count in a query, then join them all together (to avoid incredible complications, that is). The reason for this is because the COUNT function is an aggregate, and requires the grouping of other fields that tag along with it in the same query. If have explained this phenomenon in basic terms (but nothing as complicated as what you would need to do here) in this FAQ.

How does the form idea sound??

 

[covcitydv]

Thanks for your help.

So what you're saying is that the best way to approach this would be via a different SQL for the following.

Total number of 'yes' for starts when season is '2007-08'
Total number of 'yes' for subs when season is '2007-08'
Total number of 'numbers' for goals when season is '2007-08'

Hmmmm, otherwise I would have to look for some sort of visual basic or C# code ???

Sorry not very techical with these things, but always willing to learn.

 

[ajetrumpet]

Total number of 'yes' for starts when season is '2007-08'
Total number of 'yes' for subs when season is '2007-08'
Total number of 'numbers' for goals when season is '2007-08'

You're on the right track with that, but it will be difficult, and not so elegant.

Hmmmm, otherwise I would have to look for some sort of visual basic or C# code ???

You're more on the right track here, although I don't know about C#, but Visual Basic will do just fine.

Can you, and are you, willing to have the result(s) displayed in a single record form? Is that possible for you? If so, I would recommend doing that instead of trying the impossible with the queries.

 

[covcitydv]

Yes I am. Ideally it would look something like this ...

--------------------------------------------------------------------------

[PLAYERNAME]
[PLAYERPHOTO]

Appearances Goals
A (B) C


-------------------------------------------------------------------------

A = Total Number of [START] for [PLAYERNAME] when [SEASON] = '2007-8'
B = Total Number of [SUBS] for [PLAYERNAME] when [SEASON] = '2007-08'
C = Total Number of [GOALS] for [PLAYERNAME] when [SEASON] = '2007-08'

I have already set up the player name and player photo bit.

 

[ajetrumpet]

All you need in the controls that will hold the data is the DCOUNT function with a condition in it. Does this get you started?

 

[covcitydv]

The DCOUNT function ???

I'll have a look on google for it. Thanks.