473,288 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

DSum & DCount -> SQL

I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions. As I am a novice to SQL, any
suggestions would be hugely appreciated. Thanks.

Apr 26 '06 #1
1 5382
If you have a single lookup taking place, e.g. in the Report Footer section,
or on a Form view form, the issue is purely academic. Just go ahead and use
the domain aggregate function.

If you have multiple lookups, you might consider opening a recordset to
retrieve all the values at once, instead of multiple lookups.
OpenRecordset() will be faster than the domain aggregate functions. There's
an example of how to replace DLookup() in this article:
http://allenbrowne.com/ser-42.html

If you are doing this for every records (e.g. in a query, a continuous form,
or a report), opening a recordset for every record will still be cumbersome.
You might be able to circumvent this by creating a query that includes your
main table and also a lookup table, so the output has access to the lookup
table without needing and DLookup() or OpenRecordset(). And the query may
still be updatable (depending on what other tables, joins, and clauses the
query contains.

If it is okay not to have an updatable query, a subquery is another option.
This is enormously faster than DLookup() or OpenRecordset(), and allows you
to get calculated results from related tables without duplicating records.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
The major limitation of this approach is that if you are feeding a report
that performs any sorting or grouping on these fields, you are likely to run
into the "Multi-level group-by not allowed" error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon Mopes" <Sh**********@KidNet.com> wrote in message
news:44501d4b.4092328@news-server...
I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions. As I am a novice to SQL, any
suggestions would be hugely appreciated. Thanks.

Apr 26 '06 #2

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

Similar topics

0
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression, or maybe even DCount might be an alternative. I have tried numerous combinations, but with no apparent success....
2
by: Dalan | last post by:
I seemed to be having problems with structuring the use of NZ with a DSum expression. Having tried numerous variations of the expression without success, I'm asking for assistance. First some...
1
by: GERALD SHAW | last post by:
Acc 97 Hi, I have the following in my query which works well if it is all set to numbers on table design. But what I want to do is where it states MC (short for machine name) use the actual...
1
by: Marc Aube | last post by:
Is there a web site that can ofer some help. The quotation marks are posing an issue as well as other items for this function. I have some books but they are not consistent in their use of the code...
1
by: gbb0330 | last post by:
Hi All i am trying to calculate Quantity on hand in a unbound textbox in a form with a subform QonHand=Qreceived - TotalQSold TotalQsold=QsoldOnEbay+QsoldThruOtherChannels here is my...
15
by: sara | last post by:
Hi I'm pretty new to Access here (using Access 2000), and appreciate the help and instruction. I gave myself 2.5 hours to research online and help and try to get this one, and I am not getting...
1
by: kaosyeti | last post by:
so i'm new to access but i've done a lot in the last 3 months since i looked at it for the first time back then. so when i finished my first db for my job, i cut it close to the end of the year...
2
by: davy d | last post by:
I'm having a problem with an acess form linkin with another form. My index in the main form is Autonumber (Rec#) the 2nd form is called Record# and it is set for text. The link criteria works...
33
by: potassium flower | last post by:
I am trying to create a food order system for a restaurant. I have tried using both the DSum and Sum functions to calculate the total cost of an order. The total cost is a textbox on the form...
2
by: wassimdaccache | last post by:
Hello I have a subform base on query. I used a textbox with a control source = =nz(DCount("ref_ID","checklist","=" & & "and" & "= " & True & " and" & "='" & !! & "' "),0) Each time we...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.