Query to Find Parts with similar materials

[tvanhuis]

Hello,

I need to come up with a query to find our products with similar materials, and i'm hitting a wall. Here is an example what my initial table looks like:

ID	PartNo	Material
1	SKU1	A
2	SKU1	B
3	SKU1	C
4	SKU2	A
5	SKU2	C
6	SKU3	B
7	SKU3	C
8	SKU4	A
9	SKU4	B
10	SKU4	C

What i need to find is how many parts are similar to each other. So i need to know that sku1 and sku4 both have the same materials, but also that sku1 and sku2 share 2 materials, etc. So my results should look something like:

PartNo	SimilarTo	CountOfMaterialsShared	MaterialsShared
sku1	sku4	3	A,B,C
sku1	sku2	2	A,C
sku1	sku3	2	B,C
sku2	sku1	2	B,C
sku2	sku3	1	C

What i can think of is that i need to loop over each sku and find all similar skus, but i can't think of how to do this in a query, but all of my data are in tables (access 2016) so i'm stuck. I have been pointed to a cool function to concatenate data (http://allenbrowne.com/func-concat.html), but i'm still not sure how to get columns 2 and 3 above. Anyone have any ideas? Thank you!!

[Micron]

I think that to do this in code would require the sku's to be sorted if that is what everything is to be based on. If you cannot order them reliably via a query, then I can't see a way.
Do you really have to list them as comma separated values? What is the need that drives that?

[CJ_London]

you would join the table on itself

the basic query would be

SELECT A.PartNo, B.Partno AS Similar2, A.Material
FROM tblProducts A INNER JOIN tblProducts B ON A.Material=B.Material
WHERE B.Partno>A.PartNo
ORDER BY A.Partno

From there you can change it to a group by query to get the count and use Allen Brown's concatrelated function to get the MaterialsShared column

Edit: here is a link to the concatrelated function http://www.allenbrowne.com/func-concat.html

[tvanhuis]

SELECT A.PartNo, B.Partno AS Similar2, A.Material
FROM tblProducts A INNER JOIN tblProducts B ON A.Material=B.Material
WHERE B.Partno>A.PartNo
ORDER BY A.Partno

Thanks, i'll give that a shot.