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

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.

• I've got a similar problem (multiple imports/multiple records) and I can find them just fine with Advanced Search. The problem is I can't find a way to do a batch dedupe and I'd have to find and delete about 500 dupes. I'll keep looking for a way to do that, but I would welcome any suggestions (preferably not involving direct manipulation of MySQL). – Robf Aug 21 '15 at 15:25
• I think this wins the award for most underappreciated question on this site! – Jon G - Megaphone Tech Aug 31 '15 at 12:49

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
• Thanks for the great suggestion. Had a little trouble with subtotals in Excel so I used a pivot table instead with the same results. Also, rather than sorting by name or display name, I used Internal Contact ID so I didn't have to worry about duplicate names (of which we have quite a few). Worked like a charm and found only a few records to clean up. Thanks! – Damon Kirkpatrick May 13 '15 at 22:52

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