Grand total in Queries

J

Julia T

Hello,

I need some help. Can you do a grand total line in a querie?

ie:

Name Code Amount 1 Code Amount 2 Total Code Amount
Pete 20 30 50
Mary 10 20 30
Lauren 10 10 20
"Can I Total
the Columns" 40 60 100

I have attached the sql statement to help.

Thanks for your help in advance!!

SELECT [ALL New vs Est - % by Group].DOC_NAME, [ALL New vs Est - % by
Group].[Total Of YR_UNIT], [ALL New vs Est - % by Group].GROUP_DESC, [ALL New
vs Est - % by Group].CONSULT, [ALL New vs Est - % by Group].EST, [ALL New vs
Est - % by Group].NEW
FROM [ALL New vs Est - % by Group];
 
J

John Vinson

Hello,

I need some help. Can you do a grand total line in a querie?

ie:

Name Code Amount 1 Code Amount 2 Total Code Amount
Pete 20 30 50
Mary 10 20 30
Lauren 10 10 20
"Can I Total
the Columns" 40 60 100

I have attached the sql statement to help.

Thanks for your help in advance!!

SELECT [ALL New vs Est - % by Group].DOC_NAME, [ALL New vs Est - % by
Group].[Total Of YR_UNIT], [ALL New vs Est - % by Group].GROUP_DESC, [ALL New
vs Est - % by Group].CONSULT, [ALL New vs Est - % by Group].EST, [ALL New vs
Est - % by Group].NEW
FROM [ALL New vs Est - % by Group];
Click to expand...

YOu can't (easily) do it in a Query, since all the rows of a Query
should be of the same "rank" - and no row should depend upon the
values of other rows.

However, it's very easy to display this *INFORMATION* on a Form or
Report; simply put textboxes in the Form Footer, or the Report's
footer or section footer, with control sources

=Sum([Code Amount 1])

and so on.

John W. Vinson[MVP]
 
J

Julia T

Thanks John and I have done this in a report but then I need to chart the
information on a graph and I run into a problem because I need the grand
total infomation on the chart.

Can it be done in the queries section?

John Vinson said:
Hello,

I need some help. Can you do a grand total line in a querie?

ie:

Name Code Amount 1 Code Amount 2 Total Code Amount
Pete 20 30 50
Mary 10 20 30
Lauren 10 10 20
"Can I Total
the Columns" 40 60 100

I have attached the sql statement to help.

Thanks for your help in advance!!

SELECT [ALL New vs Est - % by Group].DOC_NAME, [ALL New vs Est - % by
Group].[Total Of YR_UNIT], [ALL New vs Est - % by Group].GROUP_DESC, [ALL New
vs Est - % by Group].CONSULT, [ALL New vs Est - % by Group].EST, [ALL New vs
Est - % by Group].NEW
FROM [ALL New vs Est - % by Group];
Click to expand...

YOu can't (easily) do it in a Query, since all the rows of a Query
should be of the same "rank" - and no row should depend upon the
values of other rows.

However, it's very easy to display this *INFORMATION* on a Form or
Report; simply put textboxes in the Form Footer, or the Report's
footer or section footer, with control sources

=Sum([Code Amount 1])

and so on.

John W. Vinson[MVP]
Click to expand...
 
L

Larry Linson

... I need to chart the information on
a graph and I run into a problem
because I need the grand total
infomation on the chart.
Click to expand...

You chould do a Totals Query to create the totals line, and then use UNION
or UNION ALL to append it to the end of the SELECT Query. That should work
in your case, because just to graph the information, you won't need to
update it, and that combination will definitely not be updateable.

Larry Linson
Microsoft Access MVP
 
Top