Count Unique Records in a Query

[Moxioron]

Hello.

Trying to count unique records in just one field ... while getting totals in other columns. This is what I have ... I have highlighted the row I am having the issue with.

When I run the query, I get "At most one record can be returned by this subquery". Thank you

SELECT
Left([tbl_ROL150_Updated].[Merchant Name],10) AS [Merchant Name],
Count(Select Distinct [Member Number] From [tbl_ROL150_Updated] AS Total_Members),
Count(tbl_ROL150_Updated.[ROL Case #]) AS [CountOfROL Case #],
Sum(tbl_ROL150_Updated.Amount) AS SumOfAmount

FROM tbl_ROL150_Updated

WHERE (((tbl_ROL150_Updated.Disposition)="PC") AND ((tbl_ROL150_Updated.[Entry Date/Time]) Between [Start Date] And [End Date]))

GROUP BY Left([tbl_ROL150_Updated].[Merchant Name],10);

 

[Micron]

I would copy the subquery sql to a new query and test it to see why it is returning more than one record. It could be that you need DISTINCT ROW, or there are dupes of [Member Number] (which should be an issue) or you need to add the TOP predicate. I admit that I struggle with subqueries and this is where go for info

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[james_lankford]

try

sum ( Select count ( select Distinct [Member Number] From [tbl_ROL150_Updated] ) ) AS Total_Members,