sub5
Programmer
- Oct 12, 2005
- 104
Hello All,
I have a union query based on two crosstab queries. I have formated both xtabs' fields The Value and Total as Standard with 0 decimal places. However in the union all the decimals come back. How do I set the formating in the union to 0 decimals (Please note the number and titles of the union columns can change depending on the records in Project)?
???I have seen postings here which say use format(Total,0) but I don't know how I can use that because my columns are dynamic.???
crosstab SQL
TRANSFORM Sum(qry02_05_CashReportCheck.[Converted to £]) AS [The Value]
SELECT qry02_05_CashReportCheck.RRCAELAllocation, Sum(qry02_05_CashReportCheck.[Converted to £]) AS Total
FROM qry02_05_CashReportCheck INNER JOIN tpcsWeek ON (qry02_05_CashReportCheck.Week = tpcsWeek.Week) AND (qry02_05_CashReportCheck.AP = tpcsWeek.AP)
GROUP BY qry02_05_CashReportCheck.RRCAELAllocation
PIVOT qry02_05_CashReportCheck.Project;
Union query SQL
SELECT *
FROM qry02_10_CashReportCheck
UNION ALL SELECT *
FROM qry02_20_CashReportCheck;
I have a union query based on two crosstab queries. I have formated both xtabs' fields The Value and Total as Standard with 0 decimal places. However in the union all the decimals come back. How do I set the formating in the union to 0 decimals (Please note the number and titles of the union columns can change depending on the records in Project)?
???I have seen postings here which say use format(Total,0) but I don't know how I can use that because my columns are dynamic.???
crosstab SQL
TRANSFORM Sum(qry02_05_CashReportCheck.[Converted to £]) AS [The Value]
SELECT qry02_05_CashReportCheck.RRCAELAllocation, Sum(qry02_05_CashReportCheck.[Converted to £]) AS Total
FROM qry02_05_CashReportCheck INNER JOIN tpcsWeek ON (qry02_05_CashReportCheck.Week = tpcsWeek.Week) AND (qry02_05_CashReportCheck.AP = tpcsWeek.AP)
GROUP BY qry02_05_CashReportCheck.RRCAELAllocation
PIVOT qry02_05_CashReportCheck.Project;
Union query SQL
SELECT *
FROM qry02_10_CashReportCheck
UNION ALL SELECT *
FROM qry02_20_CashReportCheck;