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.
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.