Help with a Unique query

[Somullins]

I have a database and I need help with how best to get the results. This has a customer number with the customers information and plan type. The customer can only have one plan type per customer number. How can I build a query that pulls the records that have the same customer number but different plan numbers? I want to create a report so these errors can be cleaned up.

Thanks in advance for your help!

 

[plog]

You will need an aggregate query. What you do is create a query based on your table, click the Sigma in the ribbon, bring down the customer number and then paste this into an empty field:

MultipleTypes: iif(Max([PlanType])<>Min([PlanType]), 1,0)

In the total area below it put 'Expression' and in the Criteria, put 1. Also be sure to change [PlanType] in the above code to the actual field name that holds your plan type. Run this query and it will give you all the customer numbers that have multiple plan types.

 

[Alansidman]

Try this:

SELECT Table1.[CustomerNr], Table1.[CustName], Table1.[CustPlan]
FROM Table1
WHERE (((Table1.[CustomerNr]) In (SELECT [CustomerNr] FROM [Table1] As Tmp GROUP BY [CustomerNr] HAVING Count(*)>1 )))
ORDER BY Table1.[CustomerNr];

Change the field names and table names to agree with your db.

 

[Somullins]

Thank you both for the quick responses.