Database Design

[paul_hutchings]

Could someone please help shed a little light on a database query I
have.

Here goes:

I am in the process of building a access database for a local football
team and have the following tables.

Players
Fixtures
Teams
Clubs
Player Positions
Player Status (ie.retired, injured etc)
Sponsors
Sponsor Agreements
etc....

My problem is, that in creating a fixture I am having problems
identifying how to populate the fixture table as the relationship is
currently 'A team can have many fixtures and a fixture must have two
teams'.

In relational terms I can't see how to pull two teams from the team
database to make a fixture???

Apologies if the answer is staring me in the face but I have run out of
coffee and have resorted to finding a soft spot on the wall to bang my
head against....

Any help would be most appreciated.

Regards,
Paul

[PC Datasheet]

I don't know what a Fixture is so I'm assuming it's a contest between two
teams. If correct, your Fixtures table would be like a schedule looking
like:
TblFixture
FixtureID
FixtureDate
etc

You then need another table associating the two teams with a Fixture. That
table would liik like:
TblContest
ContestID
HomeTeamID
VisitingTeamID

HomeTeamID and VisitingTeamID would be foreign keys related to TeamID in
your Team table.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


<pa************@blueyonder.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Could someone please help shed a little light on a database query I
have.

Here goes:

I am in the process of building a access database for a local football
team and have the following tables.

Players
Fixtures
Teams
Clubs
Player Positions
Player Status (ie.retired, injured etc)
Sponsors
Sponsor Agreements
etc....

My problem is, that in creating a fixture I am having problems
identifying how to populate the fixture table as the relationship is
currently 'A team can have many fixtures and a fixture must have two
teams'.

In relational terms I can't see how to pull two teams from the team
database to make a fixture???

Apologies if the answer is staring me in the face but I have run out of
coffee and have resorted to finding a soft spot on the wall to bang my
head against....

Any help would be most appreciated.

Regards,
Paul

[StopTheAdvertising]

A quick note to anyone reading this message thread. This newsgroup is an
excellent resource for getting help with your Access projects. If you feel
the need, however, to seek professional help, I strongly advise that you not
consider hiring this PC Datasheet character. Steve is a bright kid but is
totally unprofessional. The CDMA charter prohibits advertising, Steve is the
only consultant that refuses to adhere to that charter. There are literally
hundreds of competent Access consultants available. Many post regularly in
this newsgroup, others can easily be located by googling or visiting
guru.com.

[Ian Davies]

I would have in my fixtures table something like

FIXTURE TABLE
--------------------
FixtureNo
HomeTeamID
HomeTeamScore
AwayTeamID
AwayTeamScore
Date
Anything else..

Your index in the TEAM table would then link to HomeTeamID and AwayTeamID

Any calculations you want to make in queries such as points, won, lost, drew
can be simply done from the fixtures table

Ian


<pa************@blueyonder.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Could someone please help shed a little light on a database query I
have.

Here goes:

I am in the process of building a access database for a local football
team and have the following tables.

Players
Fixtures
Teams
Clubs
Player Positions
Player Status (ie.retired, injured etc)
Sponsors
Sponsor Agreements
etc....

My problem is, that in creating a fixture I am having problems
identifying how to populate the fixture table as the relationship is
currently 'A team can have many fixtures and a fixture must have two
teams'.

In relational terms I can't see how to pull two teams from the team
database to make a fixture???

Apologies if the answer is staring me in the face but I have run out of
coffee and have resorted to finding a soft spot on the wall to bang my
head against....

Any help would be most appreciated.

Regards,
Paul

[PC Datasheet]

This is Randy Harris a liar who is insincere and a person that can not be
trusted.

Randy,

Why don't you follow the advise of David Fenton ---

"Stop. I'm on the verge of killfiling the bunch of you who are
polluting the newsgroup with theis bitchfest."

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!
"StopTheAdvertising" <no****@pcdatasheet.com> wrote in message
news:qP*****************@newssvr24.news.prodigy.ne t...

A quick note to anyone reading this message thread. This newsgroup is an
excellent resource for getting help with your Access projects. If you feel
the need, however, to seek professional help, I strongly advise that you
not
consider hiring this PC Datasheet character. Steve is a bright kid but is
totally unprofessional. The CDMA charter prohibits advertising, Steve is
the
only consultant that refuses to adhere to that charter. There are
literally
hundreds of competent Access consultants available. Many post regularly
in
this newsgroup, others can easily be located by googling or visiting
guru.com.

[paulhutchings7204]

Hi Ian,

Many thanks, by index do you mean relationship as there will only be
TeamID in the TEAM table. Apologies for the blank expression.

Regards,
Paul.

[paulhutchings7204]

Ian, ignore my last I've suddenly hit the right spot on the wall and
worked it out.....

Thanks for you help.

Regards,
Paul.

[paulhutchings7204]

Hi and thanks so much for your help.

I have managed to work out the problem after banging my head enough
times.
Regards,
Paul.