Correlation between two records of the same table (1 Viewer)

[Faoineag]

Hi everyone, I have a database in which I have inserted the products of my business, now I would like to specify the similarities that exist between the articles, for example product A is similar to product B and product C. I have a Products table on which the "Product Sheet" form is based, I would like to insert a subform on this form where I list the correlations between the product in question and others, obviously present in the Products table. A button that opens another form showing these correlations would also be fine. Unfortunately I have no idea how this relationship can be created between different records in the same table. Can anyone give me some suggestions? Thanks in advance to anyone who will respond

 

[MajP]

TblProducts
-- ProductID
-- other product fields

tblProductRelations
-- RelationID
-- Product1_ID_FK ' foreign key to tbl products
-- Product2_ID_FK ' Foreign key to tbl products
-- maybe Relation Type
-- maybe relation Note

 

[MajP]

In the relation table you want to check that you do not add product 123 to both Product1 and Product2. Not a big problems but tells you nothing.
You need to decide if A is related to B do you want to also store B to A. It is easier if you require both directions. If it is good enough to store one direction you have to account for this in queries and forms and gets a little tricky, but it can be done.

If this is not many to many. If this is like a parent child where A can only be related to one product and B can only be related to one product then this can be done in the product table. But looks like you said A is related to b and c. Thus you need the junction table I showed.

-- ProductID
--RelatedProductID_FK ' relates to one record in the same table

 

[ebs17]

SELECT
   P1.ProductName AS Product1,
   P2.ProductName AS Product2,
   P1.Size - P2.Size AS Diff
FROM
   tblProducts AS P1,
   tblProducts AS P2
WHERE
   P1.ID < P2.ID
ORDER BY
   Abs(P1.Size - P2.Size)

Here, similarity to a number was interpreted as size.

 

[theDBguy]

I was about to say the same thing (using phone, so a little slow typing). Since you said a product can relate to multiple other products, a junction table makes sense. But, it could also mean, as already mentioned, creating two separate records to describe a single relationship.

 

[Faoineag]

Grazie a tutti, ho provato a seguire le vostre indicazioni per quanto riguarda la tabella di giunzione, ma purtroppo non ci sono riuscito, come potete vedere nel file allegato. Principalmente, però, non capisco come si facciano a collegare molti prodotti ad uno solo, tutti appartenenti alla stessa tabella. Nell'esempio che allego, se ne inserisco due ottengo già un messaggio di errore. Puoi dirmi dove sbaglio? Grazie

 

[MajP]

Thanks everyone, I tried to follow your instructions regarding the junction table, but unfortunately I wasn't able to, as you can see in the attached file. Mainly, however, I don't understand how you can connect many products to just one, all belonging to the same table. In the example I attach, if I insert two I already get an error message. Can you tell me where I'm wrong? Thank you

Look at the Master child link. That was wrong
Look at the bound control in the Subform. That was wrong.

Guarda il collegamento figlio Master. Era sbagliato
Guarda il controllo associato nella sottomaschera. Era sbagliato.

 

[Pat Hartman]

@Faoineag The people on the forum who are most likely to help you speak English. Your best option for help is to use Google Translate to translate your posts into English. It also helps if you mention that you are using a translator since sometimes technical terms get translated literally rather than using their technical meaning and this can be confusing or even amusing.

We may also be having a language issue with how people so far have responded to your question. "relate" in the sense of a relational database (which Access is) implies linking tables together on a pair of fields we call the primary key and the foreign key. So, htat is how everyone so far has framed their answer.

There is another interpretation of what you mean by related and that is "similar". The two words are not exactly synonyms but I have seen them used as such. In that context, the various products would have a number of attributes such as size, color, weight, material. And your question is more along the lines of finding other products made of wood or painted purple. So by related, do you actually mean "most similar" in the sense that one product might be substituted for another?

 

[Faoineag]

I apologize for formulating the request in my language instead of English. Yes, I use Google Translate, without realizing I must have copied my sentence in Italian instead of English.
@Pat Hartman Your interpretation is very correct, with the expression "related products" I was thinking (I was superficial) precisely of the meaning of "similar", that is, that they have the same or at least similar function, as can be seen on some websites in in which, on the description page of the product to be sold, there is a space of similar products.
@MajP thank you very much, that's exactly what I was looking for

 

[MajP]

If you have to do a lot of associations. A subform approach may not be the easiest to use. You may want a checklist where you can quickly see and pick related items. May be interested in ways to do this.

How to create multi value select in access form and add values in database table

I have a design a form in which i have below fields: First name, Surname, Dob, Age, Gender, Referral reason, Work Pattern, Priority , Treatment location, Initial Appointment date, Discharge date, intervention, Nature of Injury. For "Nature of Injury", user can select multiple values for...

www.access-programmers.co.uk

 

[Faoineag]

There are not many associations to make for each product, but there are many products to scroll through to find the ones I need to associate. Let me explain better: if I have to associate product A with B and C, I have to look for B and C among thousands of articles, that's why I thought of the combo box in the subform to choose. Can you explain to me better the solution you have in mind? Thank you

 

