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
0
Storm_21_924
1/19/2010 7:43:23 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

1 Replies
1236 Views

Similar Articles

[PageSpeed] 22

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 
0
Utf
1/20/2010 1:25:01 AM
Reply:

Similar Artilces:

Overlapping of values in different tables.
I am wondering if there is a way to create a pivot table - or some other way - that will show the relationships of rows between tables. I have attached data from potentially 4 different tables and the business is wanting to see the number of times that a paticular ID shows up in different table join combinations. For example, we want to know the count of unique IDs will show in the NPS, AAC and ECM tables. Is it possible to create a pivot table that will dynamically adjust based on a user choosing the column combinations that they want to see without having to create a separate pivot...

password protected file with pivot table errors
Hey friends, We have a big problem, around 20% of the times we save workbooks with pivot tables inside and protect them with password for opening the file - the file get ruined! In those times, when we open the file (the password protected file with the tables inside), excel ask me for the pass (ok, I give it) and then say "file opening did not succeeded", and then let me type again, in the second time it opens but give a message saying "there where errors in the file, and changes where made:" (and then a list of my pivot tables saying that fixes made to them). Fr...

Client-Side Lookup Question
Hello, We have a routine that runs when certain attributes on the order are filled in. So when the attributes are filled in and the users press the save button a message pops up confirming that they want to run this routine. One of the citeria for running the routine is that the opportunity that the order is associated to must be closed. Right now there is no checking for this. Is there a way through jscript that when they try to run the routine it will first go out and check if the associated opportunity is closed. If it is still open it pops up a message saying they must close ...

Insert a line or shape in a table
Creating a chart from Excel, I copied and pasted it to my Word document. I now need to put in some lines to create a graph, but when I insert the line, the table expands and places the line within one segment. I want to extend the line across the top of the whole table. I don't see where I can lock the table so it won't change. Drawn lines have nothing to keep them in place. Use a real bit of data, and the line will stay locked in place: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Cu...

Help on Lookup
I have a spreadsheet that contains two worksheets (a & b). In worksheet b (which is hidden) all of the data is kept for a test that is run each day. (user form is used for data entry). from this data, a total is calculated (nothing serious, just =sum function). The data is entered in columns and the total is calculated at the bottom of every other column (i.e every day data is entered into two columns, C&D, with the total being calculated at the foot of column D and so on). On Worksheet (a) I want to create a summary of the days data - including the total result. Can I make a cell ...

How to create a table to qualify a qty discount in exel
Hi i'm creating a workbook to orginize stock, i'm not sure of how to create a table to give varying degress of discount according to the quatity bought ie: 5 items or less = no discount, 6 to 11 items =2% discount and so on, i have used an if statement so far but i'm not sure how it is meant to work in relation to the products and customers sheets, how does it all work together. Any help & guidence would be much appreciated. You can create yourself a lookup table, and use the vlookup formula. See: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_exce...

