The Count function JonWayn 12/18/2008 7:06:02 PM 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

Re: The Count function "enigma" <enigma_epiphany[ at ]hotmail.com> 12/20/2008 5:44:49 AM 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" <JonWayn[ at ]discussions.microsoft.com> wrote in message news:4C478C0D-BD32-4067-889C-00DE7ADBCDDB[ at ]microsoft.com... [Quoted Text] > 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

Re: The Count function JonWayn 12/20/2008 12:15:01 PM 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" wrote: [Quoted Text] > 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" <JonWayn[ at ]discussions.microsoft.com> wrote in message > news:4C478C0D-BD32-4067-889C-00DE7ADBCDDB[ at ]microsoft.com... > > 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 >

Re: The Count function "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/20/2008 1:05:12 PM 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 http://I.Am/DougSteele (no private e-mails, please) "JonWayn" <JonWayn[ at ]discussions.microsoft.com> wrote in message news:378BEF03-4036-44A1-95D3-B5D9B5E6B067[ at ]microsoft.com... [Quoted Text] > 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" wrote: > >> 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" <JonWayn[ at ]discussions.microsoft.com> wrote in message >> news:4C478C0D-BD32-4067-889C-00DE7ADBCDDB[ at ]microsoft.com... >> > 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 >>

Re: The Count function JonWayn 12/20/2008 4:10:00 PM Thank you enigma and Doug. Couldnt be clearer "Douglas J. Steele" wrote: [Quoted Text] > 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 > http://I.Am/DougSteele > (no private e-mails, please) > > > "JonWayn" <JonWayn[ at ]discussions.microsoft.com> wrote in message > news:378BEF03-4036-44A1-95D3-B5D9B5E6B067[ at ]microsoft.com... > > 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" wrote: > > > >> 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" <JonWayn[ at ]discussions.microsoft.com> wrote in message > >> news:4C478C0D-BD32-4067-889C-00DE7ADBCDDB[ at ]microsoft.com... > >> > 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 > >> > > >

Re: The Count function "werner" <1werner1[ at ]unitymedia.de> 12/21/2008 5:42:48 AM "JonWayn" <JonWayn[ at ]discussions.microsoft.com> schrieb im Newsbeitrag news:4C478C0D-BD32-4067-889C-00DE7ADBCDDB[ at ]microsoft.com... [Quoted Text] > 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