count distinct records (1 Viewer)

nockam

New member
Local time
Today, 07:05
Joined
Sep 25, 2011
Messages
3
I need some help with something that should be very basic but i cannot get it to work in MS Access 2007 despite lots of researching. Can someone please help me adapt the query below to count the number of distinct or unique "extensions"?


The table has 20+ columns but I only want to see 3 + a count column. I need the group by because the same extension could be listed multiple times with the same loc_ID, ROW_DATE and STARTTIME



SELECT root_hagent.loc_id, root_hagent.row_date, root_hagent.starttime, Count(root_hagent.extension) AS CountOfextension
FROM root_hagent
GROUP BY root_hagent.loc_id, root_hagent.row_date, root_hagent.starttime
HAVING (((root_hagent.row_date)>Now()-1));
 

nockam

New member
Local time
Today, 07:05
Joined
Sep 25, 2011
Messages
3
Have a look into the DISTINCT keyword.


That is exactly my frustration. Access doesnt seem to support distint and count for the same column.

I do this all the time using MS SQL server 2000, 2005, and 2008 but I have not been able to find a way to do it in Microsoft Access yet. I would do it the way I normally do but I am pulling the data through an informix database and MS Access typically works pretty well for me when referencing Informix.

Any suggestions?
 

plog

Banishment Pending
Local time
Today, 09:05
Joined
May 11, 2011
Messages
11,611
Post some example data from your table, what you expect your query to return and what it actually is returning.
 

nockam

New member
Local time
Today, 07:05
Joined
Sep 25, 2011
Messages
3
Post some example data from your table, what you expect your query to return and what it actually is returning.


Table (I am leaving out several other columns in the table, these are not duplicate record)

loc_id, row_date, starttime, extension


1 - 1/1/2011 - 0000 - 12
1 - 1/1/2011 - 0000 - 12
1 - 1/1/2011 - 0000 - 13
1 - 1/1/2011 - 0000 - 13
1 - 1/1/2011 - 0030 - 12
1 - 1/1/2011 - 0030 - 12
1 - 1/1/2011 - 0030 - 13
1 - 1/1/2011 - 0030 - 13
1 - 1/1/2011 - 0045 - 12
1 - 1/1/2011 - 0045 - 12
1 - 1/1/2011 - 0045 - 13
1 - 1/1/2011 - 0045 - 14
1 - 1/1/2011 - 0045 - 14
1 - 1/1/2011 - 0045 - 15

Desired result (count of distinct extensions used grouped by the first 3 columns)

1 - 1/1/2011 - 0000 - 2
1 - 1/1/2011 - 0030 - 2
1 - 1/1/2011 - 0045 - 4
 

vbaInet

AWF VIP
Local time
Today, 14:05
Joined
Jan 22, 2010
Messages
26,374
That is exactly my frustration. Access doesnt seem to support distint and count for the same column.
It actually does support DISTINCT and another one called DISTINCTROW.

If you give us what plog requested we will be able to help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:05
Joined
Jan 23, 2006
Messages
15,362
Use 2 queries

query1

Select distinct YourField as MyFld from yourTable

query2

select count(MyFld) from query1
 

vbaInet

AWF VIP
Local time
Today, 14:05
Joined
Jan 22, 2010
Messages
26,374
Desired result (count of distinct extensions used grouped by the first 3 columns)

1 - 1/1/2011 - 0000 - 2
1 - 1/1/2011 - 0030 - 2
1 - 1/1/2011 - 0045 - 4
The count column of your desired results doesn't tally with your raw data. Can you give the correct figures.
 

plog

Banishment Pending
Local time
Today, 09:05
Joined
May 11, 2011
Messages
11,611
Thanks for the data, and that helps me see what you are doing. You are going to need to use a sub-query to make your rows unique and then another aggregate query to count them. This is the SQL you need:

Code:
SELECT distinct_extensions.locid, distinct_extensions.row_date, distinct_extensions.starttime, Count(distinct_extensions.extension) AS distinct_extensions
FROM (SELECT root_hagent.locid, root_hagent.row_date, root_hagent.starttime, root_hagent.extension
FROM root_hagent
GROUP BY root_hagent.locid, root_hagent.row_date, root_hagent.starttime, root_hagent.extension) AS distinct_extensions 
GROUP BY distinct_extensions.locid, distinct_extensions.row_date, distinct_extensions.starttime;
 

Users who are viewing this thread

Top Bottom