I'd Love To Do This!
If you provide me with the version of MS Access and the format of your tournament (ie 4 groups of founr top 2 forward to knock out stage???) I could knock something together for you as I have done something similar in Access to simulate the English Football League Season.
IN MS ACCESS 2000...........
For example say you had a table called "TBL_CLUBS" which holds your team data with the standard CLUB (text), (the following all numeric) PLAYED, WON, DRAWN, LOST, POINTS, GOALS FOR, GOALS AGAINST, GOAL DIFFERENCE (if applicable)
and a table with your matches in called "TBL_FIXTURES" with fields HOME TEAM (text), HOME SCORE (numeric), AWAY TEAM (text), AWAY SCORE (numeric).
OK heres your TBL_CLUBS table..........
CLUB PL W D L F A PT DIFF
TEAMA 0 0 0 0 0 0 0 0
TEAMB 0 0 0 0 0 0 0 0
TEAMC 0 0 0 0 0 0 0 0
TEAMD 0 0 0 0 0 0 0 0
and your TBL_FIXTURES table.......
HOME H AWAY A
TEAMA <null> TEAMB <null>
TEAMC<null> TEAMD <null>
TEAMA <null> TEAMC <null>
TEAMB <null> TEAMD <null>
TEAMA <null> TEAMD <null>
TEAMB <null> TEAMC <null>
You will need to write a query which resets your clubs table WIN/LOSS etc records back to zero name it QRY_RESET CLUBS
Every time you enter a score in your TBL_FIXTURES call this procedure...
DIM MYDB AS DATABASE
DIM CLUBS AS RECORDSET
DIM FIXTURES AS RECORDSET
SET MYDB = CURRENTDB()
SET CLUBS = MYDB.OPENRECORDSET("TBL_CLUBS")
SET FIXTURES = MYDB.OPENRECORDSET("TBL_FIXTURES")
DOCMD.SETWARNINGS FALSE
DOCMD.OPENQUERY("Qry_Reset Clubs")
FIXTURES.MOVEFIRST
DO UNTIL FIXTURES.EOF
IF ISNULL(FIXTURES![H]) or ISNULL([FIXTURES![A]) THEN
GOTO ONWARD
END IF
CLUBS.MOVEFIRST
DO UNTIL CLUBS.EOF
'*** HOME TEAM ***
IF CLUBS![CLUB] = FIXTURES![HOME] AND FIXTURES![H] > FIXTURES![A] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![W] = CLUBS![W] + 1
CLUBS![PT] = CLUBS![PT] + 3
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS![DIFF] = CLUBS![F] - CLUBS![A]
CLUBS.UPDATE
CLUBS.MOVENEXT
END IF
IF CLUBS![CLUB] = FIXTURES![HOME] AND FIXTURES![H] = FIXTURES![A] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![D] = CLUBS![D] + 1
CLUBS![PT] = CLUBS![PT] + 1
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS.UPDATE
CLUBS.MOVENEXT
END IF
IF CLUBS![CLUB] = FIXTURES![HOME] AND FIXTURES![H] < FIXTURES![A] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![L] = CLUBS![L] + 1
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS![DIFF] = CLUBS![F] - CLUBS![A]
CLUBS.UPDATE
END IF
'***AWAY TEAM ***
IF CLUBS![CLUB] = FIXTURES![AWAY] AND FIXTURES![A] > FIXTURES![H] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![W] = CLUBS![W] + 1
CLUBS![PT] = CLUBS![PT] + 3
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS![DIFF] = CLUBS![F] - CLUBS![A]
CLUBS.UPDATE
CLUBS.MOVENEXT
END IF
IF CLUBS![CLUB] = FIXTURES![AWAY] AND FIXTURES![A] = FIXTURES![H] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![D] = CLUBS![D] + 1
CLUBS![PT] = CLUBS![PT] + 1
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS.UPDATE
CLUBS.MOVENEXT
END IF
IF CLUBS![CLUB] = FIXTURES![AWAY] AND FIXTURES![H] < FIXTURES![A] THEN
CLUBS.EDIT
CLUBS![PL] = CLUBS![PL] + 1
CLUBS![L] = CLUBS![L] + 1
CLUBS![F] = CLUBS![F] + FIXTURES![H]
CLUBS![A] = CLUBS![A] + FIXTURES![A]
CLUBS![DIFF] = CLUBS![F] - CLUBS![A]
CLUBS.UPDATE
END IF
CLUBS.MOVENEXT
LOOP
ONWARD:
FIXTURES.MOVENEXT
LOOP
I hope you get the general idea. You can of course add a Group field to both the TBL_CLUBS and TBL_FIXTURES tables to differentiate between the different groups or divisions whichever system you employ.
Let me know your exact format needs..
Regards,
NEIL
