Query on 2 Tables

[Brampton76]

I have a Competition Setup Table which includes the following:
ID(Autonumber), Competition Name, Playing Option (of which there are 5), Cost
(this would be for each of the Playing Options. Therefore, after setting up
a competition the Name would appear in a Table 5 times with a separate option
and the cost. Another Table is for the Competition Entry. This includes the
following: Name, Competition Name (selected in a Form using the same Table as
the Competition Setup), Playing Option (selected in a Form using the same
Table as the Competion Setup) and Amount Paid. What I have been trying to do
is to query using both tables, to show from one Table: Name, Competition
Name, Amount Paid and from the other Table the Cost. A calculated field
would then take one amount from the other to give me the difference. What I
have achieved is a list of all the entries but no Cost, or, a list of the
Costs but no entries. What I seem unable to do is to list each player with
the playing option, the amount paid and then the cost for that particular
player in that particular competition. I have tried to link the 2 Tables in
the Query in all sorts of different ways and seem to have run out of options
and wondered if someone would be able to steer me in the right direction? I
am currently working my way through the Wiley Bible but finding the
Relationships and Links quite difficult to understand!

 

[Klatuu]

For starters, your table design is very wrong. Without going into all the
various database normalization rules you are breaking, let me suggest a
better design.
First, you have competitions, so you need a competition table that describes
the competition. I don't know what all you need to store about a
competition, so I will keep it as Basic as possble:

tblCompetition
CompetitionID - Autonumber, primary key - Uniquely identifies the competition
CompetitionName - Text - The name of the competition

tblPlayingOptions
OptionID - Autnumber, primary key
OptionName - Text
OptionCost - Currency

The one thing I am unclear on about the Playing Options is whether they are
the same for all competitions or they are unique to a specific competition.
If they are different for each competition, you would want to add a
CompetitionID field to this table to link it to tblCompetition.

tblCompetitor
CompetitorID - AutoNumber, primary key
CompetitorName

tblEntry
EntryID - AutoNumber, primary key
CompetitionID - Long Integer, foreign key to tblCompetition
CompetitorID - Long Integer, foreign key to tblCompitor

tblEntryOption
EntryOptionID - AutoNumber, primary key
EntryID - Long Integer, foreign key to tblEntry
OptionID - Long Integer, foreign key to tblOption
FeePaid - Currency

 

[Brampton76]

Many thanks for your help. The Playing Options are the same for each of the
competitions so I will set up a separate table for these. Again, many
thanks, I will redesign along the line of the suggestion.

 

[Klatuu]

Best of luck.