displaying two team names on the same form

Conor

Registered User.
Local time
Yesterday, 22:10
Joined
May 3, 2007
Messages
18
A lot of sports involve two competitors (individuals or teams) at a time, so this question could apply to many sports.

I want to display a simple form that has competing individuals or teams with both their names. Also other info like date or result, etc, but the names are the problem.

I believe it makes sense to have only one CONTESTANT or TEAM table with ID #s and names. When I create a form to display scheduled games, matches, or whatever term your sport uses, and try to link back for the team names, both boxes on the form show the name of the first team specified.

I would appreciate some help in figuring out how to display both team names. Thanks in advance.

Conor
 
You'll need a separate table called "match" or "game" in this table you should have at least two fields one for the home team and one for the away team.

The best best way to plan your database design is to actually attempt to do it on paper first, think how would you do this on paper? And generally a database table design will occur to you.
 
Thanks for the input, Uncle Gizmo. I do believe I have a basic, workable DB design with a games table as you suggested. What I need is an answer to the specific scenario that I cited - my form returns the same name for both teams since both fields reference the same table. I am hopeful that someone can provide an answer on how to return different names based on different team ID numbers.
 
Uncle Gizmo gave you good advice; you need to have information about match in a junction table...

For example-

tblMatch
Team1ID
Team2ID
Date
Whatever


Team1ID and Team2ID both reference the tblTeam's primary key, so on the form that is bound to the tblMatch, you can use two combobox, which will query the tblTeam for the team's name and thus display correct team name based on their respective Team1ID/Team2ID.
 
Thanks, Banana!! I'm new, but I believe the key info is that I need combo boxes, and I believe that rules out creating the form with the wizard. That may sound simple, but it's one of those things that is not intuitive to rookies - or at least to me. I thought wizards could do anything. Isn't that why they use magic?!

Thanks a ton.

Conor
 
This is my third go around with this issue. I've had input from several users, which I gratefully acknowlege, but I still cannot figure out how to do this. I've consulted Access help, books, etc. If this situation is covered, I don't know how to refer to it to find it in the index. I've tried intermediate queries and every idea that has crossed my mind.

Let me add a couple of details. My DB has 2 and only 2 tables. It is as simple as can be. I have the "Teams" table with ID and TeamName and I have the "Games" table with ID, date, team one ID#, team 2 ID#, team one score, team 2 score. That is it.

I want to create a simple form to allow entry of game results. I want to enter the ID numbers of the two teams and have the form automatically populate the team names into two simple text boxes.

The rub is that both team names come from the same table, so I have two team ID numbers trying to bring back different results from that same table. The relationship display shows a "phantom" table "Teams1" but I cannot figure out how to reference that, or even if I should.

Any help would be appreciated. Thanks in advance - again.

Conor
 
I would be inclined to delete the relationships for now as it appears to be causing you some problems. I don't think the relationship is necessary at this stage and you can of course add the relationship back in later once you have everything working properly.

Think of the "team name" table as a look up table, it is a table that just stores the information, the team name, against a unique ID for that team name.

Now in the game's table you have two fields in which you want the "team name" to appear for example the "home team" and the "away team"

Add a combo box control to each of these fields and set the row source for each combo box to the "team name table". Now you should be able to select the number that relates to each team name that you may wish to appear. This number should also be stored in the correct field in the underlying "game table".
 
Now using the following information you should be able to change the properties of each of your combo boxes so that they show the team name instead of the team number. The last picture isn't quite right as it shows the form in datasheet view whereas your form will probably be in form view. However the rest of the information should be useful.

Set the combo box limit to list property to “Yes”

DataSheetForm_7.png

Still in the combo box properties, set the column count to “2”

DataSheetForm_8a.png

and the column widths as shown.

DataSheetForm_8b.png

Change the name of the combo box as shown or to your own particular naming convention.

DataSheetForm_9.png

Now close the form and reopen it; it should display in “datasheet view” and the numbers should be replaced by the text entries provided by the look up table.

DataSheetForm_10.png
 
I appreciate your hanging with me. I am such a rank novice that I need specifics.

I'll try your approach.

Can you tell me does this type of double referencing to a table have a specific name that shows up in the index of books on Access? Something like "repetitive referencing" or "circular linking" or some arcane terminology known only to the "adept"?

Anyway, thanks once more. I hope this is my last trip to the salad bar on this subject.

Conor
 
>>>does this type of double referencing to a table have a specific name<<<

I don't think so I think I would just call it a "look up table" however there may be a name for it I am unaware of.
 
Thanks for going above and beyond. I dub you Sir Uncle Gizmo for service to the empire (and former parts of the empire).
 
just as long as the "empire doesn't strike back" :)
 

Users who are viewing this thread

Back
Top Bottom