|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a large list of people that have ranks and are grouped by location.
From this information, I need to make a tournament schedule. Each player
needs to compete against another player from a different location and play a
different rank. For example, I would like a #1 seeded rank to play a #2
seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
baffled. Thanks for the help!
|
|
A 3 query solution might work. First join each player to each other player
where the first player's rank is higher (smaller number) than the second
player's and their locations differ:
Qry1:
SELECT
P1.Player As Player1, P2.Player AS Player2, P2.Rank2
FROM Players AS P1, Players AS P2
WHERE P1.Location <> P2.Location
AND P1.Rank < P2.Rank;
Then select the rows from the first query's result set where the second
player's rank is the highest (MIN number) of all the second players joined to
each first player:
Qry2:
SELECT Player1, Player2
FROM Qry1 As Q1
WHERE Rank2 =
(SELECT MIN(Rank2)
FROM Qry1 As Q2
WHERE Q2.Player1= Q1.Player1);
Finally throw out all the rows where the second player is already in the
second query's result set as a first player.
Qry3:
SELECT Player1, Player2
FROM Qry2 As Q1
WHERE NOT EXISTS
(SELECT *
FROM Qry2 AS Q2
WHERE Q2.Player2 = Q1.Player1);
If I've got the logic right Qry3 should give you the final pairings of
players. This is very much off the top of my head, however, so without
testing it against some data I would not be completely confident that I've
covered all bases.
Ken Sheridan
Stafford, England
"Chad" wrote:
[Quoted Text] > I have a large list of people that have ranks and are grouped by location.
> From this information, I need to make a tournament schedule. Each player
> needs to compete against another player from a different location and play a
> different rank. For example, I would like a #1 seeded rank to play a #2
> seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
> baffled. Thanks for the help!
|
|
I'm not very familiar with Access, can you help me with setting this up? We
can just use generic data. I can make some up if you like. Thanks for the
feedback.
"Ken Sheridan" wrote:
[Quoted Text] > A 3 query solution might work. First join each player to each other player
> where the first player's rank is higher (smaller number) than the second
> player's and their locations differ:
>
> Qry1:
>
> SELECT
> P1.Player As Player1, P2.Player AS Player2, P2.Rank2
> FROM Players AS P1, Players AS P2
> WHERE P1.Location <> P2.Location
> AND P1.Rank < P2.Rank;
>
> Then select the rows from the first query's result set where the second
> player's rank is the highest (MIN number) of all the second players joined to
> each first player:
>
> Qry2:
>
> SELECT Player1, Player2
> FROM Qry1 As Q1
> WHERE Rank2 =
> (SELECT MIN(Rank2)
> FROM Qry1 As Q2
> WHERE Q2.Player1= Q1.Player1);
>
> Finally throw out all the rows where the second player is already in the
> second query's result set as a first player.
>
> Qry3:
>
> SELECT Player1, Player2
> FROM Qry2 As Q1
> WHERE NOT EXISTS
> (SELECT *
> FROM Qry2 AS Q2
> WHERE Q2.Player2 = Q1.Player1);
>
> If I've got the logic right Qry3 should give you the final pairings of
> players. This is very much off the top of my head, however, so without
> testing it against some data I would not be completely confident that I've
> covered all bases.
>
> Ken Sheridan
> Stafford, England
>
> "Chad" wrote:
>
> > I have a large list of people that have ranks and are grouped by location.
> > From this information, I need to make a tournament schedule. Each player
> > needs to compete against another player from a different location and play a
> > different rank. For example, I would like a #1 seeded rank to play a #2
> > seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
> > baffled. Thanks for the help!
>
|
|
Firstly, I've just noticed a mistake in qry1; it should be:
SELECT
P1.Player AS Player1, P2.Player AS Player2, P2.Rank AS Rank2
FROM Players AS P1, Players AS P2
WHERE P1.Location <> P2.Location
AND P1.Rank < P2.Rank;
Open your database and go to the Queries page of the main database window.
Double click on 'Create query in Design View'. This brings up the 'add
table' dialogue which you can close without doing anything in it. Then from
the View menu select SQL View. The SQL window will just have SELECT; in it.
Copy the SQL for qry1 from above and paste it into the SQL window in place of
what's there. You then need to change the names of the table and fields in
the SQL to your own; I've assumed the table's called Players and the fields
are called Player, Location and Rank, so change all these in the SQL to
whatever your table and fields are actually called. Remember that if a table
or field name includes spaces or other special characters such as the # sign
you need to wrap the name in square brackets, e.g. [Players List] or [Rank#].
If in doubt put brackets around the table and field names regardless. Don't
change any of the 'aliases', though; they are the names following the AS
keyword or the abbreviated alias table names P1, P2 before the field names.
When you've done this switch to datasheet view to check that it works. If
its OK you'll get a very large number of pretty meaningless combinations of
players, but don't worry about that; this query is just the first stage and
you won't need to open it at all once the other two are set up. save it as
qry1.
Repeat the above for qry2 and qr3, but this time you don't need to change
any table or field names as the ones used in these queries' result sets are
the names given to them as aliases in qry1 by the AS keyword. Open qry2 once
you've designed it to test it. This will return less rows that qry1, but
they still won't make much sense. qry3 is the only one you'll need to open
to get the final pairings of players. If it doesn't do as hoped post back as
I can easily set up a table and insert some dummy data to test, and hopefully
debug, the queries.
Ken Sheridan
Stafford, England
"Chad" wrote:
[Quoted Text] > I'm not very familiar with Access, can you help me with setting this up? We
> can just use generic data. I can make some up if you like. Thanks for the
> feedback.
>
> "Ken Sheridan" wrote:
>
> > A 3 query solution might work. First join each player to each other player
> > where the first player's rank is higher (smaller number) than the second
> > player's and their locations differ:
> >
> > Qry1:
> >
> > SELECT
> > P1.Player As Player1, P2.Player AS Player2, P2.Rank2
> > FROM Players AS P1, Players AS P2
> > WHERE P1.Location <> P2.Location
> > AND P1.Rank < P2.Rank;
> >
> > Then select the rows from the first query's result set where the second
> > player's rank is the highest (MIN number) of all the second players joined to
> > each first player:
> >
> > Qry2:
> >
> > SELECT Player1, Player2
> > FROM Qry1 As Q1
> > WHERE Rank2 =
> > (SELECT MIN(Rank2)
> > FROM Qry1 As Q2
> > WHERE Q2.Player1= Q1.Player1);
> >
> > Finally throw out all the rows where the second player is already in the
> > second query's result set as a first player.
> >
> > Qry3:
> >
> > SELECT Player1, Player2
> > FROM Qry2 As Q1
> > WHERE NOT EXISTS
> > (SELECT *
> > FROM Qry2 AS Q2
> > WHERE Q2.Player2 = Q1.Player1);
> >
> > If I've got the logic right Qry3 should give you the final pairings of
> > players. This is very much off the top of my head, however, so without
> > testing it against some data I would not be completely confident that I've
> > covered all bases.
> >
> > Ken Sheridan
> > Stafford, England
> >
> > "Chad" wrote:
> >
> > > I have a large list of people that have ranks and are grouped by location.
> > > From this information, I need to make a tournament schedule. Each player
> > > needs to compete against another player from a different location and play a
> > > different rank. For example, I would like a #1 seeded rank to play a #2
> > > seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
> > > baffled. Thanks for the help!
> >
|
|
This works great! Thank you so much for your help. You are truly a
life-saver!
"Ken Sheridan" wrote:
[Quoted Text] > Firstly, I've just noticed a mistake in qry1; it should be:
>
> SELECT
> P1.Player AS Player1, P2.Player AS Player2, P2.Rank AS Rank2
> FROM Players AS P1, Players AS P2
> WHERE P1.Location <> P2.Location
> AND P1.Rank < P2.Rank;
>
> Open your database and go to the Queries page of the main database window.
> Double click on 'Create query in Design View'. This brings up the 'add
> table' dialogue which you can close without doing anything in it. Then from
> the View menu select SQL View. The SQL window will just have SELECT; in it.
> Copy the SQL for qry1 from above and paste it into the SQL window in place of
> what's there. You then need to change the names of the table and fields in
> the SQL to your own; I've assumed the table's called Players and the fields
> are called Player, Location and Rank, so change all these in the SQL to
> whatever your table and fields are actually called. Remember that if a table
> or field name includes spaces or other special characters such as the # sign
> you need to wrap the name in square brackets, e.g. [Players List] or [Rank#].
> If in doubt put brackets around the table and field names regardless. Don't
> change any of the 'aliases', though; they are the names following the AS
> keyword or the abbreviated alias table names P1, P2 before the field names.
> When you've done this switch to datasheet view to check that it works. If
> its OK you'll get a very large number of pretty meaningless combinations of
> players, but don't worry about that; this query is just the first stage and
> you won't need to open it at all once the other two are set up. save it as
> qry1.
>
> Repeat the above for qry2 and qr3, but this time you don't need to change
> any table or field names as the ones used in these queries' result sets are
> the names given to them as aliases in qry1 by the AS keyword. Open qry2 once
> you've designed it to test it. This will return less rows that qry1, but
> they still won't make much sense. qry3 is the only one you'll need to open
> to get the final pairings of players. If it doesn't do as hoped post back as
> I can easily set up a table and insert some dummy data to test, and hopefully
> debug, the queries.
>
> Ken Sheridan
> Stafford, England
>
> "Chad" wrote:
>
> > I'm not very familiar with Access, can you help me with setting this up? We
> > can just use generic data. I can make some up if you like. Thanks for the
> > feedback.
> >
> > "Ken Sheridan" wrote:
> >
> > > A 3 query solution might work. First join each player to each other player
> > > where the first player's rank is higher (smaller number) than the second
> > > player's and their locations differ:
> > >
> > > Qry1:
> > >
> > > SELECT
> > > P1.Player As Player1, P2.Player AS Player2, P2.Rank2
> > > FROM Players AS P1, Players AS P2
> > > WHERE P1.Location <> P2.Location
> > > AND P1.Rank < P2.Rank;
> > >
> > > Then select the rows from the first query's result set where the second
> > > player's rank is the highest (MIN number) of all the second players joined to
> > > each first player:
> > >
> > > Qry2:
> > >
> > > SELECT Player1, Player2
> > > FROM Qry1 As Q1
> > > WHERE Rank2 =
> > > (SELECT MIN(Rank2)
> > > FROM Qry1 As Q2
> > > WHERE Q2.Player1= Q1.Player1);
> > >
> > > Finally throw out all the rows where the second player is already in the
> > > second query's result set as a first player.
> > >
> > > Qry3:
> > >
> > > SELECT Player1, Player2
> > > FROM Qry2 As Q1
> > > WHERE NOT EXISTS
> > > (SELECT *
> > > FROM Qry2 AS Q2
> > > WHERE Q2.Player2 = Q1.Player1);
> > >
> > > If I've got the logic right Qry3 should give you the final pairings of
> > > players. This is very much off the top of my head, however, so without
> > > testing it against some data I would not be completely confident that I've
> > > covered all bases.
> > >
> > > Ken Sheridan
> > > Stafford, England
> > >
> > > "Chad" wrote:
> > >
> > > > I have a large list of people that have ranks and are grouped by location.
> > > > From this information, I need to make a tournament schedule. Each player
> > > > needs to compete against another player from a different location and play a
> > > > different rank. For example, I would like a #1 seeded rank to play a #2
> > > > seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
> > > > baffled. Thanks for the help!
> > >
>
|
|
One additional thing for you, since you seem to be so well versed in access.
The 3rd query gives me all the possibilities. Is there a way to take those
possibilities and generate an actual bracket list? I can send you what I've
done so far if that would be helpful, or psudeo data is fine to use. Thanks
again for your help!
"Ken Sheridan" wrote:
[Quoted Text] > Firstly, I've just noticed a mistake in qry1; it should be:
>
> SELECT
> P1.Player AS Player1, P2.Player AS Player2, P2.Rank AS Rank2
> FROM Players AS P1, Players AS P2
> WHERE P1.Location <> P2.Location
> AND P1.Rank < P2.Rank;
>
> Open your database and go to the Queries page of the main database window.
> Double click on 'Create query in Design View'. This brings up the 'add
> table' dialogue which you can close without doing anything in it. Then from
> the View menu select SQL View. The SQL window will just have SELECT; in it.
> Copy the SQL for qry1 from above and paste it into the SQL window in place of
> what's there. You then need to change the names of the table and fields in
> the SQL to your own; I've assumed the table's called Players and the fields
> are called Player, Location and Rank, so change all these in the SQL to
> whatever your table and fields are actually called. Remember that if a table
> or field name includes spaces or other special characters such as the # sign
> you need to wrap the name in square brackets, e.g. [Players List] or [Rank#].
> If in doubt put brackets around the table and field names regardless. Don't
> change any of the 'aliases', though; they are the names following the AS
> keyword or the abbreviated alias table names P1, P2 before the field names.
> When you've done this switch to datasheet view to check that it works. If
> its OK you'll get a very large number of pretty meaningless combinations of
> players, but don't worry about that; this query is just the first stage and
> you won't need to open it at all once the other two are set up. save it as
> qry1.
>
> Repeat the above for qry2 and qr3, but this time you don't need to change
> any table or field names as the ones used in these queries' result sets are
> the names given to them as aliases in qry1 by the AS keyword. Open qry2 once
> you've designed it to test it. This will return less rows that qry1, but
> they still won't make much sense. qry3 is the only one you'll need to open
> to get the final pairings of players. If it doesn't do as hoped post back as
> I can easily set up a table and insert some dummy data to test, and hopefully
> debug, the queries.
>
> Ken Sheridan
> Stafford, England
>
> "Chad" wrote:
>
> > I'm not very familiar with Access, can you help me with setting this up? We
> > can just use generic data. I can make some up if you like. Thanks for the
> > feedback.
> >
> > "Ken Sheridan" wrote:
> >
> > > A 3 query solution might work. First join each player to each other player
> > > where the first player's rank is higher (smaller number) than the second
> > > player's and their locations differ:
> > >
> > > Qry1:
> > >
> > > SELECT
> > > P1.Player As Player1, P2.Player AS Player2, P2.Rank2
> > > FROM Players AS P1, Players AS P2
> > > WHERE P1.Location <> P2.Location
> > > AND P1.Rank < P2.Rank;
> > >
> > > Then select the rows from the first query's result set where the second
> > > player's rank is the highest (MIN number) of all the second players joined to
> > > each first player:
> > >
> > > Qry2:
> > >
> > > SELECT Player1, Player2
> > > FROM Qry1 As Q1
> > > WHERE Rank2 =
> > > (SELECT MIN(Rank2)
> > > FROM Qry1 As Q2
> > > WHERE Q2.Player1= Q1.Player1);
> > >
> > > Finally throw out all the rows where the second player is already in the
> > > second query's result set as a first player.
> > >
> > > Qry3:
> > >
> > > SELECT Player1, Player2
> > > FROM Qry2 As Q1
> > > WHERE NOT EXISTS
> > > (SELECT *
> > > FROM Qry2 AS Q2
> > > WHERE Q2.Player2 = Q1.Player1);
> > >
> > > If I've got the logic right Qry3 should give you the final pairings of
> > > players. This is very much off the top of my head, however, so without
> > > testing it against some data I would not be completely confident that I've
> > > covered all bases.
> > >
> > > Ken Sheridan
> > > Stafford, England
> > >
> > > "Chad" wrote:
> > >
> > > > I have a large list of people that have ranks and are grouped by location.
> > > > From this information, I need to make a tournament schedule. Each player
> > > > needs to compete against another player from a different location and play a
> > > > different rank. For example, I would like a #1 seeded rank to play a #2
> > > > seeded rank and a #3 seeded rank, etc. Any thoughts where to start? I am
> > > > baffled. Thanks for the help!
> > >
>
|
|
I think I'd be inclined to do that by sending the data to a Word document.
One possibility would be to insert the text into bookmarks in the Word
document. This would mean a fixed number of competitors and hence a fixed
number of rounds of course unless you have a number of Word templates to
handle different numbers. The Word bookmarks could have names HomePlayer1,
VisitingPlayer1, HomePlayer2, VisitingPlayer2 and so on. It doesn't matter
if they are actually home or visiting players, its just a way of
differentiating them. You could then loop through a recordset of qry3's
result set and insert the Player1 and Player2 names at the correct bookmarks
as :
n= 0
Do While Not rst.EOF
n = n+1
strBookMark = "HomePlayer" & n
<code to insert Player1 at bookmark>
strBookMark = "VisitingPlayer" & n
<code to insert Player1 at bookmark>
rst.MoveNext
Loop
The other possibility would be to insert the data into the first column of a
predefined Word table in which the brackets are in cells in the columns to
the right.
There is a demo of mine showing various Access-to-Word automation
operations, including inserting text at bookmarks and filling a Word table,
at the following link:
http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps
In the demo the bookmarks are filled with values from a form rather than a
recordset, but the code for inserting the text into the bookmarks would be
much the same, with code like that in the CreateLetter procedure opening the
Word document, and then calling the InsertAtBookmarks procedure twice in each
iteration of a loop through the recordset.
Ken Sheridan
Stafford, England
"Chad" wrote:
[Quoted Text] > One additional thing for you, since you seem to be so well versed in access.
> The 3rd query gives me all the possibilities. Is there a way to take those
> possibilities and generate an actual bracket list? I can send you what I've
> done so far if that would be helpful, or psudeo data is fine to use. Thanks
> again for your help!
>
|
|
|