ACCESS Junction table and null values

Question

I have created a junction table with composite primary key. One of the tables joined has more records than the other and when I try to save the junction table I receive the "can't have null value in primary key field" message. What do I do?

Answer 1

The relative number of rows in the two referenced tables is not relevant.  Each row in a table which models a many-to-many relationship type between those tables must have values in each of the two columns which reference the primary keys of the two referenced tables.  An example would be an OrderDetails table modelling a relationship type between Orders and Products tables.  If the Orders table has 100 rows and the Products table has 50 rows, and each order is for three products, then the OrderDetails table would have 300 rows (100 x 3) in each of which the OrderID and ProductID values, in combination, must be distinct.  Not all products need to be referenced of course, but all Orders normally would be referenced as an order for no products would make little sense.

If the above does not help in solving your problem post back with details of the real world entity types between which the 'junction' table is modelling a relationship type.

Answer 2

How are you trying to populate the junction table? It sounds like you're trying to join every record in the first table to every record in the second, which would not make sense!

You would only add records to the junction table where there is a real-life relationship to model. The classic example is a school registration database: a Table of Students, with a StudentID primary key and biographical information; a table of Classes with a ClassID primary key; and the junction table would be an Enrollment table, with a record containing Joe's StudentID and the ClassID for English to indicate that Joe is taking English. There will typically be more students than classes, but no Nulls need to be added anywhere.