The Count function

  • Thread starter Thread starter JonWayn
  • Start date Start date
J

JonWayn

Got a question on how Count is used. If you create a group by query with more
than 1 GROUP BY fields and a Count field, does it make a difference to the
results if you entered the Count formula as Count(*) or Count(FieldName). Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this query?

Thanks for any input
 
Yes... from bol

The Count function does not count records that have Null (Null: A value you
can enter in a field or use in expressions or queries to indicate missing or
unknown data. In Visual Basic, the Null keyword indicates a Null value. Some
fields, such as primary key fields, can't contain Null.) fields unless expr
is the asterisk (*) wildcard character (wildcard characters: Characters used
in queries and expressions to include all records, file names, or other
items that begin with specific characters or that match a certain pattern.).
If you use an asterisk, Count calculates the total number of records,
including those that contain Null fields. Count(*) is considerably faster
than Count([Column Name]).
 
so its faster to ude the asterisk as opposed to using a field name. Ok, the
question is, irrespective of efficiency: do they both return the same
resultant recordset?

enigma said:
Yes... from bol

The Count function does not count records that have Null (Null: A value you
can enter in a field or use in expressions or queries to indicate missing or
unknown data. In Visual Basic, the Null keyword indicates a Null value. Some
fields, such as primary key fields, can't contain Null.) fields unless expr
is the asterisk (*) wildcard character (wildcard characters: Characters used
in queries and expressions to include all records, file names, or other
items that begin with specific characters or that match a certain pattern.).
If you use an asterisk, Count calculates the total number of records,
including those that contain Null fields. Count(*) is considerably faster
than Count([Column Name]).



JonWayn said:
Got a question on how Count is used. If you create a group by query with
more
than 1 GROUP BY fields and a Count field, does it make a difference to the
results if you entered the Count formula as Count(*) or Count(FieldName).
Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this
query?

Thanks for any input
Click to expand...
Click to expand...
 
They will not necessarily return the same recordset.

As enigma told you, "The Count function does not count records that have
Null". That means that if you have records for which FirstName is Null, the
count will be different.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JonWayn said:
so its faster to ude the asterisk as opposed to using a field name. Ok,
the
question is, irrespective of efficiency: do they both return the same
resultant recordset?

enigma said:
Yes... from bol

The Count function does not count records that have Null (Null: A value
you
can enter in a field or use in expressions or queries to indicate missing
or
unknown data. In Visual Basic, the Null keyword indicates a Null value.
Some
fields, such as primary key fields, can't contain Null.) fields unless
expr
is the asterisk (*) wildcard character (wildcard characters: Characters
used
in queries and expressions to include all records, file names, or other
items that begin with specific characters or that match a certain
pattern.).
If you use an asterisk, Count calculates the total number of records,
including those that contain Null fields. Count(*) is considerably faster
than Count([Column Name]).



JonWayn said:
Got a question on how Count is used. If you create a group by query
with
more
than 1 GROUP BY fields and a Count field, does it make a difference to
the
results if you entered the Count formula as Count(*) or
Count(FieldName).
Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this
query?

Thanks for any input
Click to expand...
Click to expand...
Click to expand...
 
Thank you enigma and Doug. Couldnt be clearer

Douglas J. Steele said:
They will not necessarily return the same recordset.

As enigma told you, "The Count function does not count records that have
Null". That means that if you have records for which FirstName is Null, the
count will be different.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JonWayn said:
so its faster to ude the asterisk as opposed to using a field name. Ok,
the
question is, irrespective of efficiency: do they both return the same
resultant recordset?

enigma said:
Yes... from bol

The Count function does not count records that have Null (Null: A value
you
can enter in a field or use in expressions or queries to indicate missing
or
unknown data. In Visual Basic, the Null keyword indicates a Null value.
Some
fields, such as primary key fields, can't contain Null.) fields unless
expr
is the asterisk (*) wildcard character (wildcard characters: Characters
used
in queries and expressions to include all records, file names, or other
items that begin with specific characters or that match a certain
pattern.).
If you use an asterisk, Count calculates the total number of records,
including those that contain Null fields. Count(*) is considerably faster
than Count([Column Name]).



Got a question on how Count is used. If you create a group by query
with
more
than 1 GROUP BY fields and a Count field, does it make a difference to
the
results if you entered the Count formula as Count(*) or
Count(FieldName).
Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this
query?

Thanks for any input
Click to expand...
Click to expand...
Click to expand...
 
JonWayn said:
Got a question on how Count is used. If you create a group by query with
more
than 1 GROUP BY fields and a Count field, does it make a difference to the
results if you entered the Count formula as Count(*) or Count(FieldName).
Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this
query?

Thanks for any input
Click to expand...
 
JonWayn said:
so its faster to ude the asterisk as opposed to using a field name. Ok,
the
question is, irrespective of efficiency: do they both return the same
resultant recordset?

enigma said:
Yes... from bol

The Count function does not count records that have Null (Null: A value
you
can enter in a field or use in expressions or queries to indicate missing
or
unknown data. In Visual Basic, the Null keyword indicates a Null value.
Some
fields, such as primary key fields, can't contain Null.) fields unless
expr
is the asterisk (*) wildcard character (wildcard characters: Characters
used
in queries and expressions to include all records, file names, or other
items that begin with specific characters or that match a certain
pattern.).
If you use an asterisk, Count calculates the total number of records,
including those that contain Null fields. Count(*) is considerably faster
than Count([Column Name]).



JonWayn said:
Got a question on how Count is used. If you create a group by query
with
more
than 1 GROUP BY fields and a Count field, does it make a difference to
the
results if you entered the Count formula as Count(*) or
Count(FieldName).
Say
for instance the query is:
SELECT FirstName, LastName, Cnt:Count(*) FROM MyTbl GROUP BY FirstName,
LastName

as opposed to :
SELECT FirstName, LastName, Cnt:Count(FirstName) FROM MyTbl GROUP BY
FirstName, LastName

is there any difference at all with the resultant recordset from this
query?

Thanks for any input
Click to expand...
Click to expand...
Click to expand...
 
Back
Top