Duplicate Data in Report

[esu4edp]

I have a database for college basketball. I have a conference table, then a table for the teams within that conference, and then the schedule for each team. When I set up the report to show all the games broke down by day, it shows the two teams playing each other twice. How can i stop this?

 

[dhookom]

Don't enter the same game twice. You haven't provided any details regarding your table structures so we have no idea what you did wrong. I would suggest a table structure like:

tblTeams
==================
TeamID
TeamName
TeamCoach
...

tblGames
=====================
GameID
HTeamID
VTeamID
GameTime
GameLocation
....

Duane
MS Access MVP

 

[esu4edp]

I have 4 tables so far:

1. Conference
ConferenceID(Auto Number)
ConferenceName
Conf. Web Site
2. Teams
TeamID(Auto Number)
ConferenceID(Number linked to Conference TBL)
University
Mascot
Color1
Color2
City
State
Univ. Web Site
Athletic Web Site
3. Schedule
ScheduleID(Auto Number)
TeamID(Number Linked to Teams TBL)
Opponet
Home(Yes/No)
Conference Game(Yes/No)
Date
Time
Televised(Yes/No)
Station
4. Roster
PlayerID(Auto Number)
ScheduleID(Number Linked to Schedule TBL)
Jersey #
Last Name
First Name
Position

Let me know if it is setup wrong!
Thanks

 

[jiqjaq]

As Duane tried to say, because you've listed schedules for each team, when home TeamA plays opponent TeamB this will also show as non-home TeamB playing TeamA.

You need another table, Games, which combines the schedules of the 2 teams playing each other.

 

[dhookom]

I would change the Schedule table as I noted earlier. Use two TeamIDs, one for home and the other for visitors. Remove the Home(yes/no) field. Also, change the names of fields Date and Time because these are Access function names. I would link the Roster table to the Team table and if you want to keep stats, create a table that contains the PlayerID and ScheduleID along with the stats you require.

I would also remove all spaces and punctuation from field names.

Duane
MS Access MVP