Correlation between two records of the same table

[MajP]

Hard to see and debug. I would put a msgbox here in the code. That way you can ensure the function is called, and see that the sql is correct.
....
msgbox StrSql
debug.print strSql
CurrentDb.Execute strSql
...

if the function is being called this should also print the sql string to the immediate window. You can post that here.

 

[KitaYama]

@MajP In our business, we have a problem similar to OP's, but I always was too lazy to start working on it. Now that I see you're trying to show your solution, I'd like to point something out. (Even if what your last sample database is not what I'm looking for, I thought it may give me some ideas)

Two points that I thought should be pointed out:

1. Comparisons should be permanent. For example you have a "Cheaper Option", but productA can not always be cheaper than ProductB. Prices change based on a lot of factors. So the correct comparison should show Price, Then in the query that shows the results, you can sort the price to see which one is cheaper.
2. I think a relation (similarity) should be a both side relation. What you showed is a one side relation.
   In following image, I've set a relation between Chai Chai Chai Chai And Northwind Traders Beer (Drink Pairing)
   I expect if I go to Northwind Traders Beer , to see Chai Chai Chai Chai as its Drink Pairing too.
   In a real world situation, if ProductA is similar to ProductB and ProductB is similar to ProductC, then I expect to see both ProductA and ProductB in the results when I search for Products that are similar to ProductC. I mean when searching for either of them, you should see the other two.

@MajP For now, you don't need to change your design, because it seems this works for the OP, and it's irrelevant to add complexity to a working solution. I will start a new thread on my own and will ask for your help later. Thanks in advance
@Faoineag My apologies for jumping in and bringing up an unrelated scenario.

 

[MajP]

@MajP For now, you don't need to change your design, because it seems this works for the OP, and it's irrelevant to add complexity to a working solution. I will start a new thread on my own and will ask for your help later. Thanks in advance

It is fully make believe and not really a working solution, just a demo of some concepts. I do not even know what kind of data the OP has.

1. Comparisons should be permanent. For example you have a "Cheaper Option", but productA can not always be cheaper than ProductB. Prices change based on a lot of factors. So the correct comparison should show Price, Then in the query that shows the results, you can sort the price to see which one is cheaper.

It is make believe data and probably not a good choice. That could be determined by the real data fields. Healthier alternative is better example since that would be subjective if it is a substitution.

In a real world situation, if ProductA is similar to ProductB and ProductB is similar to ProductC

I definitely disagree that is the real world. It can be true, but can also not be true.
Those relations can be unidirectional or directional. If it is unidirectional (if a relates to b then b relates to a) you have an option. You only save one side of the relation and then in your forms and queries you do a union query to show the other. Or you can save both.

But clearly this is not the case on all relations. Assume it is a dating App. The fact that A is attracted to a B,C,D does not mean B,C,D are attracted to A. Or if Arnold is attracted to Betty and Betty is attracted to Carl then Arnold is not necessarily attracted to Carl (even with all the gender fluid stuff now a days). In a genealogy app if A is Grandparent of C, C is not Grandparent of A but Grandchild. In fact I did this with the genealogy example I did where you can view from the descendants up to the predecessor or from the predecessors down.

 

[KitaYama]

I definitely disagree that is the real world. It can be true, but can also not be true.

Well, I was talking about the real world I live in (my profession). I'm glad to see there are different real worlds than mine.

 

[MajP]

Well, I was talking about the real world I live in (my profession)

If this is the case, how would I do it?
Lets say we are associating products that can be swapped out. And say it is true that if A can replace B then B can replace A. If B can replace C then A can replace C and C can replace A.

I would save both directions of the relation.
So if you save the record A, B in the junction table then trigger the the insert query B, A.
That trickier part is the rest of the relations which would have to be created recursively. So it would create A, C. And if C is related to D it would create A,D. Then finish it up and create all the backward relations. C, A and D, A.

 

[KitaYama]

If this is the case, how would I do it?

To prevent mixing different scenarios,, I'll explain my situation in a new thread.
Thanks.

 

