473,700 Members | 2,838 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Alternative to domain aggregate functions

Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.

What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.

Option Compare Database
Option Explicit

Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Fie ld)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move Last
mCount = rsFiltered.Reco rdCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move First
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.Perc entPosition
mSum = mSum + rsFiltered.Fiel ds(Field).Value
rsFiltered.Move Next
Wend
End Function

Sep 8 '06 #1
5 8471
Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement

Arno R

"Dean" <no*****@coveya ccounting.comsc hreef in bericht news:11******** *************@i 42g2000cwa.goog legroups.com...
Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.

What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.

Option Compare Database
Option Explicit

Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Fie ld)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move Last
mCount = rsFiltered.Reco rdCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move First
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.Perc entPosition
mSum = mSum + rsFiltered.Fiel ds(Field).Value
rsFiltered.Move Next
Wend
End Function
Sep 8 '06 #2
Arno R wrote:
Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement

Arno R
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...

Sep 9 '06 #3

pi********@hotm ail.com wrote:
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...
Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.

I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.

Sep 9 '06 #4
Dean wrote:
pi********@hotm ail.com wrote:
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...

Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.

I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.
I disagree with your approach though. If you want an efficient way to "look up"
a record open a Recordset that contains only that record rather than opening the
whole domain and then do a FindFirst on the Recordset. If the criteria will
return more than one row and you want t6he first match then use a TOP 1 clause
with an appropriate ORDER BY.

If you want to do a count of a Recordset then use a SQL statement that uses the
Count() function rather that opening the entire domain and then moving to the
end and then using the RecordSet.Count .

It is in creating the most efficient query that you will reap the most benefits.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 9 '06 #5
"Dean" <no*****@coveya ccounting.comwr ote in
news:11******** *************@m 73g2000cwd.goog legroups.com:
Actually, Trevor took a different approach: He creates a query
def each time.
Huh? As far as I can tell his t functions do nothing of the sort.
I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a
replacement for d functions on attached tables, not intended as
replacement all together. Based on these comments, I am assuming
the built-in functions run faster on native Access record sets
rather than linked tables.
If I'm not mistaken, the t functions were indeed created to be
faster than the builtin domain aggregate functions on linked tables.
But I believe that problem was fixed in A2K or later so that the
domain aggregate functions were no longer slower on linked tables,
so Trevo'rs functions no longer had an advantage over them.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 10 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5880
by: Ariel Jakobovits | last post by:
I have a table with 2 primary keys, one is a foreign key, the other is produced by a sequence. I want to SELECT query for one record that has a list of the sequence-produced values for all records with a given foreign-key value, essentially: Create table table1 { fk_id number, seq_id number
6
9991
by: Steven An | last post by:
Howdy, I need to write an update query with multiple aggregate functions. Here is an example: UPDATE t SET t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ), t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y ) FROM dbo.test t
1
1387
by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00 1/1/2004 4000.00 40.00 4040.00 1/2/2004 1000.00 10.00 1010.00 1/2/2204 2000.00 20.00 2020.00 1/3/2004 1500.00 15.00 1515.00 I want my resultant DataTable to show in DataGrid as
2
7045
by: jc | last post by:
Hi. Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write my own variation of a such a function. If I can appreciate how to do this, then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or a more general function like a 10% percentile function with syntax such as PERCENTILE(COLUMN_NAME,25). Regards JC......
10
11929
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to post) *I'm running a total query, of the form
5
3711
by: David Garamond | last post by:
What do people think of adding some more aggregate functions. These are the ones that MySQL has and PG doesn't: - STD/STDDEV - VARIANCE - BIT_OR - BIT_AND - GROUP_CONCAT (for strings, added in MySQL 4.x) Particularly, I find GROUP_CONCAT practical to quickly display 1-many
8
3126
by: jefftyzzer | last post by:
The current issue of "Oracle Magazine" has an article on creating custom aggregate functions, which naturally got me thinking about how to do this in DB2. I found some articles on creating custom aggregate functions in DB2, written close to three years ago by Knut Stolze: http://www-128.ibm.com/developerworks/db2/library/techarticle/0309stolze/0309stolze.html Mr. Stolze says in the first article that "urrently, there is no
0
1586
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
5
6095
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
0
8709
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9202
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7791
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5894
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4395
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4649
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3081
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2371
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.