Many to Many; Basic data entry question

[RonL]

Hello, a basic question if I may. I'm building the smallest possible many to many relationship. Two tables and a junction table with the first two columns in the junction table both set as primary keys and each containing the primary key from each of the other two tables ie. - two foreign keys. Each one to many relation has referential integrity and cascade updates both checked. Now I go to enter data in both the data tables directly - no form, just adding rows directly to the tables. But the junction table remains blank. ie. I'm going to have to enter the junction table FKs manually - the number of junction rows will be the product of the number of rows of each of the "one to" tables. Could be a hundred or so.



There must be a way to have the junction table fill out automatically, no? (I thought the "cascade update" would do that.) Do I need to create a data entry form for that to happen? (I don't need one as this is a standalone private app which will have fewer than a dozen rows in each of the "one-side" tables, and the data won't change much. Not that big a deal to do it manually, but just wondering if there's a way to avoid the tedium.) Using Access 2010 on Win7.

Thx, Ron

[orange]

See this tutorial from Train Signal

[RonL]

Thanks. The procedure in that video is what I did. The question is: why didn't the junction table fill in automatically as I added records to the other tables? Should the FK fields data type in the junction table be set to "autonumber," as are the key fields in the other tables? I have them set to long number.

-Ron

[orange]

No. FK in tables should be Number, Long Integer.
You may be expecting too much from form/subform

Consider tables in 1 to Many relationship Order--->OrderDetail
When you add a record to Order, you must save that record before you can add records to matching Order details. and subsequent levels...

[CJ_London]

the number of junction rows will be the product of the number of rows of each of the "one to" tables. Could be a hundred or so.

this sounds like you don't need a junction table at all, just create a Cartesian query (no join)

SELECT *
FROM TableA, TableB

[orange]

RonL,

I have a set up with 3 tables

UniCourse
Tutor and
junction TutorCanTeachJunction

Tutor --->TutorCanTeachJunction<--Course

The tables UniCourse and Tutor are filled already. To populate the junction table, you select the Tutor and the UniCourse, then click the button. This executes an insert query, to add the record to the junction.


Attached is jpg of the simple form I use to populate the junction table

Here is the code associated with the button to save the values from the respective Comboboxes.

'---------------------------------------------------------------------------------------
' Procedure : btnTeaches_Click
' Author    : Jack
' Date      : 12-10-2011
' Purpose   :  To populate TutorCouldTeachJunction Table with
' the id of the tutor and the id of the Course involved.
'Unique index on TutorId & CourseId to prevent duplicates.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Private Sub btnTeaches_Click()
10       On Error GoTo btnTeaches_Click_Error

20    On Error GoTo Err_btnTeaches_Click

      Dim SQL As String
30    SQL = "Insert Into TutorCanTeachJunction(TutorId,CourseId) " _
          & "VALUES (" & Me.Combo0 & "," & Me.Combo3 & ")"
40    Debug.Print SQL
50    CurrentDb.Execute SQL, dbFailOnError

Exit_btnTeaches_Click:
60        Exit Sub

Err_btnTeaches_Click:
70        MsgBox Err.Description
80        Resume Exit_btnTeaches_Click

90       On Error GoTo 0
100      Exit Sub

btnTeaches_Click_Error:

110       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure btnTeaches_Click of VBA Document Form_frmTutorTeachesCourse"
          
End Sub

This is the rowsource of combo0(Tutor)

SELECT TUTOR.ID, TUTOR.Name
FROM TUTOR
ORDER BY TUTOR.[ID];

And this is the rowsource of combo3(UniCourse)

SELECT UNICourse.CourseID, UNICourse.Coursename
FROM UNICourse
ORDER BY UNICourse.[CourseID];

Update:
RonL suggested the many to Many resolved by 2 1 to Many relationships and a junction table. In my view if he wanted a Cartesian product, he would have said so, or would not have described what he did in his post.

I think Ron expected the junction to be populated automagically -- but it won't. You have to determine which Tutor can teach which UniCourse, select the combination and append to the junction.

[ItsMe]

Why would you use a Cartesian Product to represent an entity?

[CJ_London]

Why would you use a Cartesian Product to represent an entity?

why wouldn't you? The OP seems to want to relate every record in one table to every record in the other.

[ItsMe]

why wouldn't you?

I was taught to avoid the Cartesian Product. I was also taught to create fields for Entities and their Attributes. Perhaps this Cartesian Join is a new animal and I am not understanding something.

[orange]

We really need RonL to tell us what he wants. I gave my rationale for my response.
I did not understand his request to

to relate every record in one table to every record in the other.

, but I may have misunderstood.

A Cartesian product (Cartesian join) of 2 tables will provide all combinations of fields in the tables.
It can be a short cut method for populating tables.

I used Cartesian Product in sample code in the repository.

[ItsMe]

I used Cartesian Product in sample code in the repository.

I will have to check it out to make sure I understand it correctly. /hijack

[CJ_London]

Cartesian can be 'dangerous' in that you can create huge recordsets which will not necessarily be efficient - but in this case the OP is talking about circa 10 records in each table - creating a 'junction' table of 100 records overall. If they had say 1000 records in each - returning a 1,000,000 row recordset I would have put an advisory to ensure the set is filtered down in some way to minimise the size of the recordset. I also wouldn't have suggested it if the OP had said that not all records in one table were related to the other - but he said to quote

the number of junction rows will be the product of the number of rows of each of the "one to" tables....
....but just wondering if there's a way to avoid the tedium

Cartesian has its uses - and I suggest this is one of them.

[CJ_London]

I did not understand his request to

to relate every record in one table to every record in the other.

that was my interpretation of

the number of junction rows will be the product of the number of rows of each of the "one to" tables

[ItsMe]

Cartesian has its uses - and I suggest this is one of them.

I know I have created them before and then actually used them in a production DB. I just can't remember the circumstances, exactly. I don't think I have used them in this way, though. Interesting ...

[orange]

I agree that Cartesian join can be dangerous -- they happen magically if the user forgets the criteria in where clause. This sort of thing can result in volumes of data--quickly.

There was an OP who was dealing with Pipes ---schedule, wall thickness and flanges -- for some project and he needed all combinations to set up costs and or processing times...

The query to set up his table was a Cartesian Product

SELECT pipescheduleID, WallThicknessID, FlangeClassID
FROM PipeSchedule, WallThickness, FlangeClass;