If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting records in a query
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? -- Regards, OssieMac |
#2
|
|||
|
|||
Counting records in a query
OssieMac wrote:
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 -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Counting records in a query
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. -- Regards, OssieMac |
#4
|
|||
|
|||
Counting records in a query
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. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac |
#5
|
|||
|
|||
Counting records in a query
OssieMac wrote:
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. -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Counting records in a query
Marshall, interesting note. Can you explain the difference between Count(*)
and Count([fieldname])? Thanks. "Marshall Barton" wrote: OssieMac wrote: 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. -- Marsh MVP [MS Access] . |
#7
|
|||
|
|||
Counting records in a query
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 Pendragon wrote: Marshall, interesting note. Can you explain the difference between Count(*) and Count([fieldname])? Thanks. "Marshall Barton" wrote: OssieMac wrote: 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. -- Marsh MVP [MS Access] . -- HTH, Bob Barrows |
#8
|
|||
|
|||
Counting records in a query
Pendragon wrote:
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. -- Marsh MVP [MS Access] |
#9
|
|||
|
|||
Counting records in a query
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. -- Regards, OssieMac |
#10
|
|||
|
|||
Counting records in a query
OssieMac wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|