Select Distinct & Count

[LaurieW]

I have the query below that I need to change to remove duplicates in a field "tblCalls.CN". I have done this in other queries in this same database adding "SELECT DISTINCT tblCalls.CN," to the front of the query statement but that does not work with this query. This query is different in that it counts the field "tblCalls.ID".

Is there a way to do both of these things? I've tried it in one query or using a subquery, but I can't get it to work. Any suggestions would be greatly appreciated. Thanks!

SELECT tblAddress.AddressID, tblAddress.Unit_ID, tblAddress.Project_ID, tblAddress.Address, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.Type, Count(tblCalls.ID) AS CountOfID
FROM tblAddress INNER JOIN tblCalls ON tblAddress.AddressID = tblCalls.AddressID
WHERE (((tblCalls.[Call Date]) Between Date() And Date()-60))
GROUP BY tblAddress.AddressID, tblAddress.Unit_ID, tblAddress.Project_ID, tblAddress.Address, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.Type
HAVING (((tblAddress.Address)<>"Not a PHA Address") AND ((tblAddress.Type)=[Duplex, Family, Hi-Rise or Scat ?]) AND ((Count(tblCalls.ID))>1));

 

[vbaInet]

How about DISTINCTROW?

 

[LaurieW]

When I add "SELECT DISTINCTROW tblCalls.CN, " to the front of the query it doesn't work. Instead of finding distinct tblCalls.CN, it groups them so I have a list of the same address many many times instead of it giving me a count of the address. Here is what that query looks like in total:

SELECT DISTINCTROW tblCalls.CN, tblAddress.AddressID, tblAddress.Unit_ID, tblAddress.Project_ID, tblAddress.Address, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.Type, Count(tblCalls.ID) AS CountOfID
FROM tblAddress INNER JOIN tblCalls ON tblAddress.AddressID = tblCalls.AddressID
WHERE (((tblCalls.[Call Date]) Between Date() And Date()-60))
GROUP BY tblAddress.AddressID, tblAddress.Unit_ID, tblAddress.Project_ID, tblAddress.Address, tblAddress.City, tblAddress.State, tblAddress.ZipCode, tblAddress.Type, tblCalls.CN
HAVING (((tblAddress.Address)<>"Not a PHA Address") AND ((tblAddress.Type)=[Duplex, Family, Hi-Rise or Scat ?]) AND ((Count(tblCalls.ID))>1));

 

[vbaInet]

Ok go back to DISTINCT. You need to hide some fields in order to have distinct rows.

 

[LaurieW]

So then I'm back where I started.

 

[vbaInet]

Ok, let me break it down for you. I think you've confused yourself by trying to do everything in one query.

1. Create a query that will Count() the Call IDs. Let's call this qryCountCallIDs
2. Create another query using the DISTINCT keyword
3. Join the queryCountCallIDs via the respective field in query (2).

DISTINCT looks at the entire row and if it at least one character that doesn't correspond to a similar looking row, it will return both or however many other instances it finds. So remember to choose your fields wisely.

 

[LaurieW]

Thanks. I've tried to break it out before into 2 queries, but it did not work. I will try again following your suggestion and will keep you posted.

 

[vbaInet]

Sure! If you're not having any luck post a sample db and I'll have a look.

 

[LaurieW]

I believe it have it working correctly, but I had to do it backwards from your suggestion. I needed to use SELECT DISTINCT in the first query and then in the second query count. If I did the count first it counted the duplcates. Here is what I ended up with:

Query One:
SELECT DISTINCT tblCalls.CN, tblAddress.AddressID, tblAddress.Address, tblAddress.Type
FROM tblAddress INNER JOIN tblCalls ON tblAddress.AddressID = tblCalls.AddressID
WHERE (((tblAddress.Address)<>"Not a PHA Address") AND ((tblAddress.Type)=[Duplex, Family, Hi-Rise or Scat ?]) AND ((tblCalls.[Call Date]) Between Date() And Date()-60));

Query Two:
SELECT Count(qryExcessiveCallsSelectDistinct.AddressID) AS CountOfAddressID, qryExcessiveCallsSelectDistinct.Address, qryExcessiveCallsSelectDistinct.Type, tblAddress.ZipCode
FROM qryExcessiveCallsSelectDistinct INNER JOIN tblAddress ON qryExcessiveCallsSelectDistinct.AddressID = tblAddress.AddressID
GROUP BY qryExcessiveCallsSelectDistinct.Address, qryExcessiveCallsSelectDistinct.Type, tblAddress.ZipCode
HAVING (((Count(qryExcessiveCallsSelectDistinct.AddressID))>1))
ORDER BY Count(qryExcessiveCallsSelectDistinct.AddressID) DESC;

Thanks to you once again for your help!

 

[vbaInet]

I thought you wanted to count the number of duplicates they were as well.

Glad you got it working!

 

[LaurieW]

No, when there are duplicate CN numbers I only want to count that as one record. Thanks again!