Query for repeat pairings and give count if found

[MikeCt]

I have groups of members that meet once a week.
Every week the members are in different groups with different members
Each week all members are recorded as to who they were grouped together with in a table "GroupData"



How can I check if a member has been grouped together in a previous week and if so, how many times

In the database attached I know member #30 has been grouped together with member #175, twice
(Its possible other members may have repeats also, in the example attached)

Thanks for any help

[madpiet]

Oh, previous week... I think I skipped that part.

here's the groupings stuff:
xjGroupings (Cross join):

SELECT GroupData.MemberID, GroupData_1.MemberID AS GroupedWithMember
FROM GroupData AS GroupData_1 INNER JOIN GroupData ON (GroupData.Group = GroupData_1.Group) AND (GroupData_1.EventDate = GroupData.EventDate);

then query and summarize that:

SELECT xjGroupings.MemberID, xjGroupings.GroupedWithMember, Count(xjGroupings.MemberID) AS CountOfMemberID
FROM xjGroupings
GROUP BY xjGroupings.MemberID, xjGroupings.GroupedWithMember
HAVING (((xjGroupings.MemberID)<>[xjGroupings].[GroupedWithMember]) AND ((Count(xjGroupings.MemberID))>1));

[MikeCt]

Thanks this is exactly what I was looking for.

I have a question since I'm trying to learn as much from this as possible. With what you have given me it is easy to loop thru the list and get any results I need. Is it faster to query for results on one particular member at a time and is that possible. This query returns a list of 20 items. As the groups meets during the year this query will create a list maybe up to a few hundred entries. I know that's not much but I'm trying to learn to do what's best.

Thanks for your quick response
Mike

[madpiet]

Thanks this is exactly what I was looking for.

I have a question since I'm trying to learn as much from this as possible. With what you have given me it is easy to loop thru the list and get any results I need. Is it faster to query for results on one particular member at a time and is that possible. This query returns a list of 20 items. As the groups meets during the year this query will create a list maybe up to a few hundred entries. I know that's not much but I'm trying to learn to do what's best.

Thanks for your quick response
Mike

What do you mean by "looping"? Cursors/recordsets are usually a dirty word in SQL. Well, it's a tool of last resort. Only use if if you absolutely need to because (1) it doesn't scale, (2) it can be crazy slow.

[CJ_London]

Is this the same question as your other thread a couple of months ago?
https://www.accessforums.net/showthread.php?t=90596

[MikeCt]

By looping I mean going thru the list from top to bottom looking for a specific ID
This is why I was asking if I could use something in your Query like "WHERE MemberID = 30" retrieving only one item
I'm going to play with what you sent me and learn from it what I can from it.
Thanks for not giving me a hard time, I'm just trying to learn how to do thing right.
Thanks again
Mike

[June7]

"Right" is what solves issue to your satisfaction.

Yes, building a WHERE clause in query is one way. Can hard-code the parameter or make it dynamic.
Or use filter tools on ribbon or right click menu or quick search/sort from table/query headers.

I don't use search parameters in queries if I can help it. I use VBA to build criteria and apply to form or report.

Are you the only user of this db (I assume there is more to your db than the one table provided)?