query grouping issue

[pat67]

Hello All,

I am running a pool league and I am trying to create a query to show
each player's record vs other players. By the same token, each team's
record vs other teams. I have a table that shows each week's results,
but I cannot figure out how to create the query I want. Any help
would
be greatly appreciated. Thanks


Here is what i am looking for

Player Opponent Won Lost

Joe Mike 5 3
Joe Bob 4 2

etc for every player.

 

[pat67]

Pat,

Would be really helpful to know your current table structure, and maybe a
couple of lines of data so we can actually test what we try.

Dale











--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200907/1- Hide quoted text -

- Show quoted text -

The structure is as follows

Date Winner Team Loser Team1

7/22 Joe A Bob B
7/22 Joe A Mike B
7/22 Bob B Joe A

Where i would show and overall record for Joe of 2-1. I wand to also
be able to show that Joe is 1-1 vs. Bob and 1-0 vs. Mike. I have no
problem getting the wins and losses in separate queries. I just cannot
seem to get them together.

 

[pat67]

At first look, I'd recommend a data structure that looks more like the
following (my not be complete).

1.  Teams - contains a TeamID, TeamName, and any other pertinent info for the
team
2.  Players - Contains PlayerID, LastName, FirstName, Phone, email, ...
3.  TeamPlayers - Contains TeamID, PlayerID, FromDate, ThruDate (to
accomodate the same player moving from one team to another), assumes thata
player cannot be on more than one team at a time.
4.  Matches - Contains MatchID, MatchDate, MatchTime, Location, Table, ....
(If all the matches are at the same location, probably don't need the
location field.  On the other hand, if they aren't, then you would alsoneed
a Locations table with LocationID and LocationName)
5.  MatchPlayers - Contains MatchID, PlayerID, Winner (Yes/No) or you could
use Score if score is important.

Do you have an autonumber (MatchID or something similiar) field associated
with each of these records?




--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200907/1

I have Player and Team IDs but not for matches or locations. Even if I
were to do that, how does that help? I do not have an autonumber field
associated with the results. I can add that.