Double relationship lookups

[Jared]

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

 

[John W. Vinson]

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!

 

[tina]

comments inline.

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