Unique record count

[W Hannon Jr]

I need to get a count of the number of unique record count(s) for ID over
specific time periods

ID P C_VRM CREATE_DATE
1 X Y 9/20/2005
2 X Y 9/20/2005
2 X Y 9/20/2005
3 X Y 9/20/2005
4 X Y 9/20/2005
3 X Y 9/21/2005

SELECT DISTINCTROW Count(Table_P.ID) AS CountOfID
FROM Table_L INNER JOIN Table_P ON Table_L.Com = Table_P.Com
WHERE (((Table_P.P)="X") AND ((Table_P.C_VRM)="Y") AND
((IIf((([CREATE_DATE]>=#9/20/2005#) And
([CREATE_DATE]<#9/22/2005#)),1,0))=1));

Returns (which is not the number of unique records): 6

Unique record count should be: 4
(1,2,3,4)

 

[giorgio rancati]

Hi,
The Count(Distinct [FieldName]) does not exists in Access.
You can make in this manner
----
SELECT COUNT(P_ID)
FROM (SELECT DISTINC Table_P.ID AS P_ID
FROM Table_L INNER JOIN Table_P ON Table_L.Com = Table_P.Com
WHERE Table_P.P="X" AND Table_P.C_VRM="Y" AND
[CREATE_DATE] BETWEEN #9/20/2005# And #9/22/2005#) AS DRV_TBL

 

[Duane Hookom]

One little typo in Giorgio's otherwise great reply:
He missed the second "T" in DISTINCT.

--
Duane Hookom
MS Access MVP

Hi,
The Count(Distinct [FieldName]) does not exists in Access.
You can make in this manner
----
SELECT COUNT(P_ID)
FROM (SELECT DISTINC Table_P.ID AS P_ID
FROM Table_L INNER JOIN Table_P ON Table_L.Com = Table_P.Com
WHERE Table_P.P="X" AND Table_P.C_VRM="Y" AND
[CREATE_DATE] BETWEEN #9/20/2005# And #9/22/2005#) AS DRV_TBL
----

Bye
Giorgio

I need to get a count of the number of unique record count(s) for ID over
specific time periods

ID P C_VRM CREATE_DATE
1 X Y 9/20/2005
2 X Y 9/20/2005
2 X Y 9/20/2005
3 X Y 9/20/2005
4 X Y 9/20/2005
3 X Y 9/21/2005

SELECT DISTINCTROW Count(Table_P.ID) AS CountOfID
FROM Table_L INNER JOIN Table_P ON Table_L.Com = Table_P.Com
WHERE (((Table_P.P)="X") AND ((Table_P.C_VRM)="Y") AND
((IIf((([CREATE_DATE]>=#9/20/2005#) And
([CREATE_DATE]<#9/22/2005#)),1,0))=1));

Returns (which is not the number of unique records): 6

Unique record count should be: 4
(1,2,3,4)

 

[giorgio rancati]

ops...

Thanks Duane

Giorgio