Counting records in a query

[OssieMac]

I have a query created with the query builder that has the Unique values
properties set to Yes. I would like to be able to add a virtual field to the
query that will return the actual count of each of these records in the
database.

Example:
If Unique values is set to No then following output with records repeated:

This Person
This Person
This Person
This Person
Another Person
Another Person

With the Unique values set to Yes the following output with only one line
each for identical output.
This Person
Another Person

I want an output like the following that counts the number of times each
record appears in the database:
This Person 4
Another Person 2

Is this possible with a query and how do I add it to the query builder?

 

[Marshall Barton]

I have a query created with the query builder that has the Unique values
properties set to Yes. I would like to be able to add a virtual field to the
query that will return the actual count of each of these records in the
database.

Example:
If Unique values is set to No then following output with records repeated:

This Person
This Person
This Person
This Person
Another Person
Another Person

With the Unique values set to Yes the following output with only one line
each for identical output.
This Person
Another Person

I want an output like the following that counts the number of times each
record appears in the database:
This Person 4
Another Person 2

SELECT personfield, Count(*) As PersonCount
FROM thetable
GROUP BY personfield

 

[OssieMac]

Hi Marshall,

I thank you for your reply. However, I am still in the dark. I need
instructions on how to enter this into the query builder please.

 

[OssieMac]

Hi again Marshall,

I have got it now. I realize that I have to drag the field to be counted
into the matrix twice, then select menu item View -> Totals (or the Sum
button) and set the 2nd occurrence of the field to Count.

Works like a charm.

Thanks again for taking the time to reply.

 

[Marshall Barton]

I have got it now. I realize that I have to drag the field to be counted
into the matrix twice, then select menu item View -> Totals (or the Sum
button) and set the 2nd occurrence of the field to Count.

The query design grid is sort of limited how you specify
some constructs so SQL is preferred as a way to communicate
a query. You should not have to add the same field twice
and use Count on one of them. There is a difference between
using Count(*) as I suggested and using Count(somefield) as
you are doing. It may not matter in your specific
situation, but don't think they are equivalent. You should
be using:
PersonCount: Count(*)
in the calculated field and Expression in its Total row.

If you start to create a new query but immediately switch to
SQL View, you can then Copy/Paste what I posted over
whatever stuff Access automatically put in there. Then you
can switch back to Design View to see how Access converted
the SQL to the design grid.

 

[Pendragon]

Marshall, interesting note. Can you explain the difference between Count(*)
and Count([fieldname])? Thanks.

 

[Bob Barrows]

Count(*) counts all records that meet the criteria in the WHERE clause.
Count(fieldname) counts only records that meet the criteria in the
WHERE clause and do not contain a null in fieldname.

So with this set of records:

field1 field2
1 1
2 <null>
3 3

select count(*) returns 3
select count(field2) returns 2

Marshall, interesting note. Can you explain the difference between
Count(*) and Count([fieldname])? Thanks.

The query design grid is sort of limited how you specify
some constructs so SQL is preferred as a way to communicate
a query. You should not have to add the same field twice
and use Count on one of them. There is a difference between
using Count(*) as I suggested and using Count(somefield) as
you are doing. It may not matter in your specific
situation, but don't think they are equivalent. You should
be using:
PersonCount: Count(*)
in the calculated field and Expression in its Total row.

If you start to create a new query but immediately switch to
SQL View, you can then Copy/Paste what I posted over
whatever stuff Access automatically put in there. Then you
can switch back to Design View to see how Access converted
the SQL to the design grid.

 

[Marshall Barton]

Marshall, interesting note. Can you explain the difference between Count(*)
and Count([fieldname])?

Count(*) is a special construct that counts records
regardless of the value in any field. It is highly
optimized to make it very efficient.

OTOH, Count(fieldname) counts the non Null values in
somefield so it's possible that the results could be
dramtically different. Count(fieldname) must look at each
record to determine if fieldname is Null or not. If
fieldname is indexed, its performance may be significantly
improved, but not to the level of Count(*).

Note that the non Null aspect of Count(fieldname) is the
same across all of the aggregate functions, which always
ignore Null values. I.e. it is never useful to use
something like Sum(Nz(fieldname,0)) and Avg(Nz(fieldname,0))
will just be wrong.

 

[OssieMac]

Thank you Marshall. I am slowly getting there. I created the query with only
the one field and copied your query in and then built the remainder of my
query afterwards in the matrix and it appears to be working.

 

[Marshall Barton]

Thank you Marshall. I am slowly getting there. I created the query with only
the one field and copied your query in and then built the remainder of my
query afterwards in the matrix and it appears to be working.

Good to hear that you got it working. You just took your
first step on the road to learning SQL ;-)

You didn't need to add a table and select a field. You
could have switched to SQL View immediately.

OTOH, a lot of folks will start out in design view, add the
needed tables, join lines, fields, etc, then switch to SQL
View to make the final tweaks. Saves a lot of typing even
if all the extra [ ], ( ), and table names can be annoying.