Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-25-2011, 10:59 AM   #1
nockam
Newly Registered User
 
Join Date: Sep 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
nockam is on a distinguished road
count distinct records

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 is offline   Reply With Quote
Old 09-25-2011, 11:08 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: count distinct records

Have a look into the DISTINCT keyword.
vbaInet is offline   Reply With Quote
Old 09-25-2011, 11:26 AM   #3
nockam
Newly Registered User
 
Join Date: Sep 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
nockam is on a distinguished road
Re: count distinct records

Quote:
Originally Posted by vbaInet View Post
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?

nockam is offline   Reply With Quote
Old 09-25-2011, 11:35 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,351
Thanks: 10
Thanked 2,272 Times in 2,224 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: count distinct records

Post some example data from your table, what you expect your query to return and what it actually is returning.
plog is offline   Reply With Quote
Old 09-25-2011, 11:48 AM   #5
nockam
Newly Registered User
 
Join Date: Sep 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
nockam is on a distinguished road
Re: count distinct records

Quote:
Originally Posted by plog View Post
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
nockam is offline   Reply With Quote
Old 09-25-2011, 11:48 AM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: count distinct records

Quote:
Originally Posted by nockam View Post
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.
vbaInet is offline   Reply With Quote
Old 09-25-2011, 12:14 PM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,142
Thanks: 81
Thanked 2,010 Times in 1,958 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: count distinct records

Use 2 queries

query1

Select distinct YourField as MyFld from yourTable

query2

select count(MyFld) from query1

jdraw is offline   Reply With Quote
Old 09-25-2011, 01:10 PM   #8
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: count distinct records

Quote:
Originally Posted by nockam View Post
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.
vbaInet is offline   Reply With Quote
Old 09-25-2011, 06:57 PM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,351
Thanks: 10
Thanked 2,272 Times in 2,224 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: count distinct records

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;

plog is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Distinct count of records Tep Modules & VBA 5 02-22-2011 03:52 AM
Count of distinct wgma Queries 3 01-28-2009 02:46 PM
Count/SUM Distinct Help kidrobot Queries 3 07-06-2007 07:06 AM
need distinct records of whle table but distinct on one field ukdata Queries 1 09-16-2006 11:22 PM
Count Distinct SASHA_D Queries 2 07-29-2005 01:09 AM




All times are GMT -8. The time now is 10:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World