Calculations using unions

[Stewman]

Hello,

I have a query consisting of 3 queries all union-ed together each returning a specific count. My end result looks like this:
Kount Type
786 Duplicates
11737 Orders
38503 Calls

What I want is to do the following calculation within this sql query.

(Calls + Orders) - Duplicates

Is this possible?

Thanks,

Stewman

 

[dhookom]

Try something like:

SELECT SUM(IIf(Type = "Duplicates", -1, 1) * Kount) as FinalAmount
FROM YourUnionQueryNameHere

Duane
Hook'D on Access
MS Access MVP

 

[Stewman]

Hi Duane,

Thanks for the response. When I try this (on Oracle btw) I get the error back ORA-00907:missing right parenthesis. Any ideas?

 

[PHV]

on Oracle btw
So, why posting in a JetSQL (Microsoft) forum ?
I'd ask in an Oracle forum here.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stewman]

Yeah good point. Didn't realize this was for ACCESS and Jet. I thought it was general SQL. I'll post on an oracle forum.

 

[SkipVought]

You'ld be using Case rather than Immediate If.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]