Selective DCount

[enc]

I'm having alot of difficulty with using the DCOUNT function within a report in my database. I am creating a sports league and the report in question is to show the league standings.

I need to display how many games a team has won/drawn/lost respectively. One of my queries is designed to assign points to a team in fixture and therefore is basically a huge list of all fixtures, the games within a fixture and the points assigned to each team (3 for a win, 1 for draw, 0 for loss). Very much like this:

Fixture  | Game    | Team   | Points
------------------------------------
1        | 1       | T1     | 3
1        | 1       | T2     | 0
1        | 2       | T1     | 3
1        | 2       | T2     | 0

From this query I am trying to use the following formula for Games Won in my report:

=DCount("[Points]","qryPoints","[Points] = 3")

This however does not in anyway relate to points per team and purely shows a running total of the occurences of "2" in the column.

How is it possible to have DCount work on a "Number of occurences per team" basis?

 

[Fizzio]

I would actually change your data storage structure to store the actual game result rather then the points scored. You can then use a lookup table to determine how many points were scored
ie
tblPointsLookup
ResultID (AutoNumber)
ResultDesc (Text) ie W, D, L
ResultPoints (Number - Integer) ie 3, 1, 0

You can use a crosstab query to summarise the data by team.

If you can then include the lookup table fields in the query to summarise them.

 

[enc]

I already do use a table to store my game results. The query I posted above is generated on comparing two fields to find out whether each game has been won/drawn/or lost and therefore assigning points. This is not a problem.

My problem is I need to count the number of occurences of a number from within my query for each team.

 

[Fizzio]

You can do this using the aggregate functions in the query eg Group by, Count etc. To display these (if you do not already know) depress the Sigma button on the query toolbar to enable these functons.
hth

 

[enc]

Thanks for trying but you're mis-reading the question.

I basically need a formula that will count, for each team, how many occurences of a number is shown.

 

[Fizzio]

No, I get what you mean. Try this. (a bit rough and ready mind you)

 

[enc]

Hmm. Interesting concept and clearly works. Means I may have to have a re-design of my database though. Agh!

Thanks for that though. And thanks for sticking by.

 

[Fizzio]

No problem. I knew I'd make sense eventually

 

[ColinEssex]

No problem. I knew I'd make sense eventually

 

[Rich]

No problem. I knew I'd make sense eventually

Will we have to wait much longer?

 

[Fizzio]

Will we have to wait much longer?

I have to say, that is your best yet (although I hate to admit it). For you, an eternity