Count records in query
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=10822
Printed Date: 30 March 2026 at 12:10pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Count records in query
Posted By: gr8indianbear
Subject: Count records in query
Date Posted: 10 June 2004 at 9:39pm
|
i want to count the number of records in a query without having to run a seperate query. i think i've jsut simply fogotten how to do this. this is the SQL string i had:
the page came up with this error
Line 215 is where the recordset is opened and the SQL Query is run. any ideas? |
Replies:
Posted By: Phat
Date Posted: 10 June 2004 at 9:46pm
|
Think you will need the table name before each field. SELECT Count(ID) AS gf_Count, ID, gfaith.Title, gfaith.Blurb, gfaith.Author, gfaith.gf_Cat FROM gfaith WHERE (((Body) Like '%God%')) ORDER BY gfaith.gf_Cat, gfaith.Beginning; |
Posted By: gr8indianbear
Date Posted: 10 June 2004 at 10:00pm
|
still nothing. i even tried running the query in Access changing the wildcards to '*'. still got the same error message, that
|
Posted By: michael
Date Posted: 10 June 2004 at 10:07pm
|
No, the problem is that you are using an aggregate function 'Count' and do not use aggregate function, or group by for the rest of the fields. Theoretically the following query would work SELECT Count(gf_Cat.ID) AS gf_Count, gf_Cat.Title, gf_Cat.Blurb, gf_Cat.Authorgf_Cat FROM gf_Cat GROUP BY gf_Cat.Title, gf_Cat.Blurb, gf_Cat.Authorgf_Cat, gf_Cat.Body HAVING (((gf_Cat.Body) Like '%god%')); thus if a title, blurb or author are different they get their own row and so are not counted as id. Basically saying the query, the way you have it makes not much sense, unless you try to accomplish something different, for example just counting all the records in the first column, no matter what's the criteria, you could use something like SELECT (Select Count(id) from gf_Cat) AS gf_Count, gf_Cat.Title, gf_Cat.Blurb, gf_Cat.Authorgf_Cat FROM gf_Cat WHERE (((gf_Cat.Body) Like '%god%')); ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: gr8indianbear
Date Posted: 10 June 2004 at 11:26pm
|
ok, i got it changing what michael had from
to
the table name was gfatih, and gf_Cat is a field. i made it work to my purposes by nesting a second query into the frist query. i would have used RecordCount, but that only returned a result of -1 which means it was unable to count the recordset. but this method works |
Posted By: michael
Date Posted: 11 June 2004 at 8:59am
|
Realize that subqueries in Access pay a high penalty in performance. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: dpyers
Date Posted: 11 June 2004 at 12:28pm
|
As michael noted, nested/sub queries can blow your performance, but the extent depends upon your db structure. You may want to use a couple of timers to check the performance differences between the above query, and running a separate query and just checking the rs.RecordCount property. Also, the type of rs you create will impact performance. In general, a forward_only rs will be faster to create, but should be suitable for getting a record count. ------------- 
Lead me not into temptation... I know the short cut, follow me. |