CrossTab Query - Column Total

[ragu111]

i have a CrossTab Query. i want to have the Column Total for every record.

how can i make a column total in access query.

Ragu

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

Here is my SQL

TRANSFORM Sum([Assets Query].Dep_Value) AS SumOfDep_Value
SELECT [Assets Query].Company_Name, [Assets Query].Department_Name, [Assets Query].Asset_Cat_Code
FROM [Assets Query]
GROUP BY [Assets Query].Company_Name, [Assets Query].Department_Name, [Assets Query].Asset_Cat_Code
PIVOT Format([Date],"yyyy");

Ragu

 

[PHV]

You may try this:
TRANSFORM Sum([Assets Query].Dep_Value) AS SumOfDep_Value
SELECT [Assets Query].Company_Name, [Assets Query].Department_Name, [Assets Query].Asset_Cat_Code[!], Sum([Assets Query].Dep_Value) AS ColumnTotal[/!]
FROM [Assets Query]
GROUP BY [Assets Query].Company_Name, [Assets Query].Department_Name, [Assets Query].Asset_Cat_Code
PIVOT Format([Date],"yyyy");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

i feel there is a misunderstang

the above code give the Row_Total in a speprate column.

[red]what i need is a single columns total to appear end of the column[/red]

year 2005  2006
     100   150
     200   250
     300   200
[red]total600   500[/red]

 

[ragu111]

Anybody can help on this..??

ragu

 

[sxschech]

1. Copy your existing query
2. Whichever field has the group detail, change it to say "Total" For example, if you want the total to appear for each Dept_Name, then you would change Dept_Name to "Totals" (You'll need to keep the quotes).
3. Save the new query
4. Create a union query

Select * from query1
UNION
Select * from query2

You may need to create an additional field in your first 2 queries to set up a sort order if the total doesn't show in the location you expect.

 

[ragu111]

Good, fineally you helped me on this.

i mention "Total" on the ID column where it got Numbers. so the Total Row came on the last row.

Thanks again.