If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'Joining' 2 querries
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. |
#2
|
|||
|
|||
'Joining' 2 querries
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 " wrote in message ... 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. |
#3
|
|||
|
|||
'Joining' 2 querries
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? "Ed Warren" wrote: 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 " wrote in message ... 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. |
#4
|
|||
|
|||
'Joining' 2 querries
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. " wrote in message ... 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? "Ed Warren" wrote: 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 " wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Joining 2 table | ardi | Running & Setting Up Queries | 3 | January 9th, 2006 10:05 AM |
Some querries not showing in Word Mailmerge | Jeff | General Discussion | 4 | October 24th, 2005 04:03 PM |
Joining 3 tables in a query | Chrismja | Running & Setting Up Queries | 1 | March 17th, 2005 07:31 PM |
joining multiple queries | auntiejack | Running & Setting Up Queries | 2 | September 16th, 2004 11:56 AM |
Joining two count results | Adam Pedder | Running & Setting Up Queries | 4 | June 14th, 2004 04:59 PM |