Calculating Sum in query

[dm5085]

I'm trying to calculate a sum of Unique ID's in a query.

OTSEmpRec: IIf([tblNomination]![NominationType]=1,Count([tblNomination]![NominationId]))


It's not allowing the query to run with the error message "you tried to execute a query that does not include the specified expression "".
Can anyone help? Thanks so much!

 

[ZmrAbdulla]

Your IIF syntax is wrong see the VBA help file for correct syntax. Example from help file

IIf(expr, truepart, falsepart)

CheckIt = IIf(TestMe > 1000, "Large", "Small")

It is like

If TestMe >  1000 then 
CheckIt="Large"
Else
CheckIt="Small
End if

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[dhookom]

If you want to count the number of records in a grouping that have a NominationType = 1 then use
OTSEmpRec: Sum(Abs([tblNomination]![NominationType]=1))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dm5085]

Thanks to both of you for your replies but what I need to is count Unique nomination ID's where the nomination type is 1. The data is structured so that employees can receive many of these nominations in the same quarter but we only want to count them once in this new field [OTSEmpRec]. Any suggestions?

 

[PHV]

JetSQL lacks the Count(DISTINCT ...) aggregate function.
A workaround is to use an embedded view.
Any chance you could post your table(s) schema, some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dm5085]

Sure thing.

NomineeName NomineePosID NominationType NominationId
CARLSON, PETER M 417464 1 27210
CARLSON, PETER M 417464 2 27210
PETERSON, JOHN C 269702 1 3373
PETERSON, JOHN C 269702 2 3388
FOSTER, ALTA M 58036 2 13165

So in my query results, I'd like to see

[OTSEmpRec]= 4 Four records with unique NomID

 

[dm5085]

My data was wrong, sorry
Try this
NomineeName NomineePosID NominationType NominationId
CARLSON, PETER M 417464 1 27210
CARLSON, PETER M 417464 1 27210
PETERSON, JOHN C 269702 1 3373
PETERSON, JOHN C 269702 1 3388
FOSTER, ALTA M 58036 1 13165
MICKEY MOUSE 269702 2 334488
DONALD DUCK 58036 2 131111

So in my query results I’d like to see for Nomination type=1
Total of 4 (count of unique Nomination Ids for this Nomination type)

 

[r937]

save the following query as nominations_q --

select distinct
       NominationType    
     , NominationId
  from tblNomination

then run this query --

select count(*) as unique_NominationIds
  from nominations_q
 where NominationType = 1

r937.com | rudy.ca

 

[PHV]

A starting point:
SELECT Count(*) AS OTSEmpRec
FROM (SELECT DISTINCT NominationId FROM tblNomination WHERE NominationType=1
) AS D

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dm5085]

That works perfectly except now I have to count 3 other nomination types and I'd like to do it in the same query if I can. I'd like the results to look something like this:

OTSEMPREC SENEMPREC SEAEMPREC GOLDCOINEMPREC
4 2 1 14

Ive tried modifying the SQL to do this but I can't seem to get it to work

 

[PHV]

Perhaps something like this ?
SELECT
Sum(IIf(NominationType=1,1,0)) AS OTSEMPREC,
Sum(IIf(NominationType=2,1,0)) AS SENEMPREC,
Sum(IIf(NominationType=3,1,0)) AS SEAEMPREC,
Sum(IIf(NominationType=4,1,0)) AS GOLDCOINEMPREC
FROM (SELECT DISTINCT NominationType, NominationId FROM tblNomination) AS D

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dm5085]

Yes it does help a lot! I just realized that I'm going to need to group by quarter and recognition year and MgrName also. I've tried adding them to the sql but it doesn't seem to like that very much. Here's my sql

SELECT Sum(IIf(NominationType=1,1,0)) AS OTSEMPREC, Sum(IIf(NominationType=2,1,0)) AS SENEMPREC, Sum(IIf([NominationType]=3,1,0)) AS SEAEMPREC, Sum(IIf(NominationType=4,1,0)) AS GOLDCOINEMPREC
FROM [SELECT DISTINCT NominationType, NominationId, DirectMgrName, Quarter, RecognitionYear FROM tblNomination]. AS D;

Sorry about this, I'm just learning.

 

[PHV]

SELECT Quarter, RecognitionYear, DirectMgrName
, Sum(IIf(NominationType=1,1,0)) AS OTSEMPREC
, Sum(IIf(NominationType=2,1,0)) AS SENEMPREC
, Sum(IIf([NominationType]=3,1,0)) AS SEAEMPREC
, Sum(IIf(NominationType=4,1,0)) AS GOLDCOINEMPREC
FROM (SELECT DISTINCT NominationType, NominationId, DirectMgrName, Quarter, RecognitionYear FROM tblNomination) AS D
GROUP BY Quarter, RecognitionYear, DirectMgrName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dm5085]

That did the trick. Thank you so much!