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.
after all your column headings? If so how can I do this all in one query.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
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.
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.
G
Guest
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;
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.
A
Allen Browne
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.
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.
G
Guest
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.
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.
R
Rick Brandt
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.
So after the results are displayed drag the column where you want it and choose
Save Changes when you close it.
G
Guest
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!!
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!!
R
Rick Brandt
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!!
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.
G
Guest
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.