edit variable length items in "Access" database tables
In my database table I am holding a variable length item in a database record; the item may be from two to six alphabetic characters long. Trailiing spaces pad the item where required. To print out the item in a report, the item itself is sandwiched between two fixed-length items separated by an oblique stroke, (forward slash): - ***/variable length/***. How can I suppress the trailing spaces in my variable length item when printing results ? Yours Sincerely, CHARLES WALKER Take a look at the RTrim function or even the Trim function. RTrim(SomeField) will trim trailing spaces off a...

relationships of 3 tables
Greeings, I am creating a db to track who serves what function in what Sunday at church. So I think I need 3 tables. 1. people who serve 2. functions they serve 3. dates they serve Many people can serve many dates and many people can serve many functions on many dates. So do I have the three tables and then a join table for people/dates and a join table for people/function and a join table for dates and functions? And how do I structure them. I am thinking: tblPeople peopleID pk name tblFunction functionID pk functionName tblDate ...

delete from many tables at the same time
Hi I am new using Access, and this may be an elemental question, but anyway, I have three tables: Customers. Orders. Payments. (Customers' payments) Customers has as primary key the name of the customer and the other two tables have as a primary key an automatic ID integer. How can I configure Access in the way that If I erase a Customer from the table Customers the other two tables will be automatically updated erasing also their Orders and Payments? Thanks, Enrique. -- Eng. Enrique Lopez. Go to the Relationships window and, if not already done, creat...

Segment Description Table
I did a select * into GL40200_103208 from GL40200 table as backup I then deleted the information from GL40200 opps need the account segments back I droped GL40200 and select * into GL40200 from GL40200_103108 next step UPDATE GL40200 SET DSCRIPTN = ' ' I reconicle GL within Dynamics but still no segment information. Tool>Setup>financial>segment has no segment numbers i By the way version 9.0 The GL40200 table requires either manual or SQL entry. You can either go to: Tools--> Setup--> Financial --> Segment, or do a table import into the Segment Description Ma...

Sort name from specified interval table
Hiiii I am facing a silly problem i think u guys are rescue me from that... My prob like I have two cloum one colum contain Name of person and another coloum contain their salary.I prepared a interval table contain 4 row according to salary range....I have find the name whos salary has on the specified range on ist row of interval table.... Can any one giv the idea about that..Perhaps I tried it VLook up Function...But i faced proble to sort the name from ist colum -- mun04 ------------------------------------------------------------------------ mun04's Profile: http://www.excelforum....

Chart
I currently am using Excel 97. I've created charts using links to source data that is in dollar and percent formats. But when they pull into the chart data table they do not show the proper format. For example: In the source data worksheet it shows 97%, but when it pulls into the data table for the chart it shows .97437373. If I open the link then it shows properly, but as soon as I close the link it goes back to the bad format. How can I fix this? -- lajohn63 It sounds like a formatting issue. You may need to add a step to round (see round function in help) "lajohn63" wro...

Lookup value selection
Is there any way to select lookup value by pressing "Enter" key on keyboard and avoid mouse click? -- romeo!! Please clarify. I could use the up and down arrow to select the values and then press "Enter" key to select the lookup value - works for me in v4.0. Frank Lee, Microsoft Dynamics CRM MVP http://www.workopia.com/Links.htm http://microsoft-crm.spaces.live.com "romeo" wrote: > Is there any way to select lookup value by pressing "Enter" key on keyboard > and avoid mouse click? > > -- > romeo!! ...

Grouping daily transactions by month (using pivot tables), across years
Hello - Could someone please let me know the best way to use a pivot table to group daily transactions into monthly totals. I noticed there is a "Group and Show Detail" option----but it seems to be unable to portray 2 separate Januarys when they fall into two separate years. For instance, if my data is 1/1/07 1000 1/4/07 8000 1/19/08 10000 I just get a total for January, rather than two separate totals for Jan 07 and Jan 08. Is there a way to allow the pivot tables to show Monthly totals, but ensure that I can distinguish between years? Is there possibly a way to in...

Pivot table grouping problem #2
I have a pivot table that will not group numbers in a column. ie: Year Group 2004 2005 10 2 10 1 11 5 11 3 12 8 12 10 Why won't these rows combine and look like: Year Group 2004 2005 10 1 2 11 3 5 12 10 8 All other columns group fine when I try them, but the group column does not. Any ideas? Sheryl ...

maximum number of indexes per SQL table that Access (jet) can deal with?
why is there a maximum number of indexes per SQL table that Access (jet) can deal with? is it really the count of indexes + statistics? I've seen plenty of documentation that states that typically DSS systems have twice as much index space as data space.. So I don't think that it's a case of 'over-indexing' I'm just tired of a crippled Jet front end and other people making me 'temporarily drop my indexes' so that they can link to my tables using Jet. has this bug gone away with Access 2007? Is it going away with Access 2010? ...

Form design to add data to 3 tables in "Order Entry" style db
Sorry if this is a duplicate. I thought I posted it yesterday but couldn't find it today... I need help figuring out the best way to design a form for data entry in the "Order Entry" style, adding data to three related tables. For clarity I have eliminated several fields. Here are the three tables, plus the fourth that is a reference table: tblCustomers: CustID (pk), CustName tblOrders: OrderID (pk), OrderDate, CustID (fk) tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk) tblProducts: ProdID (pk), ProdName Each Customer is associated with one or mo...

Help needed arranging data for a pivot table
I have a very large table in excel that I need to create a pivot tabl for, but can't seem to arrange the data correctly. Here's what th table looks like: There are about 20 "type" columns across the top and down the side are dates. For each day each of the columns ha about 20 prices. So a big block of mostly price data is added eac day. Do I have to have all of the prices in one column if I want t view different "types" side by side in a pivot table? If so, I woul have many many times the 65,000 rows that excel allows. This als seems ridiculously redundant. ...

Copy data into a table (but not the formula)
Hi First of all I apologise if this has been asked before. What I want to know is it possible, and if so how does one do it, to d the following ? Copy data from a cell into another cell in a table but without having formula in the destination cell , e.g Say $b1 contains the name Fred and I want to place Fred into the $c cell but I don't want there to be a formula in $c5 asking for it tha will update the contents of $c5, if I change the value held in $b5, fo instance, I put Fred in $b1 and Fred appears in $c5, but then I pu another name, say Bert into $b1 and it goes to $c6, but the co...

Relationship between entity and case
I have created a custom entity and would like to be able to do the following: Open a case. Select the account via the lookup Select the entity from the left details area within the case and have it display any detail records for that account. The goal is to have support working strictly from the cases area and not have to click back and forth to an entity. It would also be great if when activity or history were selected it would display that associated info as well. Thanks for any help/ideas you all have! On Nov 12, 5:52=A0am, AmyF <djaiml...@gmail.com> wrote: > I have created a c...

When I insert a table the text after in moves to the next page
I would be grateful for any help! I am using styles formatting and am doing a report. When I insert a table the text moves to the next page, what ever I do. It is driving me mad! I can't find a style option for tables and can't seem to save the table as a new style Adding new contents would move existing contents down the page, or even to the next page if there isn't room on the current page... Display nonprinting marks, for example by pressing Ctrl+Shift+8 (acts as a toggle). Are there any blank paragraphs (�) on the page? If so, remove them. Your text paragra...

Need help with tables
I have a table created where one of my fields in called "Completed" with a Yes/No command option. I would like to know if there is a way that when I check that box, the whole record can be automatically transferred to another table where I can store records that are "completed". Essentially I would have two tables, one with records where the "Completed" check box is not checked and another table where the "completed" box is checked. I'm fairly new to Access so I cannot figure this one out. Any help or suggestions would be very much app...

Pivot Tables
What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, and "Count" in Pivot tables is like "CountA" in formulas. Is this true? I think you have it! It's like what you find in the statusbar, too. RS wrote: > > What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I > am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, ...

Inser the current date into a field in a Table
Hi, I want to insert the current date into "LastUpdateCost" field fromthe "CYCLETICKETNUMBER" table. I have the following code but is notcomplete. Can you help me????? strSql = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) Date ;" db.Execute strSql, dbFailOnErrorThe code is incomplete because I don't know the what code I need to dowhat I want. Thanks for your help. Hope you have a nice day.JC jeanhurtado@gmail.com wrote:> Hi, I want to insert the current date into "LastUpdateCost" field from> the "CYCLETICKETNUMBER" table. I h...

Changing Pivot Table Value Field Settings
Hi All, I'm using the Visual Reports feature in MS Project that produces reports based on Excel pivot tables connected to cubes produced by Project. I know that when using a cube as the data source I (unfortunately) cannot use a calculated field, so I'm trying to change the Value Field Setting instead, but the result is always the same, it just produces #N/A. In Project, I've selected to use the Assignment Usage cube. In Excel I have made the following selections: Row Label = Resources Column Labels = Values Values = Baseline Work, Actual Work What I'm trying to do is disp...