Microsoft Access - SQL to count from 2 fields in the same table??

Asked By Wayne Waddingham on 17-Oct-09 05:37 AM

I have 2 querys below that work fine separately, Is it possible to join these to querys together so just have one.

SELECT Count(Games.Player1) AS GamesWon, Games.Won, Games.Player1, Games.Team1
FROM Games
GROUP BY Games.Won, Games.Player1, Games.Team1
HAVING (((Games.Won)="Won") AND (Not (Games.Player1)="IsNull") AND (Not (Games.Team1)="IsNull"))

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

SELECT Count(Games.Player2) AS GamesLost, Games.Lost, Games.Player2, Games.Team2
FROM Games
GROUP BY Games.Lost, Games.Player2, Games.Team2
HAVING (((Games.Lost)="Lost") AND (Not (Games.Player2)="IsNull") AND (Not (Games.Team2)="IsNull"))

I have tryed many versions but allways seem to get errors.

Thanks for any advice
Wayne

Try UNION - Sagar P replied to Wayne Waddingham on 17-Oct-09 08:55 AM

why dont you try UNIO to join result of these 2 queries; like;

SELECT Count(Games.Player1) AS GamesWon, Games.Won, Games.Player1, Games.Team1
FROM Games
GROUP BY Games.Won, Games.Player1, Games.Team1
HAVING (((Games.Won)="Won") AND (Not (Games.Player1)="IsNull") AND (Not (Games.Team1)="IsNull"))

UNION

SELECT Count(Games.Player2) AS GamesLost, Games.Lost, Games.Player2, Games.Team2
FROM Games
GROUP BY Games.Lost, Games.Player2, Games.Team2
HAVING (((Games.Lost)="Lost") AND (Not (Games.Player2)="IsNull") AND (Not (Games.Team2)="IsNull"))

Jonathan VH replied to Wayne Waddingham on 17-Oct-09 11:21 AM

How are Player1 and Player2, and Team1 and Team2, related in your results? Are you trying to see the wins and losses for each player on the same row? If so, is Team1/Player1 always the winner and Team2/Player2 always the loser? Why is that? Why do you need the Won and Lost columns? Could you paste in a sample of this data and explain what it means? I obviously do not understand this schema, but I suspect you want a self-join.

A UNION will not produce useful results, as you will not be able to distinguish the winners from the losers in the result set.


Yes, as Jonathan pointed out Union would not produce any useful results.

[)ia6l0 iii replied to Wayne Waddingham on 17-Oct-09 12:54 PM
Please post your Games table schema, and a screenshot or a tabular structure of how you need your final result to be.

You could use a Count with a case condition to determine the Won and Lost count. 

SELECT
COUNT (CASE WHEN Won = "won" THEN 1 END) as GamesWon
        COUNT (CASE WHEN Lost = "Lost" THEN 1 END) as GamesLost
...
FROM Games

And it seems like you are having two fields called "Won" and "Lost" to record the result of a Game. I would rather have a single field with all possible results. That would lessen the confusion to a great extent and would improve the query execution too.
Db Setep - Wayne Waddingham replied to [)ia6l0 iii on 17-Oct-09 02:05 PM
Thank you for your replys,
The db is very small like 20k zipped so here it is the data is in not real but will show how i planned it to work. I know I have probly designed it the wrong way but it is allmost working.  The webpage and everything else in the db is mostly been ok to get working. The biggest problem I have run into is finding a way to count the wins and losses from Matches and Games hence the query issues I am having.

Again Thanks very much for your advice and help.
Half way there i think - Wayne Waddingham replied to Wayne Waddingham on 18-Oct-09 07:50 AM

I have come up with the following thats works good, Is there a way to add team1 in the same query?

