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
|
|||
|
|||
Double relationship lookups
Hey guys.
I really appreciate all the help you guys have given me. I have one advanced item that escapes me on how would be the best way to handle it. Here it is. I have the following tables Table Name: Team Table Fields: Team Name - Text - Primary Key Division - Lookup to table Divisions/divisionName Table Name: Divisions Fields: Id - Primary key DivisionRank - Number DivisionName - Text Table Name: 2009 Schedule/Scores Fields: GameNumber - Primary Key Date - Date Home Team - Lookup to Table Team Table/Team Name Away Team - Lookup to Table Team Table/Team Name .... and a few other fields that dont have much bearing. What I am trying to do is as follows: When the league rep selects a Home Team, I want it to display the division Rank beside it that was assigned to it in the Team Table. ie. Date Home Team Division Away Team Division 03/20/2009 Nutz N Boltz 2 Mixed Nuts 3 I need this either in a report or query but not sure how to set it up properly. When I try the drag and drop method in the Report it asks which relation to assign that field too. So I select Home Team, then when I try to drag a second one in for the Away team it does not ask me for the relationship. Thanks |
#2
|
|||
|
|||
Double relationship lookups
On Mon, 20 Apr 2009 18:02:02 -0700, Jared
wrote: When the league rep selects a Home Team, I want it to display the division Rank beside it that was assigned to it in the Team Table. ie. Date Home Team Division Away Team Division 03/20/2009 Nutz N Boltz 2 Mixed Nuts 3 The simplest way is to include the Division in each combo box's Row Source query: SELECT [Team Table].[Team Name], [Divisions].[Division Rank] FROM [Team Table] INNER JOIN [Divisions] ON Divisions.ID = [Team Table].[Division] ORDER BY [Team Table].[Team Name]; On the form put a textbox with a control source =comboboxname.Column(1) to display the second column of the combo (it's zero based). Do this for each of the home and away combos. A couple of suggestions: 1. Don't use blanks in fieldnames; if you do, you MUST always use [square brackets], and it makes it harder to work with other database software or to upsize. 2. Don't use Lookup Fields. See a critique at http://www.mvps.org/access/lookupfields.htm The ONLY benefit they have is making it easier to work in table datasheets - which you shouldn't be doing in any case! -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Double relationship lookups
comments inline.
"Jared" wrote in message ... Hey guys. I really appreciate all the help you guys have given me. I have one advanced item that escapes me on how would be the best way to handle it. Here it is. I have the following tables Table Name: Team Table Fields: Team Name - Text - Primary Key Division - Lookup to table Divisions/divisionName i seriously doubt that the Lookup field is storing the division name - that's just what it's *showing* you. a few points: 1) don't use Lookup fields in tables; for more information, see http://www.mvps.org/access/lookupfields.htm. 2) don't enter data directly in tables - that's what forms are for. 3) make sure you've linked table Divisions and [Team Table] in the Relationships window, linking from the Id field in Divisions to the Division field in [Team Table], and enforce Referential Integrity. Table Name: Divisions Fields: Id - Primary key DivisionRank - Number DivisionName - Text Table Name: 2009 Schedule/Scores Fields: GameNumber - Primary Key Date - Date Home Team - Lookup to Table Team Table/Team Name Away Team - Lookup to Table Team Table/Team Name ... and a few other fields that dont have much bearing. What I am trying to do is as follows: When the league rep selects a Home Team, I want it to display the division Rank beside it that was assigned to it in the Team Table. ie. Date Home Team Division Away Team Division 03/20/2009 Nutz N Boltz 2 Mixed Nuts 3 I need this either in a report or query but not sure how to set it up properly. you can do it in a report by basing the report on a multi-table query, using tables Divisions and [Team Table] and [Schedule/Scores], linking the Division table to [Team Table] and then linking [Team Table] to [Schedule/Scores]. then just select the fields from each table, that you want to see in the report. i strongly urge you to do some reading on relational design principles, as well as on basic database design. you're making a number of classic "newbie" mistakes that are going to come back and bite you in the butt if you don't fix them: using spaces and special characters in table and/or field names; using reserved words (Date) as fieldnames; using Lookup fields in tables (addressed above); storing data in table names (2009 Schedule/Scores) - and this last one is a big one, breaking relational design rules, and setting you on the road to development/use misery with your db. for more information, see the first nine Tips at http://home.att.net/~california.db/tips.html, and make good use of the links you find in the text. hth |
Thread Tools | |
Display Modes | |
|
|