Multiple Counts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know aggrate counts usually work on one field. Is there a way to list a
individual values in a field and how many times each occur in a report or
something?

Theres an Employer field. Maybe ten people put down, AT&T, 20 people Merck, but
there are hundreds of employrs. How do I show a query or report that lists
distinct employers and next to it the number of times they appear? Anyone know?

(e-mail address removed)
 
Dear Spam etc:

Do you mean:

SELECT Employer, COUNT(*) AS EmployeeCount
FROM YourTable
GROUP BY Employer

If not, perhaps you can tell us what you want that is different from
this.

Your comment about aggregate functions working on one field may be the
issue. The COUNT() function doesn't work on a field at all. Instead
it just counts rows. You could count any field you like and get the
same result, because any field you choose appears once in every row.
To avoid confusion on this fact, I perefer to always COUNT(*) to
emphasize the fact that I'm counting rows, not some field. This helps
me avoid confusing myself or others.

I know aggrate counts usually work on one field. Is there a way to list a
individual values in a field and how many times each occur in a report or
something?

Theres an Employer field. Maybe ten people put down, AT&T, 20 people Merck, but
there are hundreds of employrs. How do I show a query or report that lists
distinct employers and next to it the number of times they appear? Anyone know?

(e-mail address removed)
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
I know the count function. What I want is like an aggragate. But I want to
count individual instances of each unique occurance in a field either through a
query or report. Original DB looks something like

Name Emp SS#
Jones ATT 123-4443
Palis GE 324-4456
Smith Merck 234-9999
Tolson ATT 345-9998
Todd GE 345-4444
Baker GE 455-666


What I want to see is a list in report or query form that says

ATT 2
Merck 1
GE 3

In other words, not count(*) which will tell me how many records there are.

I want a list of companies and the number of times each occurs in the table.
Can it be done?

I did a report that got a list of individual companies but I'm not sure how to
toal the occurances of that company. Or how to make a query that will just show
me a distinct list of companies in a tally and how often they occur in the
table like

ATT 2
Merck 1
GE 3

Some kind of lookup function?

(e-mail address removed)
 
What you are asking for is a count function
Based on your sample data
Select Emp, Count(Emp)
from SampleTable
Group by Emp

Will return the result exactly as you have described it.
There is such a thing as Distinct Count, but I believe it is not applicable
here
HS
 
I confess, I tricked you. These two queries are identical:
Select emp, count(*) from home group by emp
Select emp, count(emp) from home group by emp

Tom's suggestion is no different from mine, except in terms of approach and
personal preference.

Regards
HS
 
Well anything that works. Still trying to figure out how to clear a combobox on
an Access form. An unbound one.
..clear doesn't work the way it does with other VBA object models like Word or
Excel.
(e-mail address removed)
 
what do you mean by clear? just clear the selected value?
me.comboboxname.value = ""

remove the dropsoem list?
me.comboboxname.rowsource = ""

HS
 
No, that clears the visible value. It doesn't empty the items. Then again I
think part of the problem is Access seems to sometimes additems run through
code into its property windows which I have had to clear. This is confusing, as
it never does something like that in Word or Excel. But then Access automates a
lot of things I'm used to doing with code. Removing the value list so I can
populate a combo with available queries through ADO for example. Unless there
is an automated way to do this. It seems to automate hooking up fields. Not
linking comboboxes with a list of tables queries.

what do you mean by clear? just clear the selected value?
me.comboboxname.value = ""
(e-mail address removed)
 
Back
Top