[Faoineag]

@KitaYama You don't have to apologize, on the contrary, you allowed me to understand that my concept of relationship (similarity) was wrong: I took it for granted that every relationship between two products was unidirectional and that many products were potentially related to many others, at least on a theoretical. So, using your screenshot as an example, I didn't realize that product A (Chai Chai) has B (Northwind traders beer) as a related product, while for B I can't insert A in the subform.
So @MajP , maybe before checking the code can you help me correct the relationship type? Thank you

 

[Faoineag]

To prevent mixing different scenarios,, I'll explain my situation in a new thread.
Thanks.

For my part it is not necessary, because what I am looking for is what you expressed in the screenshots, that is, I think we are looking for the same thing

 

[KitaYama]

For my part it is not necessary, because what I am looking for is what you expressed in the screenshots, that is, I think we are looking for the same thing

I'd already posted a new help request before readying your comment.
You can have an eye on it if you're interested:

Solved - Relations between products : Similar Products.

This is a branch of This thread. My case seems to be slightly different from the mentioned one. Hence, I thought a new thread may prevent over lapping comments and problems. I have a product table. Currently 48895 records. And of course increasingly in a daily basis. Some products are similar...

www.access-programmers.co.uk

 

[Faoineag]

Hard to see and debug. I would put a msgbox here in the code. That way you can ensure the function is called, and see that the sql is correct.
....
msgbox StrSql
debug.print strSql
CurrentDb.Execute strSql
...

if the function is being called this should also print the sql string to the immediate window. You can post that here.

I think I don't understand where I have to insert the msgbox in the code?

 

[MajP]

I think I don't understand where I have to insert the msgbox in the code?

When someone says a procedure does not work, I am assuming that means nothing happens. (no error message)
one of the first things to check in those cases where nothing happens if if the code is firing. Is it being called correctly. I check this quickly with a messagebox.

Private Function AssignRelation()
  Dim prod1 As Long
  Dim prod2 As Long
  Dim strSql As String
  prod1 = Me.Parent.ID
  prod2 = Me.ID
  strSql = "Insert into tblProductRelations (product1_ID_FK, Product2_ID_FK) values (" & prod1 & ", " & prod2 & ")"

Msgbox StrSql
Debug.print strSql

  CurrentDb.Execute strSql
  Me.Parent.subfrmRelations.Form.Requery
  Me.Requery
End Function

1. If the messagebox does not show I know that the code is not called.
2. If the messagebox shows I can look in the immediate window and check the SQL string I printed and verify that is correct.
3. If that is the case then I probably should have added dbfail on error to see if the string can be executed. The fail on error will pop up an access sql error message.
currentDb.execute strSql, dbFailOnError

 

[Faoineag]

Hi @MajP, I have temporarily set aside the problem of the double-click code because I would first like to resolve the issue of not only unilateral relationships between products. Following the way you approached the problem posed by KitaYama, I recreated a sample database of my own and found some oddities, could you kindly check if this is correct? Thank you
In my sample database I have these items:
Apple(A)
Pear (B)
Banana (C)
Fishing (D)
Apricot (D)
Paper (E)
Salad (F)

In the product sheet A I associate it with D
if I go to D I see that it is automatically associated with A, then if I go back to A I see that the association with D appears twice, is this normal?
Then I associate B with C, C is automatically associated with B, if I then associate C with A and go back to A I see that it is associated 2 times with D, 1 time with C and 1 time with B.
If I go to B I see that it is associated 3 times with C and 1 time with A.
If my procedure is correct, can't we ensure that the same associations are not repeated for each product?

 

[MajP]

I go back to A I see that the association with D appears twice, is this normal

I did "greedy code." Instead of checking if a relation exists, I applied a composite index to the table. I called it ID1_ID2 and made it consist of the two foreign key fields.

The index makes the combination of the two fields unique.
if A, B exists already you cannot add A,B.
If you use an Execute query without DBFailonerror. It will simply ignore any duplicates and not provide an error message.

You will have to first remove the duplicates before applying this index.