SELECT Sum(IIf([Won]="Won",1,0)) AS xWon, Sum(IIf([Won]="Draw",1,0)) AS xDraw, Matchs.Team1
FROM Matchs
GROUP BY Matchs.Team1
HAVING
(((Matchs.Team1)="Cheap Shotz"
Or (Matchs.Team1)="Hookys Crew"
Or (Matchs.Team1)="Kannaries"
Or (Matchs.Team1)="Maymmuk Mitji"
Or (Matchs.Team1)="On Queue"
Or (Matchs.Team1)="Pot n Rollers"));


Is it possible for add team2 the same as above but somehow put it all in the same query??

Thanks

Jonathan VH replied to Wayne Waddingham on 18-Oct-09 09:59 AM

I cannot help you if you do not answer the questions in my first post.

Why do you need the HAVING clause? Is there also a requirement to limit the result set to specific teans? If so, I suggest using the IN operator.

grabe the db file from above - Wayne Waddingham replied to Jonathan VH on 18-Oct-09 10:14 AM
I dont undersstand half this sql stuff, the database is there to download in my post above it will show you how it works, and the having is what access does i have no idea why. If you download the db you will understand what i am meaning i am sure. its very simple setup.
Jonathan VH replied to Wayne Waddingham on 18-Oct-09 10:31 AM
I cannot help you if you do not answer the questions in my first post.
. - Wayne Waddingham replied to Jonathan VH on 18-Oct-09 10:51 AM

Ok sorry I dont mean to sound like a arrogant **** but I have spent like 20 hours reading sql stuff trying this trying that and still no futher ahead then when i first started, the last sql i posted is exactly what i want but do not know how to incorperate the exact same sql but referencing team2 and making it all one sql,

All i need is for each team cheap shotz, hookys crew, etc, etc to show won x lost x and drew x games etc from the matches table
Matchs.Won will only have Won or Draw in it,
Matchs.Lost will only have Lost or Draw in it.

 I have used Dsum to count Total games won in each match ok for each team, maybe i can use Dcount to achieve the Matchs Won Totals

Thanks very much for trying to help me.

Jonathan VH replied to Wayne Waddingham on 18-Oct-09 11:16 AM

Okay, you answered my second question. How about the others:

1) How are the Player1 and Player2 columns related? Are these matches always between two players and these are the two players in the match? Does a player always belong to the same team, or can players change teams during the season?

3) Is Player1 (and therefore Team1?) always the winner and Player2 (and therefore Team2?) always the loser (unless there is a draw)? Why is that? Is that the design spec?

4) Why do you need the Won and Lost columns? Is this just there to distinguish which matches are draws?

Your subsequent posts raise some more questions (one of which in my second post).

5) Are you interested in player statistics or team statistics? Your first queries include the players, and I assume there is a many-to-one relationship between players and teams, but your last query does not include players.

6) Do you wish to know how to design a logical database or are you only interested in getting a specific query that works with your design?

Your going think wtf lol - Wayne Waddingham replied to Jonathan VH on 18-Oct-09 01:00 PM

Hmm after laying it out like this It is wrong/made it very hard for myself now, but I have already built a .asp website based on the db and only reason I decided to make a front end for the db is to allow them to have the db with them on the night when the play enter results and upload it to the web each week and hopefully find a easier way to get the totals. But apparently not, access is no easier for me than the vb/asp web was when the db is designed wrong L, But it is my first attempt at anything in access, so I guess it’s a learning curve. It will never have many users using it just the person entering the matches and game results.
And yes I would like no how to design them the way the experts would have but I don’t have that knowledge.

Rounds            (Played Weekly)

Matches           3 per week
                        (Cheap Shotz v Kannaries)
                        (Pot n Roller v Hookys Crew)
                        (On Queue v Maymmuk Mitji)
                        etc change them around each week etc

Games             10 per Match (Singles 7 and 3 Doubles)

Matches Results:
Matchs.Team1:Cheap Shotz, Matchs.Won=Won or Draw
Matchs.Team2:Kannaries, Matchs.Lost=Lost or Draw

