Counting unique records using SQL

[daievans]

So - I am trying to count unique records in my table (Demographic_detail). It works perfectly when I use the following

SELECT Count(*) AS N
INTO tblTempC
FROM (SELECT DISTINCT Account FROM Demographics_detail)  AS T;

However, I'd like to add a criteria to count unique where my field (Processedfile) = "MHR_XAM20130514-XXX-01.TXT"

I throw this at the SQL wall, rather un athletically I admit as follows

SELECT Count(*) AS N
INTO tblTempC
FROM (SELECT DISTINCT Account FROM Demographics_detail)  AS T
WHERE (T.[Processedfile]="MHR_XAM20130514-XXX-01.TXT");

And, it, and then I, blows up ... :banghead:

 

[plog]

SELECT Count(*) AS N
INTO tblTempC
FROM (SELECT DISTINCT Account FROM Demographics_detail)  AS T
WHERE (T.[Processedfile]="MHR_XAM20130514-XXX-01.TXT");

There's no T.[Processdfile], the only field in T is T.[Account]. You are applying criteria to a field that isn't in your data. Perhaps it will give you the results you want if you remove the WHERE clause from the main query and replace it with one in the sub-query.

 

[daievans]

Thanks plog - took your advice and found another nugget of yours elsewhere on here and finally came up with the following which works!

SELECT D_Alias.Demographics_detail.Processedfile, Count(Demographics_detail.Account) AS UniqueAcc 
INTO tblTempC
FROM (SELECT Demographics_detail.Processedfile, Demographics_detail.Account 
FROM Demographics_detail 
GROUP BY Demographics_detail.Processedfile, Demographics_detail.Account)  AS D_Alias
GROUP BY D_Alias.Processedfile
HAVING (((D_Alias.Processedfile)="MHR_XAM20130514-XXX-02.TXT"));

Decided to introduce the D_Alias as Access provided me with Alphabet Soup ...