Is there a way to find contacts who have multiple active or expired memberships?

Question

Due to some import errors over the past few months, we have some members who are showing with multiple memberships. Some are of the same type and some are not. The way our memberships work, no individual should ever have more than one active membership (except in very rare circumstances).

I need a search that will help me find contacts with more than one membership.

Answer 1

Alternately if you don't have sql access and are good with Excel you can do the following:

1. Export all the memberships with Find membership. It will give you one row for each membership a person has. You probably only need to export one or two field names, like sort name. (if you need more details on how to do this ask)
2. Open the giant list in Excel (if it didn't automatically)
3. Sort by sort name or display name
4. Under data in Excel add subtotals, use count and change in name
5. Collapse one level of what is shown so all you see is name and the subtotal
6. Sort descending by subtotal
7. Delete the bottom of the list where the count is 1

Answer 2

If you have sql access you can run this query.

SELECT c.display_name,
       count(*)
FROM   civicrm_contact c
JOIN   civicrm_membership m 
ON     c.id = m.contact_id 
WHERE  m.contact_id = c.id
GROUP BY 1
HAVING count(*) > 1

If you only want active memberships then add the following line between the where line and the group by line

AND now() between start_date and end_date

You didn't ask for this but if you want people with multiples of the same current membership type then you can use this code:

SELECT c.id,
       c.display_name,
       mt.name,
       count(*)
from   civicrm_contact c,
       civicrm_membership_type mt,
       civicrm_membership m
where  m.contact_id = c.id
and    m.membership_type_id = mt.id
and    now() between m.start_date and m.end_date -- current only
group by 1,2,3
having count(*) > 1
order by 3,1