Games Results:
Games.Team1: They Pick The team because it needs to be the wining team only Games.Player1: They pick the player  incase someone swaps teams on the night
Games.Won:  Already has default value of  Won (Games never have a Draw)

Games.Team2: They Pick The team because it needs to be the loosing team only
Games.Player2: They pick the player  incase someone swaps teams on the night
Games.Lost:  Already has default value of  Lost (Games never have a Draw)

Games of doubles are treated as a single for each player but with a flag on the games table showing if it is singles or doubles they won or lost in

ie: Cheap Shotz  matches Won total comes from
Matchs.Won WHERE Matchs.Team1=Cheap Shotz AND Matchs.Won=Won

ie: Cheap Shotz  matches Drawn total comes from
Matchs.Won AND Matchs.Lost WHERE Matchs.Team1=Cheap Shotz  AND  Matchs.Won=Draw AND Matchs.Team2=Cheap Shotz  AND Matchs.Lost=Draw

ie: Cheap Shotz  matches Lost total comes from
Matchs.Lost WHERE Matchs.Team2=Cheap Shotz AND Matchs.Lost=Lost

Totals for matches and games hopefully now make more sense.

Jonathan VH replied to Wayne Waddingham on 18-Oct-09 01:57 PM

Because of that design (and because of the limitations of Access's implementation of SQL), you're going to have to use something quite complex to get the desired result set. You need a self-join and that join needs to be a FULL OUTER JOIN, which Access doesn't have. Here's an example query using a LEFT OUTER JOIN, although this will be missing values if there's a team with no wins:

SELECT Sum(IIf(w.Won="Won",1,0)) AS xWon, Sum(IIf(w.Won="Draw",1,0)) + Sum(IIf(w.Won="Draw",1,0)) AS xDraw, Sum(IIf(l.Lost="Lost",1,0)) AS xLost, Team1 AS Team
FROM Matchs AS w LEFT JOIN Matches AS l ON w.Team1 = l.Team2
GROUP BY w.Team1

You should not use this, however, because it ignores the results of a team with no wins (that's why it requires a FULL OUTER JOIN). I have never used Access, although I have its Help information, so I won't attempt to write a real Access query, but it's apparently possible to simulate this by using intermediate queries. You'll also need to COALESCE the team name in both the the select list and the group by clause, but Access doesn't have the COALESCE function, so I believe you'll have to use Nz(). I.e., if Access supported FULL OUTER JOIN, you'd need something like this:

SELECT Sum(IIf(w.Won="Won",1,0)) AS xWon, Sum(IIf(w.Won="Draw",1,0)) + Sum(IIf(w.Won="Draw",1,0)) AS xDraw, Sum(IIf(l.Lost="Lost",1,0)) AS xLost, Nz(Team1,Team2) AS Team
FROM Matchs AS w FULL OUTER JOIN Matches AS l ON w.Team1 = l.Team2
GROUP BY Nz(w.Team1,l.Team2)

When designing a database, one should keep in mind what output one will need. Your design (obviously) makes the desired output nearly impossible to get. I'm also dubious about the utility of the result set you're asking for, as it doesn't give you the win/loss record of the teams, but instead the cumulative totals of the individual games (otherwise the player information doesn't belong in this table). E.g., if team A loses two matches to team B by the score of 6-7 each time but wins the third meeting by a score of 13-0, this query will show their totals as 25-14, which belies the match record of 1-2.

BTW, I reserve the right to be the arrogant **** on any thread in which I participate, so don't think you're going to usurp that role.

ok - Wayne Waddingham replied to Jonathan VH on 18-Oct-09 02:16 PM

Ok, Thank you very much for your time and input. I guess i have 2 choices one start all over again and try to come up with a better design or use multiple querys and dsum and dcount to achieve the totals i need.

I have now idea what your BTW actually means fyi

But again Thank You