[MajP]

if I have to associate product A with B and C, I have to look for B and C among thousands of articles, that's why I thought of the combo box in the subform to choose

That may work for you, I do not know. But scrolling thousands of products with little information may not be easy. I do not know how much information you need to see to determine if there is a relation. You may need something more robust.

Lets say I have thousands of products. Shirts, Pants, Shoes, Belts, etc.. On your site when someone picks a pair of Pants they see related items that they may be interested to go with the Pants. Lets say someone needs to create relations.

I would have a very hard job picking these items from a combobox, so I would need something fancier.
I would have my main form with the Product information, maybe even an image of the Pants
I would then have a subform for selecting related products, that once selected would display them in the selection list. This subform would be my complete list of products. But now on the subform I could filter by belts and see images of all the belts. I could filter belts by color. Etc. Once a product is found I click and add to the related list. Using a subform or even a popup form, you can have lots of features to filter and sort to find items to relate

I do not know if you need this type of functionality, because I do not know exactly what you are doing. But IMO it is very hard with limited information to determine and find related items.

 

[Faoineag]

Yes, you have fully understood the meaning of what I would like to do. It's not clear to me how you can add items from the subform that contains all the products to the selection list

 

[MajP]

I will make a demo, showing a few capabilities.

 

[Pat Hartman]

if I have to associate product A with B and C, I have to look for B and C among thousands of articles

What makes them alike? If they have attributes that make them similar, then you would use the attributes to link the items rather than doing it manually.

 

[Faoineag]

This is another sore point. What essentially makes them similar is the function, at least in the case of equipment, while for clothing it may be the type, etc. In this case tags would be useful, but honestly I've never heard of them applied to Access

 

[theDBguy]

In this case tags would be useful, but honestly I've never heard of them applied to Access

Actually, "tags" make more sense in Access than just simply connecting products together. In that case, when you want to list all products with a certain tag, it would be easier to query the tables by simply providing the tags as a criteria.

 

[gemma-the-husky]

Maybe you can group products by style, colour finish etc, so if someone wants a bathroom suite you can show them all the white models, and all the chromed handles and so on.

It's worth looking at some commercial websites to see how they handle filtering.

It can still be hard to get close to a small enough selection of alternative products.

Hard to maintain with a large list of products as well.

 

[Pat Hartman]

I've never heard of them applied to Access

Access works with relational databases. That is a very common technique for relational databases.

 

[MajP]

Here is a simple and silly example. The concept is that you may need some robust features to find and subjectively link items.

Assume you have products from the Northwind market
Products Products

Product Name	Description	Standard Cost	List Price	Reorder Level	Target Level	Quantity Per Unit	Discontinued	Minimum Reorder Quantity	Category
Chai Chai Chai Chai		$13.50​	$18.00​	10​	40​	10 boxes x 20 bags	No​	10​	Beverages
Northwind Traders Syrup		$7.50​	$10.00​	25​	100​	12 - 550 ml bottles	No​	25​	Condiments
Northwind Traders Cajun Seasoning		$16.50​	$22.00​	10​	40​	48 - 6 oz jars	No​	10​	Condiments
Northwind Traders Olive Oil		$16.01​	$21.35​	10​	40​	36 boxes	No​	10​	Oil
Northwind Traders Boysenberry Spread		$18.75​	$25.00​	25​	100​	12 - 8 oz jars	No​	25​	Jams, Preserves
Northwind Traders Dried Pears		$22.50​	$30.00​	10​	40​	12 - 1 lb pkgs.	No​	10​	Dried Fruit & Nuts
Northwind Traders Curry Sauce		$30.00​	$40.00​	10​	40​	12 - 12 oz jars	No​	10​	Sauces
Northwind Traders Walnuts		$17.44​	$23.25​	10​	40​	40 - 100 g pkgs.	No​	10​	Dried Fruit & Nuts

Etc.

Then you have some relations types.

tblRelation_Types tblRelation_Types

RelationType
Main Course Pairing
Desert Pairing
Appetizer Pairing
Drink Pairing
Cheaper Option
Healthier Option

So for a fish product you might want to pair it with a white wine. Or you can replace sauce A with a healthier option. Etc.
You cannot do this type of relations with tags or groups as people have suggested. These are one direction relations. Because product B is a Cheaper option for A, but Product A is a more Expensive option for B.

If attempting to do this with a pull down it would be near impossible, to show the information to subjectively build the relation.

I set the subform to allow this, but provided some very simple capability using a subform on the right.
If I want to see a cheaper or potentially healthier drink option to my Chai, Chai I have some function on my subform

I can filter by beverages

I can sort by price

I can then double click on my filtered sorted options to add to my relations

So as we all hoped Beer is Healthier and Cheaper than Chai, Chai.

Using a datasheet is nice since the filtering and sorting requires no code. The double click code is simply

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 & ")"
  CurrentDb.Execute strSql
  Me.Parent.subfrmRelations.Form.Requery
  Me.Requery
End Function

It could get way fancier than that. My point is (having done this) you may have to build a more robust capability to help find possible associated items.