Problem with Aggregating functions in update queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Again and again I run into this frustrating limitation in Access that you can't use aggregate SQL functions in update queries. I see no reason why it has to be so. Getting this changed has to be no. 1 on my wish list for Access. I would have expected the issue to be fixed in Access 2003

Many others must have hit this limitation. Couldn't the MVP's collect all posts regarding this and present it to Microsoft to put some pressure there, or do they not even listen to the MVP's when deciding about changes in future versions

Presently, I have a problem with accumulating totals across hierarchies of companies. My problem is that in the aggregating subquery, I need to refer to a field in the outer query on the customer. Thus I cannot get the DSUM function to work (it gives null values). I guess it is the third parameter where I refer to a field not in from the query given as the domain (2. parameter of the DSUM function)

Now, since I have to run this update query several times (for each level in hierarchy) to have the totals agreggregated correctly upwards in the hierarchy, I foresee a huge number of temporary tables with totals, a confusing mess - if I can't use either aggregating functions or DSUM.

As I see it my options are to either use a different SQL tool than the Access (but everything else is encapsuled in this Access project, so it's not a good solution) or make a VBA module that combines queries with parameters.

Any other suggestions

Regards

Frank M.
 
Hi,


Microsoft listen to MVP, not only to them, to their customers too, but
it is surely preferable to get MANY posts in their suggestion box. :-) I
think we will need a LOT of them to get Microsoft move on your first point,
unfortunately, a LOT of messages.


There is other alternatives, one is to precompute the required sums,
and make a table of that result. Then, update the table through the tables.


Hoping it may help,
Vanderghast, Access MVP



Frank M. said:
Again and again I run into this frustrating limitation in Access that you
can't use aggregate SQL functions in update queries. I see no reason why it
has to be so. Getting this changed has to be no. 1 on my wish list for
Access. I would have expected the issue to be fixed in Access 2003.
Many others must have hit this limitation. Couldn't the MVP's collect all
posts regarding this and present it to Microsoft to put some pressure there,
or do they not even listen to the MVP's when deciding about changes in
future versions.
Presently, I have a problem with accumulating totals across hierarchies of
companies. My problem is that in the aggregating subquery, I need to refer
to a field in the outer query on the customer. Thus I cannot get the DSUM
function to work (it gives null values). I guess it is the third parameter
where I refer to a field not in from the query given as the domain (2.
parameter of the DSUM function).
Now, since I have to run this update query several times (for each level
in hierarchy) to have the totals agreggregated correctly upwards in the
hierarchy, I foresee a huge number of temporary tables with totals, a
confusing mess - if I can't use either aggregating functions or DSUM.
As I see it my options are to either use a different SQL tool than the
Access (but everything else is encapsuled in this Access project, so it's
not a good solution) or make a VBA module that combines queries with
parameters.
 
Back
Top