Prev: hi
Next: 6 cases from a textbox control that needs to be validated.
From: Brampton76 on 20 Jun 2008 06:17
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!
--
Glenn
From: Klatuu on 20 Jun 2008 16:15
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

--
Dave Hargis, Microsoft Access MVP


"Brampton76" wrote:

> 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!
> --
> Glenn
From: Brampton76 on 21 Jun 2008 15:34
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.
--
Glenn


"Klatuu" wrote:

> 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
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Brampton76" wrote:
>
> > 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!
> > --
> > Glenn
From: Klatuu on 23 Jun 2008 09:26
Best of luck.
--
Dave Hargis, Microsoft Access MVP


"Brampton76" wrote:

> 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.
> --
> Glenn
>
>
> "Klatuu" wrote:
>
> > 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
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Brampton76" wrote:
> >
> > > 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!
> > > --
> > > Glenn
 | 
Pages: 1
Prev: hi
Next: 6 cases from a textbox control that needs to be validated.