Joint Table Primary Key

arishy

Registered User.
Local time
Today, 13:02
Joined
Aug 12, 2013
Messages
37
In a many-to-many relation, one create joint table and use two columns of both ID's in a Composite way. This what the text books recommend.

I had a Form/subform to view the relation between the two table. I tried to add to the subform a new relation Access did not allow me to do it. I can go to the joint table in datasheet view and ADD to my heart content observing the unique condition.

If I change the design of the joint table to auto as PK and the two ID's with no PK in either, I can update from the subform ...

My question WHY the Composite Keys do not allow me to update the joint table by adding new records in the subform ???
 
I can't answer that with certainty, but you should know that you cannot add a record to a child form until the parent has been populated with a record that can act as parent to the child. I.e. if the parent has a record with Prime Key (PK) X and the child has a record using Foreign Key (FK) X as the basis for the relationship, PK X has to exist BEFORE you can create FK X. If your parent table is actually a JOIN query, you might have a situation where you cannot create the parent - so cannot create the child.
 
Very well explained.
Let me take you to real world situation. I have two tables with many to many. Neither of them has FK. So, I created an EMPTY joint table. AND to avoid duplicates I used Composite key(PK1+PK2) in the joint table. So far so good.
Now I need to use a form/subform to CREATE the the Joint table. How I do that using user friendly UI ( using names instead of keys) to build the relation.
If I understood you, each one is parent and child !!!! not at the same time of course!!
 
arishy

almost certainly the problem is that you are misunderstanding table/form design. rather than using generic terms like PK1 and Pk2, it woluld be better if you could provide the actual table details you have - and then I am sure someone will be able to clarify the problem.
 
Your point about my misunderstanding the Table/form relation is right. And I am trying hard to correct it.
As you must realize I am talking basics here. Let me proof it:
tblProduct PK ProductID auto, ProductName text
tblOrder PK OrderID Auto, OrderName text
tblProductOrder PK =(PK ProductID & PK OrderID) as composite key
frmMain has top part: both ProductID and ProductName as text boxes
SubForm: has OrderID and OrderName as text boxes as datasheet view

The confusion starts here: watch it!!!!
To make it simple I have data for product table with PK 1,2,3 .....
Also I have Data for orders PK 1,2

When I display the form I get ProuctID 1

And since I am CREATING the Joint Table from Scrtach, The subform displays nothing
So, I click add new IN THE SUBFORM......Access protest

IF I CHANGE THE JOINT table design to
ProductOrderID auto AS PRIMARY KEY
PK product as number
PK Order as number

I can resolve Access warning.... Hence my POST
 
You should be proud of what you did back then.
Contributions like this one last for ever. Thank you Pat.
 

Users who are viewing this thread

Back
Top Bottom