Union Queries - Results showing as text & not numbers

RAMMY987

Registered User.
Local time
Today, 12:17
Joined
Sep 7, 2007
Messages
12
To cut a long story short, i've got a union query that is a full outer join of 2 tables.

Anyway, the results i have got back needed formatting to no decimal places.

How do i do that?

I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.

It's like it is showing as a text and not number.

Is there a way of converting the result to a number, particularly in a sql query.
 
Last edited:
Can you post the SQL of your query so we can see exactly what is going on
 
Here it is.

SELECT [LSC - EBS - Funding - Combined Funding].[Cost Centre], Sum([LSC - EBS - Funding - Combined Funding].[16-18 Income]) AS [SumOf16-18 Income], Sum([LSC - EBS - Funding - Combined Funding].[Adult Income]) AS [SumOfAdult Income], [LSC - FIMO - Funding].[Cost Centre], Max([LSC - FIMO - Funding].[Budget 16-18 Income]) AS [MaxOfBudget 16-18 Income], Max([LSC - FIMO - Funding].[Budget Adult Income]) AS [MaxOfBudget Adult Income]
FROM [LSC - EBS - Funding - Combined Funding] INNER JOIN [LSC - FIMO - Funding] ON [LSC - EBS - Funding - Combined Funding].[Cost Centre] = [LSC - FIMO - Funding].[Cost Centre]
GROUP BY [LSC - EBS - Funding - Combined Funding].[Cost Centre], [LSC - FIMO - Funding].[Cost Centre]
UNION ALL
SELECT [LSC - EBS - Funding - Combined Funding].[Cost Centre], Sum([LSC - EBS - Funding - Combined Funding].[16-18 Income]) AS [SumOf16-18 Income], Sum([LSC - EBS - Funding - Combined Funding].[Adult Income]) AS [SumOfAdult Income], [LSC - FIMO - Funding].[Cost Centre], Max([LSC - FIMO - Funding].[Budget 16-18 Income]) AS [MaxOfBudget 16-18 Income], Max([LSC - FIMO - Funding].[Budget Adult Income]) AS [MaxOfBudget Adult Income]
FROM [LSC - EBS - Funding - Combined Funding] LEFT JOIN [LSC - FIMO - Funding] ON [LSC - EBS - Funding - Combined Funding].[Cost Centre] = [LSC - FIMO - Funding].[Cost Centre]
GROUP BY [LSC - EBS - Funding - Combined Funding].[Cost Centre], [LSC - FIMO - Funding].[Cost Centre]
HAVING ((([LSC - FIMO - Funding].[Cost Centre]) Is Null))
UNION ALL
SELECT [LSC - EBS - Funding - Combined Funding].[Cost Centre], Sum([LSC - EBS - Funding - Combined Funding].[16-18 Income]) AS [SumOf16-18 Income], Sum([LSC - EBS - Funding - Combined Funding].[Adult Income]) AS [SumOfAdult Income], [LSC - FIMO - Funding].[Cost Centre], Max([LSC - FIMO - Funding].[Budget 16-18 Income]) AS [MaxOfBudget 16-18 Income], Max([LSC - FIMO - Funding].[Budget Adult Income]) AS [MaxOfBudget Adult Income]
FROM [LSC - EBS - Funding - Combined Funding] RIGHT JOIN [LSC - FIMO - Funding] ON [LSC - EBS - Funding - Combined Funding].[Cost Centre] = [LSC - FIMO - Funding].[Cost Centre]
GROUP BY [LSC - EBS - Funding - Combined Funding].[Cost Centre], [LSC - FIMO - Funding].[Cost Centre]
HAVING ((([LSC - EBS - Funding - Combined Funding].[Cost Centre]) Is Null));

Basically it is the joining of 3 queries, all of which show numbers, but the results of this show as text.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom