adding calculated fields in queries

i am trying to execute a query that sums two fields in a table of quarterly data.

field one: the sum of all 4 quarters for [employees that left that quarter] for a particular year. so i need to sum this field for all 4 quarters.

field two: the sum of [total employees] that are only in quarter 4. so i need to sum all of the quarters that are labeled '4' for all clients that submit their employee data.

In one query, i can get either one of the two fields to display, but not both at the same time. i need to both to somehow come up so i can add the together. would i need a join query? thanks

ameen

that question was really hard to figure out

perhaps you are looking for something like this --

select sum(foo) as totalsum
, sum(case when quarter=4 then foo else 0 end) as q4sum
from yourtable

rudy
http://r937.com/

i don't know too much about sql syntax. my field names are

'left quarter' (that i need the sum for all for quarters) and

'new total' (from which i need the sums of just quarters labeled 4)

this is what i currently have.

Sum([Employee Data].[Left Quarter]) AS [SumOfLeft Quarter], Sum(case when quarter=4 then [Employee Data].[New Total] else 0 end) AS [SumOfNew Total]

i get a message that says i have a missing operator and the 'when' is highlighted after i ok the error message. what do you think is wrong? thanks.

ameen

what do i think is wrong? perhaps you are using a database that does not support standard sql (i.e. case)

which one are you using, by the way?

if it's access, try

select Sum([Employee Data].[Left Quarter])
AS [SumOfLeft Quarter]
, Sum(IIF(quarter=4,[Employee Data].[New Total],0)
AS [SumOfNew Total]

rudy

yes, it is access. now i have:

Sum([Employee Data].[Left Quarter]) AS [SumOfLeft Quarter], Sum(IF(quarter=4,[Employee Data].[New Total],0)) AS [SumOfNew Total]

and i get the message undefined function 'IF' in expression

sql is a very precise language

you must exercise due diligence when copying

the function is IIF not IF

OK. got it to work thanks! also, do you know how to select DISTINCT of field on which i am also using COUNT? it is for client ID and looks like:

Count(Clients.[Client ID]) AS [CountOfClient ID]

i really appreciate your help.

ameen

access does not support COUNT DISTINCT

you need to split the query into two -- one to be saved, and the other to query the saved query

i used to have an example, but i don't any more

a proper solution will require a complete understanding of your table structure and data patterns


rudy