Group & Count

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I have 3 fields, CompanyID, Name & Date where the ID and
name will always be the same but the date will change. If
I group and then count on the ID in a query, I get the
total number of times the name is held. However, if I
introduce the date (so that I can use it as a parameter)
the count is then split (because there are a number of
different dates for each name). It is as if the count is
now on the uniqueness of each record rather than the ID
alone. Is there a way around this, the end result is a
report that shows how many times an individual appeares in
the database and not how many times an individual appeares
where the whole record is the same? Many thanks
 
In a query, do a "totals" query (hit the sigma sign on the toolbar in design
mode). Then specify "group by" in the "Total" row in the name field and
"count" in the "Total" row in the ID field. That will give you the count of
how many times each name appears.

If you want to know how many times the name appears for a given date, set
the "Total" row of the date field to "Where" and in the criteria row put in
the date that you're looking for. Then you'll see how many times each name
appeared for that date only.
 
hi,
if you have group by on the date, that is what is doing it.
count the date as well. that should get you what you want.
group by on the ID, count everything else.
and be carefull when you use aggragate functions such as
count, sum, ect with group by. group by can through off
the results big time.
 
Back
Top