multiple value lists and multiple relationships

[buttons]

Hi, I am trying to build a program to calculate the results of a competition. I have 1 table with:

competitor

type of obstacle 1 (Value list)

marks obs 1 (Value list)

co-efficient obs 1 (look up)

result obs 1 (Marks X co-ef.)

type of obstacle 2

marks obs 2

co-efficient obs 2

result obs 2

etc. up to 16

and a second with:

type of obstacle (time, pace, success)

marks obs ("time", "walk/trot/sprint", "yes/no")

co-efficient

from which the value lists and look ups are sourced. I have managed to make it work but for each obstacle I find myself having to create a new value list, a new instance of table1 and three new instances of table2. Is there a more elegant way of doing this please?

Mike

[comment]

I don't really follow this, but it seems like you should use three tables: Competitors, ObstacleTypes and a join table of Obstacles (with up to 16 records per competitor).

[buttons]

hi consultant

thanks for your quick reply, I am not at home at the moment but I will try that over the weekend. Thanks