records w/similar data

[slimjen1]

All, using access 2016. I have one table with custid, state and order id.. some have the same cust id and state. I want only the records that have the same custid and same state. I keep getting everything. My query is the following

select  customerid,       count(*)as st_count
from    custord
groupby
        customerid having  count(*)>1

Is it because its' one table? I tried a count but still get everything. what do I need to get this data? It's Friday and I can't think

 

[isladogs]

You need to include the State field in the Group By part of your query

As you are essentially trying to do a duplicate record query try using the duplicates wizard including both CustID & State fields

The result will be something like this:

SELECT  CustomerID, State
FROM Custord
WHERE (((Custord.[CustomerID]) In (SELECT [CustomerID] FROM [Custord] As Tmp GROUP BY [CustomerID],[State] HAVING Count(*)>1  And [State] = [Custord].[State])));

NOTE: the above may have typos which is why I'm recommending you use the wizard

 

[slimjen1]

Got it...Thanks for your help!!

 

[isladogs]

You're welcome

BTW - its Thursday here in the UK ... where are you posting from if its Friday for you?