Answers.Body: <p>If you have sql access you can run this query.  </p>
  
  <pre><code>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(*) &gt; 1
  </code></pre>
  
  <p>If you only want active memberships then add the following line between the where line and the group by line</p>
  
  <pre><code>AND now() between start_date and end_date
  </code></pre>
  
  <p>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:</p>
  
  <pre><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(*) &gt; 1
  order by 3,1
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-13T00:12:54.067Z
Answers.LastActivityDate: 2015-08-31T19:57:07.183Z
Answers.LastEditDate: 2015-08-31T19:57:07.183Z
Answers.UserDisplayName: Paul-Tahoe
Answers.Body: <p>Alternately if you don't have sql access and are good with Excel you can do the following:</p>
  
  <ol>
  <li>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)</li>
  <li>Open the giant list in Excel (if it didn't automatically)</li>
  <li>Sort by sort name or display name</li>
  <li>Under data in Excel add subtotals, use count and change in name</li>
  <li>Collapse one level of what is shown so all you see is name and the subtotal</li>
  <li>Sort descending by subtotal</li>
  <li>Delete the bottom of the list where the count is 1</li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-13T22:52:19.360Z
Answers.Text: 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!
Answers.UserDisplayName: Damon Kirkpatrick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-13T01:33:32.670Z
Answers.LastActivityDate: 2015-05-13T01:33:32.670Z
Answers.UserDisplayName: Paul-Tahoe
Body: <p>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).  </p>
  
  <p>I need a search that will help me find contacts with more than one membership.</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-08-21T15:25:17.310Z
Comments.Text: 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).
Comments.UserDisplayName: Robf
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-08-31T12:49:51.917Z
Comments.Text: I think this wins the award for most underappreciated question on this site!
Comments.UserDisplayName: Jon G - Megaphone Tech
Tags: <civimember>
Title: Is there a way to find contacts who have multiple active or expired memberships?
ThreadText: # Is there a way to find contacts who have multiple active or expired memberships?
  
  (Asked by: username_0 on 2015-05-12)
  
  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.
  
  **Tags:** civimember
  
  > **Comments:**
  > **username_1**: 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).  
  > **username_2**: I think this wins the award for most underappreciated question on this site!  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_3 on 2015-05-13. Score: 4 | Accepted answer
  
  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
  
  > **Comments:**
  > **username_0**: 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!  
  
  ---
  
  ### Answer by username_3 on 2015-05-13. Score: 5
  
  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
  
  
  ---
  
  