If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table relationships and lookups
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 |
#2
|
|||
|
|||
Table relationships and lookups
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 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 - 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. 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. Yes Bad Normalization have one teams table Anyhelp would be greatly appreciated. Thanks! Shaun . Sorry there isnt more detail One step at a time post more and we'll respond more.. Good luck, and Have fun Barry |
Thread Tools | |
Display Modes | |
|
|