putting the total row at the end of a cross tab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to put the total row (row heading) at the end of your results
after all your column headings? If so how can I do this all in one query.
 
If you use the Column Headings property of the crosstab so that all the
column headings are specified, you can add a calculated field to the grid as
a RowHeading.

You will type something like this into the Field row:
HorizTotal: Nz([F1],0) + Nz([F2],0) + ...
where F1 and F2 are the names in the Column Headings.
 
Allen thanks for the response.

This is what I have and it is not working, what am I doing wrong?

TRANSFORM Sum(TSAU.[Reg Hours]) AS [SumOfReg Hours]
SELECT TSAU.Project, TSAU.Resource AS [Resource Name], TSAU.Dept AS [Dept
Name], TSAU.Task AS [Task Name], TSAU.CN AS [Charge No], TSAU.[Row Type],
Sum(TSAU.[Reg Hours]) AS [Total Hours]
FROM [TS Actuals 001 Union] AS TSAU
WHERE (((TSAU.Period)<>"pre 2005"))
GROUP BY TSAU.Project, TSAU.Resource, TSAU.Dept, TSAU.Task, TSAU.CN,
TSAU.[Row Type]
PIVOT TSAU.Period;

Allen Browne said:
If you use the Column Headings property of the crosstab so that all the
column headings are specified, you can add a calculated field to the grid as
a RowHeading.

You will type something like this into the Field row:
HorizTotal: Nz([F1],0) + Nz([F2],0) + ...
where F1 and F2 are the names in the Column Headings.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

anthonysjo said:
Is it possible to put the total row (row heading) at the end of your
results
after all your column headings? If so how can I do this all in one query.
Click to expand...
Click to expand...
 
Sorry, Anthony. The information I gave you works in a report, not in the
crosstab.

In the crosstab it is even simpler.
In a fresh column in query design, choose:
Field: TotalHours: [Reg Hours]
Total: Sum
Crosstab: Row Heading

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

anthonysjo said:
Allen thanks for the response.

This is what I have and it is not working, what am I doing wrong?

TRANSFORM Sum(TSAU.[Reg Hours]) AS [SumOfReg Hours]
SELECT TSAU.Project, TSAU.Resource AS [Resource Name], TSAU.Dept AS [Dept
Name], TSAU.Task AS [Task Name], TSAU.CN AS [Charge No], TSAU.[Row Type],
Sum(TSAU.[Reg Hours]) AS [Total Hours]
FROM [TS Actuals 001 Union] AS TSAU
WHERE (((TSAU.Period)<>"pre 2005"))
GROUP BY TSAU.Project, TSAU.Resource, TSAU.Dept, TSAU.Task, TSAU.CN,
TSAU.[Row Type]
PIVOT TSAU.Period;

Allen Browne said:
If you use the Column Headings property of the crosstab so that all the
column headings are specified, you can add a calculated field to the grid
as
a RowHeading.

You will type something like this into the Field row:
HorizTotal: Nz([F1],0) + Nz([F2],0) + ...
where F1 and F2 are the names in the Column Headings.


anthonysjo said:
Is it possible to put the total row (row heading) at the end of your
results
after all your column headings? If so how can I do this all in one
query.
Click to expand...
Click to expand...
Click to expand...
 
Allen I think this is what I have defined using the design view. I even have
the Total Hours column listed last but it shows just before my column
headings.

Allen Browne said:
Sorry, Anthony. The information I gave you works in a report, not in the
crosstab.

In the crosstab it is even simpler.
In a fresh column in query design, choose:
Field: TotalHours: [Reg Hours]
Total: Sum
Crosstab: Row Heading

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

anthonysjo said:
Allen thanks for the response.

This is what I have and it is not working, what am I doing wrong?

TRANSFORM Sum(TSAU.[Reg Hours]) AS [SumOfReg Hours]
SELECT TSAU.Project, TSAU.Resource AS [Resource Name], TSAU.Dept AS [Dept
Name], TSAU.Task AS [Task Name], TSAU.CN AS [Charge No], TSAU.[Row Type],
Sum(TSAU.[Reg Hours]) AS [Total Hours]
FROM [TS Actuals 001 Union] AS TSAU
WHERE (((TSAU.Period)<>"pre 2005"))
GROUP BY TSAU.Project, TSAU.Resource, TSAU.Dept, TSAU.Task, TSAU.CN,
TSAU.[Row Type]
PIVOT TSAU.Period;

Allen Browne said:
If you use the Column Headings property of the crosstab so that all the
column headings are specified, you can add a calculated field to the grid
as
a RowHeading.

You will type something like this into the Field row:
HorizTotal: Nz([F1],0) + Nz([F2],0) + ...
where F1 and F2 are the names in the Column Headings.


Is it possible to put the total row (row heading) at the end of your
results
after all your column headings? If so how can I do this all in one
query.
Click to expand...
Click to expand...
Click to expand...
 
anthonysjo said:
Allen I think this is what I have defined using the design view. I
even have the Total Hours column listed last but it shows just before
my column headings.
Click to expand...

So after the results are displayed drag the column where you want it and choose
Save Changes when you close it.
 
Rick thanks for the help, it worked!!!

Funny thing is that it looks exactly the same in the design view but I have
a feeling that the underlying SQL changed.

Thanks again for the help!!
 
anthonysjo said:
Rick thanks for the help, it worked!!!

Funny thing is that it looks exactly the same in the design view but
I have a feeling that the underlying SQL changed.

Thanks again for the help!!
Click to expand...

No. As explained in a previous thread a couple days ago Access stores quite
a few properties about a table and/or query that you do not see in the GUI.
Column order, Column width, Hide/Show of Columns, Sorts and Filters applied
to the Datasheet instead of in the QueryDef, etc.. What you did was change
one of these properties as it relates to the positions of the columns. If
you copied your SQL into a freshly created query the column order would not
be the same.
 
Great explaination!!! Thanks for the help!

Rick Brandt said:
No. As explained in a previous thread a couple days ago Access stores quite
a few properties about a table and/or query that you do not see in the GUI.
Column order, Column width, Hide/Show of Columns, Sorts and Filters applied
to the Datasheet instead of in the QueryDef, etc.. What you did was change
one of these properties as it relates to the positions of the columns. If
you copied your SQL into a freshly created query the column order would not
be the same.
Click to expand...
 
Back
Top