Anyone have a "Using Juction tables for dumbies"?

[morfusaf]

I want to try and use a junction table, but .... jeeze

once I get the relationship, i don't know how to get the forms to populate the junction table properly or anything... I feel so stupid.


Anyone have a everything you need to know when using junction tables?

 

[Beetle]

As an example, let's say you have students and classes, which is the classic many-to-many relationship (a student can take many classes and a class has many students). To model this you have three tables - Students, Classes and StudentClasses, StudentClasses being the junction table of course. In a very basic example StudentClasses would only contain two fields - StudentID and ClassID - which are both Foreign Keys and together make a compound Primary key for the table.

Now, in a data entry scenario you would use a main form/sub form setup. The main form would be based on either Students or Classes and the sub form would be based on the junction table, in this case StudentClasses. In the sub form you have two combo boxes, one bound to StudentID and the other bound to ClassID. The Row Source for each combo box returns both the ID (StudentID or ClassID) value and the name (either Student or Class name). Only the ID value is stored in the table but the combo box, with it's Column Width values set appropriately - i.e. 0", 1" - displays the related name from the parent table.

Edit: Actually, in this scenario you would only have one combo box in the sub form (either Student or Class, the opposite of whatever the parent form displays). The other ID value is there for the purpose of the Master/Child link between the main form and sub form, but is not actually displayed in the sub form. Sorry, it's the end of the day. My brain shut down about a half hour ago.

 

[Megacitizen]

I'm having similar problems, where I'm trying to set up a relationship of one base sending many teams and one region receiving many teams. However, when I try to relate primary and foreign Keys in my tables, I keep getting legends like "Relationship must be on same number of fields with same data types" As far as I know, they should already be that.

I have a very sore head now

 

[Beetle]

@ #3

Please post some info about your table structure, including the relevant fields and the data types.

 

[Megacitizen]

@ #3

Please post some info about your table structure, including the relevant fields and the data types.

There's more info on the "Creating a grid on a form" thread in this Forms page, which I started a few weeks ago. But to summarize;

I am trying to create a Data Entry type DB where the user will key in information regarding various teams from various bases around the world doing certain tasks in various regions of the same city (currently London, but may also be adding past records from Mexico City as well as records from future cities).

At the moment, I have 4 tables each containing the following fields:-
(All fields are text unless otherwise stated)

t_Teams
TeamID - Autonumber
TeamName
BaseID
RegionID

t_Bases
BaseID
BaseName
Nation

t_Regions
RegionID
RegionName
TeamID

t_Placement
RegionID
TeamID

The first named field in each table is the PK, with the exception of the Placements table which has no PK at present.

I'm trying to get a relationship series of 1 Base sends many teams and 1 Region receives many teams, but 1 team can only be in 1 region at a given time.

When I try testing my tables on a form, as well as the legend mentioned in the earlier post, I also get "Cannot enter, join key not in RecordSet" when I try inputting info into certain fields.

When I eventually get everything working, I will then have to add in fields and/or tables regarding the various stages of a team's application to join and a record of their activities within the city in question - hence my original "..Grid on a Form.." thread query.

Edit:- forgot to say, am working on Access 2010 (insert hitting own head with club type smilie here)

 

[Megacitizen]

THanks for your help. Have ordered a copy of "The Practical SQL Handbook - Using SQL Variants" so that should hopefully be of assistance. It's being delivered from somewhere in Western USofA by snail mail, but I think the snail's travelling via Europe & Asia rather than crossing the Dateline, which would be the shorter route for me .