Tips on domain aggregate replacements

[Brian]

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?

 

[Allen Browne]

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

 

[Brian]

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.

 

[Klatuu]

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.

 

[Brian]

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.

 

[Albert D.Kallal]

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?

that tip is usually referring to sql quires, and not so much in code.

It is HORRIBLE performance hit to use a dlookup in a query like:

select FirstName, LastName, dlookup(bla bla al) from tlbcustomers

99% of the time, the above dlookup can be replaced with a sub-query, or a
join to the other table.

So, really, no, there is not much of and advantage in your example. However,
if you are using the dlookup in a loop, or the dsum in a loop (or query),
then remove the dlookup, and use relational join, or a sub-query to get
those results.

It makes little, if any sense to repalce a dsum, or a dlookup with a bunhc
of code and a reocsodet *if* you don't need to.

A seem a good number of mentions that these functions are slow, and, they
are NOT slow. However, if you got one inside of a loop, or a query that
don't need to be there..then you are wasting perfoamcne....

 

[Klatuu]

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?

 

[Brian]

Thank you. At least I know which dom ag's to focus on now...

 

[Brian]

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?

 

[Klatuu]

Okay, Brian, this is going to send you screaming to the looney bin. I went
back to an app I did about 6 years ago, because I remember a specific report
that took forever to run, and I made some mods that took it from about an
hour to run to about 2 minutes. Here is the funny part. I took all the
aggregate functions out of the query. Then in the report. I would do DSums
from the controls on the report on the data returned by the query.

The other thing is you really need to split your database. Put the back end

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?

 

[Brian]

I will try moving the DSum's to the report. I already have several others
there, and the quirk is that it seems to make navigating from page to page
rather slow (of course, there could be some other reason for that).

This already is a split db, but the users are 400 miles away from the server
hosting the data They are all running the same copy of the FE locally on a
single terminal server via TS/RDP sessions. So, for all intents & purposes,
there already is a copy of executable on each user's station. The BE sits in
the same folder on that server where the FE sits. Would there be some
advantage to having them each run their own copies locally on the terminal
server?

It doesn't seem to have much impact on performance; the report takes just
about as long to run if they are all logged in as it does when I am logged on
testing it at night.

Having said all of this, I always split my databases. The only reason that I
don't have the BE sitting on a server in their office with a FE on each
workstation is that I have to get all my inefficiencies worked out first.
Several of my other FE/BE db's seem to suffer from network latency issues,
and I have not had time yet to go through the process of keeping the
connection to the db open, trimming out domain aggregate functions, etc. to
maximize efficiency.

I spend virtually all my time on user interface and data structure/integrity
issues - don't have much time to rethink methodologies...

Okay, Brian, this is going to send you screaming to the looney bin. I went
back to an app I did about 6 years ago, because I remember a specific report
that took forever to run, and I made some mods that took it from about an
hour to run to about 2 minutes. Here is the funny part. I took all the
aggregate functions out of the query. Then in the report. I would do DSums
from the controls on the report on the data returned by the query.

The other thing is you really need to split your database. Put the back end

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?

 

[Klatuu]

I certainly understand your situation. As to performance, it is always best
for each user to have the front end on their own computer, not on a network
server. This will always be faster. If you don't believe me, do some
research. You will find that this is the recommended configuration. As to
your deployment problem, check out this site, it may be useful to you.

http://www.granite.ab.ca/access/autofe.htm

I will try moving the DSum's to the report. I already have several others
there, and the quirk is that it seems to make navigating from page to page
rather slow (of course, there could be some other reason for that).

This already is a split db, but the users are 400 miles away from the server
hosting the data They are all running the same copy of the FE locally on a
single terminal server via TS/RDP sessions. So, for all intents & purposes,
there already is a copy of executable on each user's station. The BE sits in
the same folder on that server where the FE sits. Would there be some
advantage to having them each run their own copies locally on the terminal
server?

It doesn't seem to have much impact on performance; the report takes just
about as long to run if they are all logged in as it does when I am logged on
testing it at night.

