I'm writing a Crosstab and the problem that I've run into is my total column shows a repeating decimal even though there are only 2 decimal places on my field. I've tried converting the field to currency, double even tried to run a trim on the field all with the same results. Any idea?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Crosstab Rounding issue
- Thread starter Moxy1
- Start date
- Thread starter
- #3
it is fairly straight foward:
TRANSFORM Sum(tblReturnBalance.ret_amount) AS SumOfret_amount
SELECT tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name, Sum(tblReturnBalance.ret_amount) AS [Total Of ret_amount]
FROM tblReturnBalance
GROUP BY tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name
PIVOT tblReturnBalance.ret_description;
TRANSFORM Sum(tblReturnBalance.ret_amount) AS SumOfret_amount
SELECT tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name, Sum(tblReturnBalance.ret_amount) AS [Total Of ret_amount]
FROM tblReturnBalance
GROUP BY tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name
PIVOT tblReturnBalance.ret_description;
You may try this:
TRANSFORM [!]Round([/!]Sum(tblReturnBalance.ret_amount)[!],2)[/!] AS SumOfret_amount
SELECT tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name, [!]Round([/!]Sum(tblReturnBalance.ret_amount)[!],2)[/!] AS [Total Of ret_amount]
FROM tblReturnBalance
GROUP BY tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name
PIVOT tblReturnBalance.ret_description
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
TRANSFORM [!]Round([/!]Sum(tblReturnBalance.ret_amount)[!],2)[/!] AS SumOfret_amount
SELECT tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name, [!]Round([/!]Sum(tblReturnBalance.ret_amount)[!],2)[/!] AS [Total Of ret_amount]
FROM tblReturnBalance
GROUP BY tblReturnBalance.ret_branch, tblReturnBalance.ret_client_no, tblReturnBalance.ret_client_name
PIVOT tblReturnBalance.ret_description
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
- Status
Similar threads
- Locked
- Question
