A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table relationships and lookups



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2010, 08:43 PM posted to microsoft.public.access.tablesdbdesign
Storm_21_924
external usenet poster
 
Posts: 1
Default 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  
Old January 20th, 2010, 02:25 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.