Aloha,

I'm trying to design a database to track teams scores. These teams can play several times a day. They also play in different towns, so they will have away scores and home scores. I would also like to pull queries based on scores per month away and home. Thanks in advance:)

Aloha from a landlocked surfer!

I would do two tables:

1 - Teams
    =====
    TeamNumber (Number (could be autonumber))
    Name (Text)
    and whatever other info you want

2 - Games
    =====
    GameNumber (Number (could be autonumber))
    TeamNumber (Number - references Teams Table)
    Date (Date Field)
    HomeIndicator (Yes/No Field)

    Create a relationship between Teams.TeamNumber and Games.TeamNumber - a 1 to many relationship

Now you can report by team, and separate by the HomeIndicator and also filter by date. I'll be in and out today, but would be happy to help further as you go through this.

Mike

Mike
EchoVue.com

Mahalo, echovue


I'll give it a try and thanks for the quick response.

Mike another question. How do I identify that team number 1 played team number 10? Do I also need a table for scores? Would they go in the Games table.

Good question.

What about changing the Games table to...
GameNumber
HomeTeamNumber
HomeTeamScore
AwayTeamNumber
AwayTeamScore

I think that should do it, and we can build queries off there that could tell us all kinds of things.


Mike
EchoVue.com

Welcome to the forum as well.

You'll find it a great resource!

Mike

Mike
EchoVue.com

Mike would this work?


Schedule = Table GamesPlayed = Table
DateId = Number ScheduleId Autonumber
ScheduleDate = Date DateId #
                         AwayId #
Home = Table HomeId #
HomeId = # AwayScore #
HomeTeam = Text HomeScore #

Away = Table
AwayId = #
AwayTeam = Text

Relationships

HomeId 1 to many to Games Table HomeId
AwayId 1 to many to Games Table AwayId
DateId 1 to many to Schedule Table DateId

Hi SurferDude,

Sorry, I got tied up for a while. That would work, however it looks like the Away table would be the same as the home table. You could tie the awayID and HomeID into the same table, thus avoid duplicating the data.

Mike

Mike
EchoVue.com

It would be easier to pull queries if you kept the Games as a single table with a format like:
Game ID (autonumber field)
Home Team (pick list of teams from Team table)
Away Team (pick list of teams from Team table)
Home Score
Away Score
Date/Time
Location

In general you want the tables you have to correspond to the major 'nouns' that you're looking at, in this case Teams and Games.

Thanks guys this what I got and it works. I figured out the Home and Away was not good design. Thanks again for the help:)

Tables
Team Games Schedule
ScheduleId ScheduleId DateId
HomeName DateId ScheduleDate
HomeId AwayId Day
AwayId HomeId
AwayName AwayScore
                HomeScore



Now I'm trying to create a query that gives me the total score
for each team for April.

Thanks in Advance