Table relationships and lookups

  • Thread starter Thread starter Storm_21_924
  • Start date Start date
S

Storm_21_924

Hi guys, I may be a little over my head, I've had some experience in
creating simple access db's. however this one will be extremely
complicated as far as I can tell. Some backround info - i've got an
excel spreadsheet currently that i would like to convert to Access.
The spreadsheet does multiple lookups and calucations.

This is for a Soccer club that i run to maintain roster information,
dollars, scheduling and stats. I'm currently working on the
scheduling pience. Here's what I have so far.

tables.
Club - Lists the teams in the club, home field name and address, city,
state, zip and notes
Opponent - Lists the teams in the league, home field name and address,
city, state, zip and notes
League_division - Basic, lists the league divisions 1,2,3,4 ( I didn't
want to include this in the two previous tables because teams can move
up and down depending on the season records.
League_Season - Hopefully to use for keeping historical data, players
and game info.
Schedule - This is going to be for entering each shceduled game.

Schedule table includes the following fields
Game_ID - AutoNumber
Game_Date - Date of game
Game_Time - Time of game
Game_Location - Value list, Home, Away, Other
Game_ClubTeam - Combo box of Club team from Club Table
Game_Opponent - Combo box of Opponent team from Opponent Table
Game_FieldName -
Game_FieldAddress -
Game_FieldCity -
Game_FieldZip -
Game_FieldNotes -

What I would like see is this.. Once the Game_Location, Game_ClubTeam
and Game_OpponentTeam has been chosen, I would like the
Game_fieldName, Game_FieldAddress, Game_fieldcity, Game_fieldState,
Game_fieldZip and Game_fieldNotes all be filled in automatically based
on the Location, club and opponent fields.

I don't know if I am over complicating this, but I really don't know
where to start.

Both the ClubTeam and OpponentTeam Tables have matching fields.

Anyhelp would be greatly appreciated.

Thanks!
Shaun
 
Shaun

See Notes Below

Hi guys, I may be a little over my head, I've had some experience in
creating simple access db's. however this one will be extremely
complicated as far as I can tell. Some backround info - i've got an
excel spreadsheet currently that i would like to convert to Access.
The spreadsheet does multiple lookups and calucations.

This is for a Soccer club that i run to maintain roster information,
dollars, scheduling and stats. I'm currently working on the
scheduling pience. Here's what I have so far.

tables.
Club - Lists the teams in the club, home field name and address, city,
state, zip and notes
Opponent - Lists the teams in the league, home field name and address,
city, state, zip and notes
Click to expand...

Right off the bat question your normalization the club and opponent tables
seem too similar to be seperate.. you should probably just have a teams table
with a [Team_Type] Field or something similar to sort or filter by if Club
and Opponent teams need to be differentiated between.
League_division - Basic, lists the league divisions 1,2,3,4 ( I didn't
want to include this in the two previous tables because teams can move
up and down depending on the season records.
League_Season - Hopefully to use for keeping historical data, players
and game info.
Schedule - This is going to be for entering each shceduled game.

Schedule table includes the following fields
Game_ID - AutoNumber
Game_Date - Date of game
Game_Time - Time of game
Game_Location - Value list, Home, Away, Other
Game_ClubTeam - Combo box of Club team from Club Table
Game_Opponent - Combo box of Opponent team from Opponent Table
Game_FieldName -
Game_FieldAddress -
Game_FieldCity -
Game_FieldZip -
Game_FieldNotes -
Click to expand...

I didnt see any mention of table for the Field Info for [Game_FieldName] .ect

If field Specific info is different from team info you will want a
field_info table
What I would like see is this.. Once the Game_Location, Game_ClubTeam
and Game_OpponentTeam has been chosen, I would like the
Game_fieldName, Game_FieldAddress, Game_fieldcity, Game_fieldState,
Game_fieldZip and Game_fieldNotes all be filled in automatically based
on the Location, club and opponent fields.

I don't know if I am over complicating this, but I really don't know
where to start.
Click to expand...

Assuming you are doing all this on a form And not directly in tables
you can have four combos a Game_Location, Game_Field_Name, Game_ClubTeam,
and Game_OpponentTeam. when a "Field_Name" is selected the address would be
easy to get with some code in the Field_name combos after-update event.. or
In a report with the appropriate Controls
Both the ClubTeam and OpponentTeam Tables have matching fields.
Click to expand...

Yes Bad Normalization have one teams table
Anyhelp would be greatly appreciated.

Thanks!
Shaun
.
Click to expand...

Sorry there isnt more detail
One step at a time
post more and we'll respond more..

Good luck, and Have fun
Barry
 
Back
Top