'Joining' 2 querries

[jlwood44]

I am working with chess matches from different schools. I have a query with:
1) Match ID (PK)
2) Player1 ID
3) Player2 ID
4) Player1 wins
5) Player2 wins
6) Draws
7) Winning team.

I would like to combine this with a querry with:
(This 2nd querry show the 2nd players results, so show every individual
results)
1) Match ID
2) Player2 ID
3) Player1 ID
4) Player2 wins
5) Player1 wins
6) Draws
7) Winning team.

How would this be done? How about the PK? If there are 15 matches, I want
the combined querry to have 30 entries.
Part of the reason for doing this is that sometimes a player is listed as
player 1 and sometimes as player 2. By combining the querries, I can easily
show (and group) all of 'Joe Smith's matches and show who he played and the
results of each match.

In advance, thank you very much for your help.

 

[Ed Warren]

Sounds like what you want is a result set that looks something like the
following:

MatchID
PlayerID
Results: won;lost;draw
Team: winning; not winning

Look at help for a UNION QUERY. You will want to have the first select
statement pull from the Player1 column, then the second pull from the Player
2 column.

You will end up with an sql statement looking something like the following:

Select MatchID, [Player1 ID] as PlayerID, (you will have to build some logic
for the results) as Results, (logic for team) as TEAM from MatchTable
Union
Select MatchID, [Player2 ID] as PlayerID, (logic again) as Results (logic
for team) as TEAM from MatchTable

Ed Warren

 

[jlwood44]

It looks like you very much understand the problem. And you understand the
solution. What happens with my Match ID (which is the Primary Key) when I
union the querries? Will it keep counting on my Autonumber and make my 15
matches into 30 records?

How will my second entry (field) be named when the first 15 come from
Player1 ID and the next come from Player2 ID? When/if can I rename the field?

Sounds like what you want is a result set that looks something like the
following:

MatchID
PlayerID
Results: won;lost;draw
Team: winning; not winning

Look at help for a UNION QUERY. You will want to have the first select
statement pull from the Player1 column, then the second pull from the Player
2 column.

You will end up with an sql statement looking something like the following:

Select MatchID, [Player1 ID] as PlayerID, (you will have to build some logic
for the results) as Results, (logic for team) as TEAM from MatchTable
Union
Select MatchID, [Player2 ID] as PlayerID, (logic again) as Results (logic
for team) as TEAM from MatchTable

Ed Warren

I am working with chess matches from different schools. I have a query
with:
1) Match ID (PK)
2) Player1 ID
3) Player2 ID
4) Player1 wins
5) Player2 wins
6) Draws
7) Winning team.

I would like to combine this with a querry with:
(This 2nd querry show the 2nd players results, so show every individual
results)
1) Match ID
2) Player2 ID
3) Player1 ID
4) Player2 wins
5) Player1 wins
6) Draws
7) Winning team.

How would this be done? How about the PK? If there are 15 matches, I want
the combined querry to have 30 entries.
Part of the reason for doing this is that sometimes a player is listed as
player 1 and sometimes as player 2. By combining the querries, I can
easily
show (and group) all of 'Joe Smith's matches and show who he played and
the
results of each match.

In advance, thank you very much for your help.

 

[Ed Warren]

Basic select queries do nothing to the tables, they just return the data in
a more usable format
Run the query and see what results you get, then change the query as
required to give you the desired result. This is the power of a database,
you store only the data you need, then transform it with a query to get it
into the form and format you want to see.

Ed Warren.

It looks like you very much understand the problem. And you understand
the
solution. What happens with my Match ID (which is the Primary Key) when I
union the querries? Will it keep counting on my Autonumber and make my 15
matches into 30 records?

How will my second entry (field) be named when the first 15 come from
Player1 ID and the next come from Player2 ID? When/if can I rename the
field?

Sounds like what you want is a result set that looks something like the
following:

MatchID
PlayerID
Results: won;lost;draw
Team: winning; not winning

Look at help for a UNION QUERY. You will want to have the first select
statement pull from the Player1 column, then the second pull from the
Player
2 column.

You will end up with an sql statement looking something like the
following:

Select MatchID, [Player1 ID] as PlayerID, (you will have to build some
logic
for the results) as Results, (logic for team) as TEAM from MatchTable
Union
Select MatchID, [Player2 ID] as PlayerID, (logic again) as Results (logic
for team) as TEAM from MatchTable

Ed Warren

"(e-mail address removed)" <[email protected]>
wrote
in message news:[email protected]...

I am working with chess matches from different schools. I have a query
with:
1) Match ID (PK)
2) Player1 ID
3) Player2 ID
4) Player1 wins
5) Player2 wins
6) Draws
7) Winning team.

I would like to combine this with a querry with:
(This 2nd querry show the 2nd players results, so show every individual
results)
1) Match ID
2) Player2 ID
3) Player1 ID
4) Player2 wins
5) Player1 wins
6) Draws
7) Winning team.

How would this be done? How about the PK? If there are 15 matches, I
want
the combined querry to have 30 entries.
Part of the reason for doing this is that sometimes a player is listed
as
player 1 and sometimes as player 2. By combining the querries, I can
easily
show (and group) all of 'Joe Smith's matches and show who he played and
the
results of each match.

In advance, thank you very much for your help.