Interesting query trick...expandable to a crosstab?

[J Welsby]

I have a table with 3 fields - Name, Value, Month

After reading something about a Union query, I created the following select
query:

SELECT tblData.Name, Sum(tblData.Value) as Total
FROM tblData
GROUP BY tblData.Name
ORDER BY tblData.Name ASC
UNION ALL SELECT "Grand Total:", Sum(tblData.Value) as Total
FROM tblData;

What is interesting is that the last record becomes one with 'Grand Total'
appended in the Name column and the Total column showing the sum of all the
records:

Name Total
Jack 10
Sprat 30
Fat 30
Grand Total: 70


Question then, could this be expanded to a crosstab query so that a record
is added at the bottom of the query output which shows the total for each
column?

I tried this without success but was hoping that someone else had some idea
of how this could be accomplished. Following is the basic sql for a crosstab
query that works with this table:

TRANSFORM Sum(tblData.Value) AS SumOfValue
SELECT tblData.Name
FROM tblData
GROUP BY tblData.Name
PIVOT tblData.Month;

Anyone with much better sql skills than mine care to take a stab?

Many thanks in advance

 

[Albert D. Kallal]

Well, why bother?

If you are sending this to a report, the reports footer, just place a
standard text box, and for the data source, go

=sum([name of column to total])

With the above, you don't care if the report is based on a table, a query,
or even a cross tab.....

Easy as pie.....

Reports have all kinds of sorting and totalling and grouping features....it
is a better place to accomplish this stuff....

 

[J Welsby]

"Why bother?" Because I don't want reports in my db interface. Nice to have
the data readily availabe via a datasheet in a form. If the user wants the
data out of Access to work with it, then it's a simple copy/paste into
something like Excel

Appreciate this is doable via a Report but is it doable via a query?

 

[Guest]

What happens when you try it?

(david)

 

[Albert D. Kallal]

"Why bother?" Because I don't want reports in my db interface. Nice to
have
the data readily availabe via a datasheet in a form. If the user wants
the
data out of Access to work with it, then it's a simple copy/paste into
something like Excel

Ok, you made the point. I don't think relzyong on cut and past is very
reialiry aproahc to data procssing. As a generalre rule, I don't alow uses
to see, or view quierds, or tables direcliry. It is a time horned view that
most devleopers have. Take well note of suggesting #2

http://www.mvps.org/access/tencommandments.htm

Users can make a real mess of the data...espaiclry if they paste the wrong
way!!!

Appreciate this is doable via a Report but is it doable via a query?

Hum, ok...perhaps if you save a copy of the query (have two exact the same),
then perhaps a union query with sums might work..you could try that...

As, a last note, if while viewing a report, if you click on the analyse with
excel button..the columns, and totals go over nicely to Excel. And, even
more interesting is that the totals columns are actually exported as
formulas to Excel.

And, I suppose you could export the data to a temp table, and then run a
query on that...that would work for sure..but, a bit more trouble then
likely what you are looking for solution wise...

 

[J Welsby]

Albert, cheers. You have given me lots to consider here. Per David's
request, I will repost here in a couple days wrt what I have achieved.
Again, many thanks
James