Knockout Competition Listing - Simple Query? 1

[Binnit]

I have a list of persons who are to play in a tournament.

How can I create a query that will use the master list to create a table containing all the combinations of names drawn to play against each other?

eg

Fred
Barney
Joe

resulting in

Fred v Barney
Fred v Joe
Barney v Joe

I'm sure this must be easy but not sure where to start.

Any help appreciated.
Thanks

If IT ain’t working Binnit and Reboot

 

[MichaelRed]

There are several (numerous?) threads re "round-robin" schedualing in these fora (Tek-Tips). Although I'm not aware of any which strictly a query (SQL) for the generation. Most are caoncened with the actual schedual (meeting time and place) as well as the participants.

A simple query CAN (I think) provide the 'round-robbin' pairs of names:

SELECT DISTINCTROW qryNameLIst.CustName, qryNameLIst_1.CustName
FROM qryNameLIst, qryNameLIst AS qryNameLIst_1
WHERE (((qryNameLIst.CustName)<>[qryNameList_1].[CustName]))
WITH OWNERACCESS OPTION;

OPf course you need to adjust the record source names and the field name in all locations. Also please note that the source consist of the single field; there is NO join clause; and the results set includes both pais sets (e.g. Home v. Away). If you want to restrict the set to having only one of the pair, this soloution will not work. Further, this does not include any schedualing information, so unless all matches are simply on an as available basis, more "work needs to be done to set up the time and place of each meeting.





MichaelRed

 

[Binnit]

MichaelRed
Thanks for your reply, I will search the forum under "Round Robin", my attempts so far have resulted in2 queries using the main table, the results are as follows:-

Name	NewName	Joint
Fred	JOe	Fred v JOe
Barney	JOe	Barney v JOe
Alex	JOe	Alex v JOe
JOe	Fred	JOe v Fred
Barney	Fred	Barney v Fred
Alex	Fred	Alex v Fred
JOe	Barney	JOe v Barney
Fred	Barney	Fred v Barney
Alex	Barney	Alex v Barney
JOe	Alex	JOe v Alex
Fred	Alex	Fred v Alex
Barney	Alex	Barney v Alex

Clearly there is not point having Fred V Joe and then Joe V Fred so thats the next part to work on.

Thanks for your tip


If IT ain’t working Binnit and Reboot

 

[MichaelRed]

" ... Clearly there is not point having Fred V Joe and then Joe V Fred ... "

But that is the essence of a 'round-robbin' schedual. Searching on that (and other common schedualing term) term will produce a number of threads, however I believe they are all intended to generate the 'round-robbin' results, so will need modification to suit your purpose.





MichaelRed

 

[Golom]

To get around having both "Joe Vs Fred" and "Fred Vs Joe", use "<" rather than "<>" like this

SELECT  
    (Q1.CustName & " Vs " & Q2.CustName) As [TheMatch]

FROM qryNameLIst As Q1, qryNameLIst As Q2

WHERE Q1.CustName < Q2.CustName

 

[Binnit]

Golom
This does exactly what I need to get going- absolute genious!

I just wish I knew why it works and maybe that will become obvious as I take it further, the next step is to have the players seeded/ranked etc and continue with the knockout scheduling once first/second rounds are played etc.

Thanks very much, I have wasted loads of time on this but alwasy believed that it would have a simple solution albeit clearly beyond my current ability!

Star for you.

MichaelRed
I may have misunderstood the definition of Round Robin when raising this post, I had manged to achieve the same results as your sql but in much more of a cumbersome fashion, I am therefore pleased to see how you did it in more simplistic terms, however, the results produced by Golom are actually what I was seeking in my mind.
Thanks for your valuable help too.

I must get to grips with more of this sql query building, any recommended books to help me?

If IT ain’t working Binnit and Reboot

 

[MichaelRed]

The (SQL) thing is .. It is one of those 'deceptively simple' games. It only has a few keywords ... but they can be (legally / appropiatly) mixed and matched in so many ways that the sum is MUCH greater than the parts. In particular, SubQueries can be placed most ant/ever-where sometimes (aparently) requireing 'predicates' and occassionally not. Then, again, SubQueries and Joins are similar in results but different in syntax -and 'vendor specific "extensions" make the concept of "A SAQ 'language' more of a etheral goal than a 'book'. I have (and occassionally use) "The Complete Reference SQL" (Groff & Weinberg) from Osborne, ISBN: 0-07-211845-8B. It uses ~~~ 1/2 of the test on various SQL constructs, with ~~ 1/3 of that illustrating the differences between the vendors it includes, The 'other half' discusses 'other aspects', including hte tid-bit that (as of the writing) there was NO vendor who completly supported the 'standard' (although many of the deviations are refered to as 'vendor specific extensions'. I cite this 'reference work' not as a recommendation but as an example of the literature which is commercially available. My actual advice is to spend some time in your local bookstore and thoroughly review several boks on the topic. Find one or more which are at least generally comprehensible (to you) but which offer material which you are not familiar with and would like to know more about. Then, make sure that it atleast acknowledges the vendor specific differences, and hopefully features the specific vendor of the products which you commonly use.





MichaelRed

 

[Binnit]

Thanks for the tips MichaelRed

Now to find a suitable bookstore, most of our locals do not stock much more than the "Dummies" series.



If IT ain’t working Binnit and Reboot