Filling Junction Tables for many-to-many relationships

[Guest]

I can set-up a junction table to create a many-to-many relationship between 2
tables in Access 2003 but have not discovered a way of filling the junction
table with primary key data from the 2 tables being joined; other than by
filling the values in manually.

Suggestions are welcome.

 

[Nikos Yannacopoulos]

So what would you expect as an alternative? All possible combinations?

 

[Guest]

Yes I know that the table must be filled in a certain way to make the
relationship between the tables, which I have been able to do by entering the
values in the junction table manually and yes, the result was a record for
every combination of the 2 keys from the tables being joined. And even for a
small data set, involves alot of data entry.

What I need is an automated way of doing this. or better still, an
alternative way of setting-up many-to-many relationships altogether.

Thanks for your response--
enruoblem

 

[Nikos Yannacopoulos]

John,

If you want the table to have a record for every possible combination,
then I'm not sure you need the table at all! The use of the table would
normally be to hold "allowed combinations"; what you are actually
talking about is a Cartesian Product, which you can produce at any time
(even without having the table), in the following manner:
Start making a query in design view, add the two tables without joining
them in any way, and drag and drop each table's PK field down to the
grid. Switch to datasheet view; you've got your Cartesian Product! Make
the query a make-table or append query, run it, your junction table is
populated. Make the two foreign key fields the (composite) PK in the
junction table, so you make sure you don't get duplicates. Is it
meaningful, though?

I'm not aware of an alternative way of setting up a many-to-many
relationship.

HTH,
Nikos

 

[Guest]

I just want a many-to-many relationship between 2 of my tables. Whatever
data needs to be in the junction table to facilitate that, and nothing more.

I have found that a make table query table cannot be used as the junction
table in Access 2003.

I am really looking for a solution that does not require the use of
cut-and-paste methods of updating tables; especially when that is what would
have to be done whenever a new report is required.

Thanks for contibuting

Further suggestions welcomed

 

[Nikos Yannacopoulos]

I just want a many-to-many relationship between 2 of my tables. Whatever
data needs to be in the junction table to facilitate that, and nothing more.

This is not making much sense. A junction table would normally have only
the necessary records (whatever they signify), not ecery possible
combination!

I have found that a make table query table cannot be used as the junction
table in Access 2003.

A make-table query is just for that, to make a table. You can't use it
as a select query. Two different things.

I am really looking for a solution that does not require the use of
cut-and-paste methods of updating tables; especially when that is what would
have to be done whenever a new report is required.

There are probably better ways for populating your junction table, than
what you've been considering so far, but I can't suggest what tehy could
be not knowing what you are trying to achieve, i.e. what the tables
represent - so what the junction table actually would represent, and how
you plan to create new records in your "primary" tables.

I'd love to help you further, but I'll be off for the next two weeks, so
I'm afraid I can't (for the time being). There are many others around
who can help you, though. I suggest you (a) start a new thread,
providing more info (what I said above I don't know), and (b) maybe do
some reading to understand relational databases, data normalization etc.
better?

Regards,
Nikos

 

[Guest]

Thanks for those helpful insights.

Enjoy your holiday

 

[Guest]

Many Thanks
--
enruoblem

This is not making much sense. A junction table would normally have only
the necessary records (whatever they signify), not ecery possible
combination!


A make-table query is just for that, to make a table. You can't use it
as a select query. Two different things.


There are probably better ways for populating your junction table, than
what you've been considering so far, but I can't suggest what tehy could
be not knowing what you are trying to achieve, i.e. what the tables
represent - so what the junction table actually would represent, and how
you plan to create new records in your "primary" tables.

I'd love to help you further, but I'll be off for the next two weeks, so
I'm afraid I can't (for the time being). There are many others around
who can help you, though. I suggest you (a) start a new thread,
providing more info (what I said above I don't know), and (b) maybe do
some reading to understand relational databases, data normalization etc.
better?

Regards,
Nikos

 

[Guest]

Like Nikos I'm unclear as to just what you want of this relationship, so lets
take a very common scenario involving a many-to-many relationship and look at
the practicalities of how data is entered.

Lets assume an Orders table a Products table and an OrderDetails table which
models the many-to-many relationship between the first two. Generally data
entry would be done via a form based on one of the referenced tables with a
subform based on the 'junction' table. For this example you would normally
have an Orders form based on the Orders table and within it a subform based
on the OrderDetails table (or more likely on sorted queries based on each
table so that the records in the form and subform are ordered logically).
The subform in this case would usually be in continuous form view or
datasheet view, though the former allows a higher standard of visual
presentation and a generally more professional appearance.

The LinkMasterField and LinkChildFields properties of the subform control
(i.e. the control in the parent Orders form which contains the subform) would
both be OrderID, i.e. the primary key of the Orders table and the foreign key
in the OrderDetails table which references it. You don't need to have a
control in the subform bound to the OrderID column as the value is inserted
into this automatically via the linking mechanism.

To enter the other foreign key, ProductID, into the subform you'd generally
use a combo box bound to the ProductID column, but which shows something more
meaningful from the Products table such as ProductName. To do this set up
the combo box with a RowSource property such as:

SELECT ProductID, ProductName
FROM Products
ORDER BY ProductName;

To get the combo box to show the ProductName values you set its properties
in its properties sheet as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm

The exact second dimension of the last property isn't crucial so long as its
at least as wide as the combo box, but the first one must be zero, as its by
virtue of this that the first column is hidden. When you select a
ProductName value form the combo box you are actually assigning its ProductID
value to the ProductID foreign key column in the OrderDetails table. The
table would probably have other Columns such as quantity and UnitPrice, to
which other controls on the subform would be bound.

There's no reason why this set up couldn't be reversed of course with an
Orders subform in a Products parent form; the principle is exactly the same,
but normal business practice favours the former arrangement of course.

You can find an example of this kind of setup in the Orders form in the
sample Northwind database. Hopefully that and my outline above of how to put
together this sort of interface will be transferable to your situation
without too much effort.

 

[Tony Toews]

I can set-up a junction table to create a many-to-many relationship between 2
tables in Access 2003 but have not discovered a way of filling the junction
table with primary key data from the 2 tables being joined; other than by
filling the values in manually.

Please explain what type of data are in the two tables. Once we
understand the overall situation we can give you some suggestions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Had the same problem. Solution is to create your own autonumber sub in the
module and use SQL statement "INSERT INTO" to populate the junction table.

Boni

 

[Guest]

John,

I am not an expert at any of this but am learning fast. I am using access
2007 and ran into the same problem. I hope this helps.

I created an append query with the 2 table that I was extracting the data
from. I created the relationship between the two fields and ran the append to
the junction table. I then changed the relationship and continued until all
the data was in the junction table.

I don't know if this makes any sense, but it worked for me. I hope it helps.

Michael