Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Posts
    18

    Unanswered: combining two records from the same table

    Hello,

    I'm constucting a application to organise a sport tornament. The problem I run into is the following:

    I got one table with all teams (team A, team B, etc.) Those teams have to play against eachother. So I want to Create a table with all the matches that have to be played. Somthing like this:
    Match, team 1, team 2, score t1, score t2
    1, team A, team B, 3, 0
    2, team A, team C, 2, 3
    3, team B, team C, 2, 2

    So when I have a poule I want Access to create all matches. So when using a pule with 4 teams there have to be 6 matches created (if you don't use home and away matches)

    Can someone Help!!!!!!
    Thanks

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    how about this, only works if they have a sequential id field

    Code:
    Sub MakeTable()
        DoCmd.GoToRecord , , acLast
        max = Me.ID.Value
        For i = 1 To max
            For j = 1 To max
                If i <> j Then DoCmd.RunSQL "INSERT INTO Matches( team1, team2, score1, score2 )" & _
                " SELECT '" & i & "' AS team1, '" & j & "' AS team2, '" & 0 & "' AS score1, '" & 0 & _
                "' AS score2;"
            Next j
        Next i
        
    End Sub
    this would fill the table with the matches
    you could do this with a recordset but i haven't tried using them yet so can't offer the correct syntaxs for that method
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select a.teamname, b.teamname
    from teams a, teams b
    where a.teamname < b.teamname


    rudy

  4. #4
    Join Date
    Oct 2002
    Posts
    18

    Looks promising, but .....

    Thanks for the quick respons,

    I understand the way the "for i =1 To max and for j = 1 to max" works but two thinks I don't get.

    First I'm not sure were to place the code. I think the "me" must relate to the table including the teams but isn't it nesecery to declare the variable max??

    Second, I'm not sure how this code take the propper field (the name of the team from the team table) in the team1 field of the table "matches"

    For example my table "teams" looks like this:
    id,name,field2,field3
    1,team a, ....., ......
    2, team b, ......, ......
    etc.

    I hope You can help me further

    Harmen

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select a.name, b.name
    from teams a, teams b
    where a.id < b.id

  6. #6
    Join Date
    Oct 2002
    Posts
    18

    Could you explain a little bit more

    I'm a beginner in programming with
    Acces, So could you tel me where to place the code. Maybe a little example about "a.name" how does it refer and to what??

    Thanks for your time

    Harmen

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Queries > New > SQL View

    paste and run

    if you want to create the scores table, you can pre-fill the scores as follows

    select a.name as teama
    , b.name as teamb
    , 0 as scorea
    , 0 as scoreb
    into gamescores
    from teams a, teams b
    where a.id < b.id


    rudy

  8. #8
    Join Date
    Oct 2002
    Posts
    18

    Works perfect

    Thank, thanks, thanks,

    Works perfect, You can't imagine how you helped me

    Thanks,

  9. #9
    Join Date
    Oct 2002
    Posts
    18

    Can You Help me Further

    Thanks for your help,

    But I don't have much experience with programming in Access, so could you help me a little further.
    Using the "For i = 1 to max" methode i get. And I develept a little other methode so all matches are create in a order so they are easier to use with planning a tournament.
    I'm not sure were I have to put the code. Also I'm not getting the "me" statement.

    So if you would be so kind????

    Harmen

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't do access code, only access sql


    rudy

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    you asked me to show you where to put the code,
    answer :
    ps code has been slightly modified and includes r397 method
    Attached Files Attached Files
    Last edited by m.timoney; 11-28-02 at 12:46.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •