UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Totals Sum In Query, Access 2016    
 
   
dynamicdave
post Nov 6 2019, 03:56 PM
Post#1



Posts: 204
Joined: 4-September 01



MS has been nice enough to include the totals function for a query. The one activated by theta in the tool bar.

This adds a nice row to the bottom of your query and a user can select from average, count, sum and so on. Loverly. Thanks very much....

However what if I want to create the query on the fly? That is I want to encode the SQL within other code or build a SQL statement in code behind a form. How do I then add this function to the query?

--------------------
Perth, Western Australia - The most isolated capital in the world
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 04:07 PM
Post#2


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


>> The one activated by theta in the tool bar. <<
I always thought it was Sigma!

A totals query in SQL would look something like:
CODE
SELECT
  ContactID,
  SUM(Amount)
FROM YourTable
GROUP BY
  ContactID
ORDER BY Amount
;

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Nov 6 2019, 08:43 PM
Post#3


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


QUOTE
However what if I want to create the query on the fly? That is I want to encode the SQL within other code or build a SQL statement in code behind a form. How do I then add this function to the query?

The question is, what do you want to vary in the SQL? If it is just criteria, for example, using values from a form's control(s), then VBA is not necessary. If the selection of fields is required or some other manipulation of the SQL itself, on the other hand, then you can use VBA to create the query and either save it or insert it into the record source of a form or report.

I think we need more information on specifically what you want to accomplish before we can really help. And where you want to use the query ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 11:53 PM