Thanks.
In the specific example that is plaguing me, it is a multi-user DB, but it
is being run by all users locally on a terminal server, and the performance
is the same whether one or all are using the app. The performance is
suffering largely due to the complexity of certain reporting requirement (in
addition to the speed of the server - the particular report takes about 1/4
as long to run locally on my development station, butthat is still about
1-3/4 minutes)
The biggest offender is a certain report that (in addition to a lot of other
info) needs to Sum a particular complex total for each of the next twelve
months (the DSum in VBA was a sideline to this issue) and present these as
items on each line of output.
The report layout, simplified, looks like this:
Item Descr Month1Total Month2Total Month3Total, etc. -> Month 12
1 ABC $1200 $1500 $1700
2 DEF $ 350 $ 0 $ 25
Totals at bottom
I calculate these like this:
First month's total -> DSum (blah, blah, blah where date is within next month
Second month's total -> DSum (blah, blah, blah where date is within the
month after next (using dateadd to group into the correct months)
I'm trying to find an alternative, because I suspect the DSum as the
bottleneck here.
:
Minimizing the use of domain aggregates is a laudable goal; however, one can
never get rid of all of them. Whether you use a query or a DSum, DLookup,
depends on a number of factors. If you just want the one value returned, I
would suggest the DSum. If you have additional data you want at the same
time, the query would be the way to go.
One key point, as I stated earlier, is to use the Execute method rather than
the RunSQL. It is much faster. the Execute method only works for action
queries. For select queries, I would suggest using a recordset. In fact, in
my personal experience, it seems to be faster if I first create a recordset
with no domain aggregates in it if I need a group of data. Then If I need to
total a field, do a DSum on the recordset I created.
If you are having performance problems, there may be other factors to
consider. Are you using a split database? Are there multiple users? Does
each user have a copy of the front end on their own workstation? Users
sharing a front end database or an unsplit database over a network is a real
performance issue.
:
That's how I am already doing it (DSum, DLookup, etc.). However, everywhere I
turn for help on speeding up my DB's, I hear, "Get rid of your domain
aggregates", and that is why I asked the question in the first place.
Am I missing something here? Is there really some advantage to using Sum in
a parameterized query over using DSum?
:
One note. Instead of the RunSQL, use the Execute method instead. It is much
faster.
Now to do the sum of Quantity where Customer is 1234 and you want the
results in ABC
First, make ABC a Variant data type, because in the DSUM function, it is
possible to get a null return if there are no matches. Since I don't know
where you are call it from, I will, for example purposes, assume you have a
text box on a form named txtCustID, and the current record is Customer 1234
ABC = DSUM("[Quantity]", "MyCustomerTableName", "[CustomerID] = " & txtCustID
The above assumes the data type of [CustomerID] is a numeric field. If it
is a text field, then use this version:
ABC = DSUM("[Quantity]", "MyCustomerTableName", "[CustomerID] = '" &
txtCustID & "'"
If there are no records matching the criteria, then ABC will contain Null;
otherwise, it will contain the sum for quantity for the selected customer.
:
Thanks, ALlan. Just a follow-up here, though, because I have always used a
lot of queries & subqueries and am not very familiar with building recordsets
in VBA.
As an example, let's say I want to assign the Sum of the Quantity field from
the Invoices table where CustomerID=1234 to variable ABC. What I have been
doing is writing a Sum query against Invoices, and then (in VBA) doing a
DFirst (since the query will return only a single entry) or DLookup to grab
the SubOfQuantity.
There are two parts I don't understand yet:
1. Can I just do this in VBA, without the query?
2. If not, how do I pass the Where clause to the query on the fly without
using it in the DFirst or DLookup? Obviously, I cannot hard-code the criteria
in the query.
:
Brian, if you just want a single value in VBA, just go for it.
If you need multiple values, or values in a loop, you are probably better to
build a recordset to get those values. It will be based on a totals query,
or possibly a query with a subquery.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Everything I am reading tells me I should minimize my use of domain
aggregate
functions, so I am in the process of going through several apps to replace
these. In many cases, I can use a join in a query, but there are times
when I
need to run the statement from VBA. I need a tip on how to accomplish
this.
I know how to run action queries using RunSQL, but (how) can I run select
queries to return Max, Sum, etc. results to a variable or array in VBA?
They are all running the same copy of the FE locally on a