Trouble showing Many-to-Many relationship in subform 2

[demosoc]

Good morning,
And please pardon my lack of Access vocabulary. I’m just learning…

I have a table [tblProducts] that has a many-to-many relationship with itself. It connects via a junction table [tblProductsProducts] by the Product_ID field [and Product_1_Id]. Basically, there are products which have relationships with each other for various reasons, and I want to display them on a subform based on the Products form.

For example if I'm looking at the form for ProductA I want to see all of it's relationships; ProductB, ProductR and ProductZ. However, it will only show me those where it was entered in tblProductsProducts.Product_ID not where it was entered in tblProductsProducts.Product_1_ID. So if the ProductZ record was entered as the main one, then ProductA will show up on the ProductZ form (via the subform), but ProductZ will not show up on the ProductA form (via the subform).
I tried to add both links to the subform (Link Child Fields: Product_Id; Product_1_Id Link Master Fields: Product_Id; Product_Id) but that didn’t work at all, and actually removed all the other products.

Is it possible to show all of a product’s relationships to other products in the same subform?
If so, is the solution the same for reports?

Thank you

 

[PHV]

I'd use an union query as the RecordSource of the subform:
SELECT Product_Id, Product_1_Id FROM tblProductsProducts
UNION SELECT Product_1_Id, Product_Id FROM tblProductsProducts

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

does your link table show all of the relations?
Example
A related to , B,C,D
B related to A, D
C related to A
D related to A, B

ProdID ProdID1

A B
A C
A D
B A
B D
C A
D A
D B

This would be correct to duplicate both (A D and D A). But if you only show one instance (A D) then you would need a union query as PHV points out.

 

[demosoc]

Sorry for the delay- I had an early weekend.

The link table shows only one AD (not the corresponding DA).
It seems redundant to have a record showing the same relationship in the table twice (AD and DA)...

I tried the union query:

SELECT Product_Id, Product_1_Id, Relationship_Level, Product, Product_1 FROM tblProductsProducts UNION SELECT Product_1_Id, Product_Id, Relationship_Level, Product, Product_1 FROM tblProductsProducts;

It brought all of the data into the subform nicely! Unfortunately, it didn't allow any additional data to be entered. The users want to be able to enter new relationships (based on security) from the ProductProduct subform which resides on the Products form.

Basically, I don't want to require end users to remember to enter the data twice, nor do I want to force them to go to an additional data entry form as that would be different from how they enter data everywhere else.

Is it possible to use the union query and still allow new records to be created on the subform? If not, I guess I could add another subform linking Product_1_Id though that looks clunky.

Thanks!

 

[MajP]

Probably lots of choices on how you do this. A popup form, a second subform, etc., but the union query will be non-updateable.

Personally, I think I would actually upate the data using an insert query so that when I save

ProdID ProdID1
A B

it automatically save a record B A as well. This creates a more natural data base relationship of Parent child. It will make other queries and relationships easier. Need also to delete B A when you delete A B.

 

[MajP]

This should be transparent to the user, and will solve your current problem.

 

[fneily]

It seems redundant to have a record showing the same relationship in the table twice (AD and DA)...". No, it isn't. MajP was correct. AD and DA are not the same relationship. English - A is composed of D. D is composed of A. Not the same. A is composed of D,F,K. D,F,K is composed of A. May or may not be true. So your junction table should look like what MajP presented.

 

[demosoc]

Thank you all so much for your time- I'm slowly learning. Now I'm off to learn about insert queries...
Thanks again!

 

[MajP]

I found the trick to do this is writing a query that updates any number of records, not writing a query that you have to fire for each time you enter a new record. This is how I would do it to make it easy.

1) Build a union query. I will call it "qryUnionProducts"
2) Build an append qry that updates all records/and only those records in "qryUnionProducts" not in "tblProductsProducts". Something like

INSERT INTO tblProductsProducts ( ProductID, Product1ID )

SELECT
qryUnionProducts.ProductID,
qryUnionProducts.Product1ID

FROM qryUnionProducts

LEFT JOIN
tblProductsProducts ON (qryUnionProducts.ProductID = tblProductsProducts.ProductID) AND (qryUnionProducts.Product1ID = tblProductsProducts.Product1ID)

WHERE tblProductsProducts.ProductID Is Null

I am not very good with SQL so there may be a cleaner version, but this does the trick.