The Count function

[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

 

[enigma]

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]

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?

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

 

[Douglas J. Steele]

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)

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?

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

 

[JonWayn]

Thank you enigma and Doug. Couldnt be clearer

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)

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?

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

 

[werner]

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

 

[weiwqeowqewqoeqweowqoeowqeowqeowqeoowqeowqeowqoewq]

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?

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