Help with a boxing database

Question

I'm trying to set up a database in access to store boxing results. 

I'm really stuck on one thing. If I have a fight table with boxer A and Boxer B, how do I write it so that if Boxer A wins by KO it also means boxer B loses by KO? Without putting the result in twice? 

THanks

Kevin

Answer 1

I wouldn't include both boxers in the Fights table.  I'd suggest modelling it with, in broad outline, the following tables:

Boxers

….BoxerID  (PK)

….FirstName

….LastName

Results

….WinnerResult  (PK)

….LoserResult

The above table would contain a row with values 'Wins by KO' and 'Loses by KO', along with other rows with winning and corresponding losing results.

Fights

….FightID  (PK)

….FightDate

Contestants

….FightID  (FK)

….BoxerID  (FK)

….Result

The primary key of the last table is a composite of the two foreign keys.

The interface for this would be a fights form, in single form view, within which is embedded a contestants subform, in continuous forms view, linked to the parent form on FightID.  Having inserted two rows into the subform, when the result is known a result for the winner would be selected from a combo box bound to the Result column, with a RowSource property of:

SELECT WinnerResult FROM Results ORDER BY WinnerResult;

In the AfterUpdate event procedure of the combo box you'd put the following code:

    Dim strSQL As string

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    strSQL = "UPDATE Contestants " & _

                    "SET Result = " & _

                        "(SELECT LoserResult " & _

                          "FROM Results " & _

                          "WHERE WinnerResult = """ & ctrl & """) " & _

                      "WHERE FightID = " & Me.FightID & " " & _

                      "AND BoxerID <> " & Me.BoxerID

    CurrentDb.Execute strSQL, dbFailOnError

The subform should refresh automatically to show the loser result, but if not, add the following line:

    Me.Refresh

Answer 2

It would help to see your table structure. But I'm going to guess here you have a table like this:

tblFight

FightID (PK Autonumber)

FightDate

BoxerA

BoxerB

I would change BoxerA to FightWinner and BoxerB to FightLoser. I would then add a field for FightDecision. So now you have.

tblFight

FightID (PK Autonumber)

FightDate

FightWinner

FightLoser

FightDecision

So now all you need is the select the fight winner and loser from a table of fighters and then select the decision from a table of decisions.

Answer 3

Can you share your table structure and a few rows of data?