Query with Totals via VBA

[cstimart]

I have an Access 2007 database. The user selects the Year/Quarter combination and pushed a button to display the resulting query, which the code creates as a temporary query.

Is it possible to have that query display totals at the bottom, and for specific columns....or do I need to create a permanent query with the totals and then change the SQL before opening when the button is selected?

 

[Micron]

display totals at the bottom You cannot do this with a query but you can with a report. You can create a Totals query, but the values returned in their respective fields will be at the top only of one row of data, or on every row in their field, depending on how other fields are grouped. By values I mean the results of the calculations being performed.

or do I need to create a permanent query with the totals and then change the SQL before opening when the button is selected?
I don't understand the question or how it relates to having totals at the bottom.

 

[cstimart]

or do I need to create a permanent query with the totals and then change the SQL before opening when the button is selected?
I don't understand the question or how it relates to having totals at the bottom.

My query is a list of records, which when you view the datasheet, you can use the Sigma in the Home ribbon to add a Totals row at the bottom...which you can then select certain columns to calculate a Sum, Average, Count, etc...

What I ended up doing was creating a permanent query which had the columns selected to calculate the Sum....then when the button is pushed with different Year/Quarter combination selected, then I revise the existing query using..

CurrentDb.QueryDefs("ByQuarter").SQL = "SELECT ...."

Problem solved.

 

[Micron]

Good that you got it solved, but I'm beginning to think you are referring to the datasheet view of a form. I didn't see anywhere in your original post that you were opening a form - I thought you were referring to the datasheet view of a query. display the resulting query

 

[cstimart]

Nope...it's a query....just a basic query (for example) that would gather a list of all the records from tblText that the Job Date was in 2015 Q1....then when viewing that datasheet, totaling the sum of the hours and sum of the widgets created, etc...

 

[Micron]

Then you gotta tell me - how do you get the totals at the bottom of this query in datasheet view?
And what version you're using.

 

[cstimart]

Access 2007 - create a basic query, goto the Datasheet View and utilize the Sigma on the home ribbon to include a Totals row....then each column should have a drop down list to select Sum, Average, Count, etc...

 

[Micron]

I see what you mean. Feel like a moron now - been using Access since version 2.0 and never knew this.

 

[cstimart]

I see what you mean. Feel like a moron now - been using Access since version 2.0 and never knew this.

Learning. That's what this forum is all about!