How do I count how many times each distinct entry in a column appears?

[keith]

I am trying to get a exact count of different distinct entries in an
Access column. At first, I was trying to work with three columns, but
I've narrowed it down to one to simplify it. I've searched Google
Groups for Distinct Count and Count, the Microsoft Help file (which
apparently has bad links in Office 2003), and looked at other files,
but I can't find the answer. The closest I've been able to get is to
create a query to find the distinct entries, and to make a total count
of those (50000+), but I have a list of 200000+ entries, and I need to
know how many times those distinct items appears in the main list.

For example, my column looks like this:
First_Description
Description A
Description B
Description C
Description A
Description D
Description A
Description B

What I'd ultimately like to have is either a report, or a table that I
can export to Excel (since it's less than 65000 distinct rows) that
looks like this:
First_Description # of occurances
Description A 3
Description B 2
Description C 1
Description D 1

I know this may sound simple, but for the life of me, I can't figure it
out. The best I've done is this:
SELECT Count([First Descriptions].First_Description) AS
CountOfFirst_Description1, [First Descriptions].First_Description
FROM [Select Distinct], [First Descriptions]
GROUP BY [First Descriptions].First_Description
ORDER BY Count([First Descriptions].First_Description), [First
Descriptions].First_Description;

But, this query ran for over 5 hours last night, and it never came up.
I'm trying it again now, but I know it will be slow.
Thanks for all your help! It is appreciated!
Keith

[Tom van Stiphout]

On 21 Jan 2005 06:06:41 -0800, ke***@s116160440.onlinehome.us wrote:

Create a new query, and select your table.
Drag the Description field to the grid TWICE.
Leave the first one at Group By.
Set the second one to Count.

-Tom.

I am trying to get a exact count of different distinct entries in an
Access column. At first, I was trying to work with three columns, but
I've narrowed it down to one to simplify it. I've searched Google
Groups for Distinct Count and Count, the Microsoft Help file (which
apparently has bad links in Office 2003), and looked at other files,
but I can't find the answer. The closest I've been able to get is to
create a query to find the distinct entries, and to make a total count
of those (50000+), but I have a list of 200000+ entries, and I need to
know how many times those distinct items appears in the main list.

For example, my column looks like this:
First_Description
Description A
Description B
Description C
Description A
Description D
Description A
Description B

What I'd ultimately like to have is either a report, or a table that I
can export to Excel (since it's less than 65000 distinct rows) that
looks like this:
First_Description # of occurances
Description A 3
Description B 2
Description C 1
Description D 1

I know this may sound simple, but for the life of me, I can't figure it
out. The best I've done is this:
SELECT Count([First Descriptions].First_Description) AS
CountOfFirst_Description1, [First Descriptions].First_Description
FROM [Select Distinct], [First Descriptions]
GROUP BY [First Descriptions].First_Description
ORDER BY Count([First Descriptions].First_Description), [First
Descriptions].First_Description;

But, this query ran for over 5 hours last night, and it never came up.
I'm trying it again now, but I know it will be slow.
Thanks for all your help! It is appreciated!
Keith

[keith]

I'm trying it now (it's the reverse of how I had it). The query has now
run for the last two hours...hopefully it will pop up soon! I'll post
the outcome here.

Thanks!
Keith

[King Ron]

You may wish to add an index to your 'Description' column to speed the
tabulation.

king ron of chi

[keith]

Thanks Ron. I've left the query running since this morning and it's
still not done. I quit it and added the indexing property. Hopefully
this will work.

[Tom van Stiphout]

On 21 Jan 2005 13:11:56 -0800, ke***@s116160440.onlinehome.us wrote:

With an index, this should take less than a minute. Perhaps less than
5 seconds.

-Tom.

Thanks Ron. I've left the query running since this morning and it's
still not done. I quit it and added the indexing property. Hopefully
this will work.

[keith]

Tom van Stiphout wrote:

On 21 Jan 2005 06:06:41 -0800, ke***@s116160440.onlinehome.us wrote:

Create a new query, and select your table.
Drag the Description field to the grid TWICE.
Leave the first one at Group By.
Set the second one to Count.

-Tom.

I left it running all weekend long...it never came up, even after I
indexed it. Is there anything else I could try?

Thanks,
Keith

[rkc]

ke***@s116160440.onlinehome.us wrote:

Tom van Stiphout wrote:

On 21 Jan 2005 06:06:41 -0800, ke***@s116160440.onlinehome.us wrote:

Create a new query, and select your table.
Drag the Description field to the grid TWICE.
Leave the first one at Group By.
Set the second one to Count.

-Tom.

I left it running all weekend long...it never came up, even after I
indexed it. Is there anything else I could try?

Have you considered moving smaller amounts of your data to
a test database until you get the query running correctly?

[keith]

No, but I could try that. Thanks!

[keith]

I cut it down from 200000+ records to 2614. There are now 936 unique
entries, but the script still is not working or it's just taking too
long to process. Finding the distinct entries takes a second or two, so
thanks for the indexing idea...that helps a bit.
Should I try this using VBA, and if so, how do I do that?

[keith]

Ok, the query took about 3 hours with 2614 records. It worked...almost.
This is an example of what I received:

First_Description CountOfFirst_Description1
Emission limitation exceedance report 338612
Emission measurement summary 169306
Emission observation forms 169306
Emission reduction credits annual report 169306

There are obviously not 338612 entries, so I don't know why it's
displaying it as such. If I divide that number reported by 169306, I
get the correct number of times that it displays (I did a few random
checks).

Would anyone know why it's giving me these numbers? Is this the number
of passes the query had to make? It appears that this query will work
on a larger scale, but I don't know how long it will take to calculate.
But, I'll try again...

[David Schofield]

On 24 Jan 2005 05:19:31 -0800, ke***@s116160440.onlinehome.us wrote:

Tom van Stiphout wrote:

On 21 Jan 2005 06:06:41 -0800, ke***@s116160440.onlinehome.us wrote:

Create a new query, and select your table.
Drag the Description field to the grid TWICE.
Leave the first one at Group By.
Set the second one to Count.

-Tom.

I left it running all weekend long...it never came up, even after I
indexed it. Is there anything else I could try?

Thanks,
Keith

Post your SQL and a summary of the table definition.
Are you on a PC with limited memory?
If you group by the first character (left(xxx), 1), the first 5
characters (left(xxx, 5)) etc how long does that take?

David

[keith]

SELECT [First Descriptions].First_Description, Count([First
Descriptions].First_Description) AS CountOfFirst_Description
FROM [First Descriptions], [First Descriptions] AS [First
Descriptions_1]
GROUP BY [First Descriptions].First_Description;

It has 512 MB of RAM, but I do work on it while letting it process.
However, I left it running since Friday with nothing in the background
and it still didn't finish.

I'm not sure I understand how you would group by the characters or the
code necessary to do it. I just have used the "Group By" setting in the
Query screen.

[David Schofield]

On 24 Jan 2005 11:24:16 -0800, ke***@s116160440.onlinehome.us wrote:

SELECT [First Descriptions].First_Description, Count([First
Descriptions].First_Description) AS CountOfFirst_Description
FROM [First Descriptions], [First Descriptions] AS [First
Descriptions_1]
GROUP BY [First Descriptions].First_Description;

It has 512 MB of RAM, but I do work on it while letting it process.
However, I left it running since Friday with nothing in the background
and it still didn't finish.

I'm not sure I understand how you would group by the characters or the
code necessary to do it. I just have used the "Group By" setting in the
Query screen.

Hi
I see.
You didn't follow Tom's instructions correctly I'm afraid, you should
have just dragged down the description field twice from one copy of
the table.
The query you want is

SELECT [First descriptions].[First Description], Count([First
descriptions].[First Description]) AS [CountOfFirst Description]
FROM [First descriptions]
GROUP BY [First descriptions].[First Description];

It is always worth posting the sql for any query you are discussing.

David

[keith]

Thanks David. I tried entering in your query exactly as you have it,
and it popped up two boxes asking for information.

I'll try your method as I left this query running since Monday, and
still no results.

Arrgh..I know the answer is in front of me...I just can't see the
forest.

[keith]

FYI...it finally worked. I did nothing different. I tried pulling down
the description twice (which I could've sworn I did), and all of a
sudden, the query results were there a few hours later.

I don't know if it took so long to do it initially, or if pulling the
description is what did it, but it works, and I am happy.
Thanks to all who helped!!!

[David Schofield]

On 26 Jan 2005 07:53:35 -0800, ke***@s116160440.onlinehome.us wrote:

Thanks David. I tried entering in your query exactly as you have it,
and it popped up two boxes asking for information.

I'll try your method as I left this query running since Monday, and
still no results.

Arrgh..I know the answer is in front of me...I just can't see the
forest.

Keith

If you looked at my query you will see that I referred to your table
as [First Descriptions] and your field as [First Description], while
in your post your field is actually [First_Description] with an
underscore rather than a space. So my query should have been

SELECT [First descriptions].[First_Description],
Count([First descriptions].[First_Description])
AS [CountOfFirst_Description]
FROM [First descriptions]
GROUP BY [First descriptions].[First_Description];

Sorry about that, but I think you should take the time to read up a
bit on using queries.
Your original query, apart from taking 200000 times longer to run (if
that is the number of records) will give answers that are 200000 times
too large.

David

[keith]

I just need to read a bit more about everything, to be honest with you.
I just plug away, then when I need help, I read the help file/Google
Groups/MVPs.org...anywhere I can think of.

Are there any good books that would help a person who knows about
computers, but needs a bit of help with Access? There's tons on Amazon,
but any personal recommendations from this group would help.
Thanks again!
Keith