Having said all of this, I always split my databases. The only reason that I
don't have the BE sitting on a server in their office with a FE on each
workstation is that I have to get all my inefficiencies worked out first.
Several of my other FE/BE db's seem to suffer from network latency issues,
and I have not had time yet to go through the process of keeping the
connection to the db open, trimming out domain aggregate functions, etc. to
maximize efficiency.

I spend virtually all my time on user interface and data structure/integrity
issues - don't have much time to rethink methodologies...

Okay, Brian, this is going to send you screaming to the looney bin. I went
back to an app I did about 6 years ago, because I remember a specific report
that took forever to run, and I made some mods that took it from about an
hour to run to about 2 minutes. Here is the funny part. I took all the
aggregate functions out of the query. Then in the report. I would do DSums
from the controls on the report on the data returned by the query.

The other thing is you really need to split your database. Put the back end

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?

 

[Dirk Goldgar]

I certainly understand your situation. As to performance, it is
always best for each user to have the front end on their own
computer, not on a network server. This will always be faster.

That's true in a normal LAN configuration. But in a terminal server
setup, such as I understand Brian to be using, each user's front-end is
typically on the same remote terminal server as the back-end, and each
user works with his own copy of the front-end within his terminal-server
session. Thus, Access is running locally on the server, while the user
views and interacts with that local session via terminal services. This
is the recommended WAN configuration.

 

[Klatuu]

Thanks, Dirk. That is an environment I am not familiar with and did not
understand the difference.

 

[Brian]

Thanks, Dirk. That is, indeed how I am running it, although the entire issue
that started this thread was my need to get things efficient enough to run in
a LAN so that I can distribute this to clients who don't have the luxury of a
terminal server.

My experience so far is that certain forms and reports are exponentially
slow across the LAN based on the number of users, and I am trying to educate
myself on the methods of increasing speed.

 

[David C. Holley]

Keep in mind that there's a BIG difference between having 100 DLookups()
in a row and 100 DLookups() spread across 100 different SUBS &
FUNCTIONS. The issue at hand is the number of times you're hitting the
tables. If you doing multiple DLookups() on the same table or query,
you're better off using DAO to open the recordset and extract the data.
If you're just using a couple of DLookups() in one sub it shouldn't make
that big of a difference. Same thing pretty much applies to the other
functions.

 

[David C. Holley]

If its just a single value, that's fine. I personally *HIGHLY* recomend
using DFirst() in the situation given because it presumes that there
will only be 1 record returned. I pref DLookup() as it can locate the
precise record. As I seem to recall, DLookup() wil return the first
record found that matches the criteria supplied.

 

[David C. Holley]

Ditto what he said, plus you can encapsulate the .Execute method in a
transaction and provide the option to bail out as well as capture errors
and such - its much more robust.

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.

:

 

[David C. Holley]

There is also the shear practical matter if things appear to be running
at an accept speed, there's no *IMMEDIATE* need to alter the code.
However, that does not preclude the possibility that you may have to
further down the road as the number of records increases and thus the
hits to the db. In short, if it ain't broke don't fix it. I would,
however, recommend that from here on out you consider that when you're
designing new functionality and then do the modifications as you have
time. You'll end up getting much more familar with the techniques on
functionality that's not in production instead of having to tweak live
code and end up till 3am in the morning trying to fix something.

 

[David C. Holley]

One other practical option is to use summary tables. A summary table is
used in situations where the number of detail records is immense, but
you just need a total. An example would be a banking application. When a
cashier makes a deposit, the credit to the account is added to the table
with the account detail and in the summary table(s) the following fields
are updated - account balance, total deposits today, cashier total
deposits (as examples). In this case the amount of effect to obtain the
account balance is effectively spread across each individual transaction
as opposed to being all at once in a query. This though will require
creating code that updates all of the related tables if you're using
Access. In more advanced DB's such as SQLServer, triggers can be setup
saving a good amount of time.

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?