Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2018, 07:56 AM   #1
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 402
Thanks: 148
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Save query count to variable

How can I save the record count from query results without rerunning a count query?


Thanks.

sumdumgai is offline   Reply With Quote
Old 12-05-2018, 08:18 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Save query count to variable

VariableName = DCount("*", "QueryName")

though that will run the query behind the scenes.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
sumdumgai (12-05-2018)
Old 12-05-2018, 09:52 AM   #3
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 402
Thanks: 148
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Save query count to variable

'Behind the scenes'. Might that add to the query response time; i.e., to the time it takes for query results to be returned?

sumdumgai is offline   Reply With Quote
Old 12-05-2018, 10:02 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Save query count to variable

Well, it won't add to the time it takes the query to run. I'm saying that running the DCount() will run the query. If the query takes 2 seconds to run, the DCount() will likely take 2 seconds to return the result. I don't know of any way to know how many records a query returns without running the query.

Perhaps if you describe your situation and what you're trying to accomplish overall, we can come up with an effective solution.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-05-2018, 10:32 AM   #5
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 402
Thanks: 148
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Save query count to variable

I've been talking with a couple of other people on this forum to get help on improving query response time. That problem has been solved. I thought it might be beneficial to display the returning record count. I was hoping there was a way for the query to give record count as soon as the search results were available; i.e., without having to re-query. Response time is critical.
sumdumgai is offline   Reply With Quote
Old 12-05-2018, 11:06 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Save query count to variable

if you open it as a recordset, you need to movelast before you will get a valid recordcount - but at least you are not running it twice.

The access tabledef has a recordcount property which returns the number of records in a table (but only works on local tables)

also in VBA you have the recordsaffected property which can be applied after they have executed (using .execute) to return how many were deleted/appended/updated
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
sumdumgai (12-05-2018)
Old 12-05-2018, 11:46 AM   #7
sumdumgai
Newly Registered User
 
Join Date: Jul 2007
Posts: 402
Thanks: 148
Thanked 1 Time in 1 Post
sumdumgai is on a distinguished road
Re: Save query count to variable

Doing 'DoCmd' select query so 'recordsaffected' won't work. Thanks anyway.

sumdumgai is offline   Reply With Quote
Old 12-05-2018, 12:02 PM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,784
Thanks: 34
Thanked 538 Times in 511 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Save query count to variable

I would not think you would want to return the recordset of all records and count them. I would think that would be slow. You would want to return a single record that is the count of the records in the query.

set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)
x = rs!TotalCount

I doubt that is any different than PBaldy's solution. In the old days the D aggregates functions were slow and you could get better performance rolling your own. Not certain anymore.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
sumdumgai (12-05-2018)
Old 12-05-2018, 12:26 PM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Save query count to variable

Quote:
Doing 'DoCmd' select query so 'recordsaffected' won't work
won't work with docmd anyway. And I did say it only works with action queries (using execute). You haven't specifically said what type of queries you are running so I was just trying to cover all the bases
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 12-05-2018, 01:09 PM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Save query count to variable

As I've done a lot of speed tests recently I thought I'd run a comparison of DCount vs recordset.count

Unfortunately, using this code causes error 3141: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Code:
set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)
x = rs!TotalCount
I tried various variations but couldn't get it to work.
So I replaced it with
Code:
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenDynaset)
    rst.MoveLast
    rst.MoveFirst
    N = rst.RecordCount
I ran the comparison several times on a query based on a large table of 2.6 million records. The average times were as follows:

Quote:
DCount: N = 2616838 ; Time taken 0.05 seconds
RecordsetCount: N = 2616838 ; Time taken 0.22 seconds
Both are fast but DCount took less than 25% of the time using a recordset.
Whether using dbOpenForwardOnly would be much faster I can't tell as it defeated my efforts to solve the error
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 12-05-2018 at 01:20 PM.
isladogs is offline   Reply With Quote
Old 12-05-2018, 01:25 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Save query count to variable

Quote:
Originally Posted by isladogs View Post
[CODE]set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)
Wouldn't it need to be:

select Count(*) as TotalCount from yourQuery
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
MajP (12-05-2018)
Old 12-05-2018, 01:45 PM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Save query count to variable

Thanks Paul

I thought I'd tried that in my various attempts but obviously not!
That brought the recordset count time down as you would expect

On running each once the times were almost identical 0.05 & 0.06 seconds
So I run each 100 times in a loop and measured the total time:

Number of loops 100; DCount : N = 2616838 ; Time taken 5.28 seconds (range 3.9 => 5.67s)
Number of loops 100; RecordsetCount: N = 2616838 ; Time taken 2.57 seconds (range 2.54 => 4.55s)

As you can see this time the recordset approach typically took around half the time
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 12-05-2018, 02:46 PM   #13
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,784
Thanks: 34
Thanked 538 Times in 511 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Save query count to variable

Quote:
select Count(*) as TotalCount from yourQuery
That is what I meant. Thanks for the catch.
MajP is offline   Reply With Quote
Old 12-05-2018, 02:57 PM   #14
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,601
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Save query count to variable

Was the DCount applied to a query that returned an indexed field?

DCounts against tables with indexed fields are vastly faster than those without indexes. Wondering how it affects it if a query is in between.
Galaxiom is offline   Reply With Quote
Old 12-05-2018, 03:01 PM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Save query count to variable

I ran both tests on queries as well as tables
The queries did include an indexed field.

Just for info I redid both sets of tests on the PK field itself.

That had the effect of speeding up the DCount & slowing down the recordset methods with typical results as follows:
Quote:
Number of loops 100; DCount : N = 2616838 ; Time taken 3.66 seconds
Number of loops 100; RecordsetCount: N = 2616838 ; Time taken 3.77 seconds
Not a lot in it but DCount slightly faster each time I tested

Next I ran the count on an unindexed field which was inevitably going to be much slower. In fact it was so slow that running each 100 times would have taken a ridiculous time.
The following is for 10 loops
Number of loops 10; DCount : N = 2175016 ; Time taken 213.32 seconds
Number of loops 10; RecordsetCount: N = 2175016 ; Time taken 214.65 seconds

So multiplying up by 10 for a likely 100 loop outcome, both times would be around 2130 seconds instead of around 3.7 seconds! Almost 600 times larger!!!!!

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 12-05-2018 at 03:21 PM.
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
Galaxiom (12-05-2018), sumdumgai (12-06-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Save Variable Name report in Variable Name Folder setis Modules & VBA 10 10-04-2017 05:34 AM
get value from combo box, save in variable, pass it to query viwaltzer Forms 5 09-13-2012 12:27 PM
What query return save to variable jasmin_m Modules & VBA 1 01-12-2010 08:57 AM
Save as variable name smilediamond Modules & VBA 16 05-07-2009 06:24 AM
capturing a count query in a variable? pdbowling Modules & VBA 6 03-21-2003 08:57 AM




All times are GMT -8. The time now is 06:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World