Solved Compare and Output values of the same field in a table

[AlexN]

Hi everyone again,

I have a table with some data, the kind showing in the following image:

I want to query (or whatever else needed) this table and get the groups having same number of members the way shown below:

Group D	Group E	8​
Group D	Group F	8​
Group E	Group F	8​
Group G	Group H	7​
Group J	Group K	4​
Group J	Group L	4​
Group K	Group L	4​

I also want this to be able to change dynamically as data in table does. Is it doable?

Thank you all in advance

 

[June7]

Can do a self-join linking on MembersCount field. Problem is records will be double because you'll get pairs going both ways, like:

D F
F D

Another option is to use Allen Browne's ConcatRelated function http://allenbrowne.com/func-concat.html to produce an output like:

MembersCount	Expr1
2​	n
3​	m
4​	j, k, l
6​	i
7​	g, h
8​	d, e, f
9​	c
10​	b
15​	a

 

[AlexN]

Can do a self-join linking on MembersCount field. Problem is records will be double because you'll get pairs going both ways, like:

D F
F D

Another option is to use Allen Browne's ConcatRelated function http://allenbrowne.com/func-concat.html to produce an output like:

MembersCount	Expr1
2​	n
3​	m
4​	j, k, l
6​	i
7​	g, h
8​	d, e, f
9​	c
10​	b
15​	a

Thanks,
1st suggestion looks good but, seems I can't manage to get the results the way I want. Tried linking MembersCount field but I'm getting all records twice. I only want all possible variations of the equal ones

 

[CJ_London]

from the look of the desired output a self join should be sufficient combined with a criteria

SELECT A.groupname, B.groupname, B.memberscount
FROM tblGroups A INNER JOIN tblGroups B ON A.memberscount=B.memberscount
WHERE B.groupname>A.groupname
ORDER BY A.groupname

 

[AlexN]

from the look of the desired output a self join should be sufficient combined with a criteria

SELECT A.groupname, B.groupname, B.memberscount
FROM tblGroups A INNER JOIN tblGroups B ON A.memberscount=B.memberscount
WHERE B.groupname>A.groupname
ORDER BY A.groupname

That seems to do the job. So easy but couldn't find it.
Thank you so much CJ.

Thank you all, you've been so helpful all these years.

 

[June7]

Dang, I was close. Difference was I used <> instead of >.