total based on entry

[jarheadjim]

i have a table that has job title, but i need to seperate them into 2 groups and sum up the totals. The titles are id'd 1-9, 1-5 are one group and 6-9 are the second. So my ? is how do i get the query to sum up the total of people with TitleID=1-5 and a second total where TitleID=6-9? I'm sure i'm overcomplicating this, but it's got me puzzled.

 

[RichMorrison]

Jim,

Make 2 fields in a query, one for 1-5, one for 6-9.

In the first field cell, use an IIf function like this:
IIf([JobTitle] Between 1 and 5,something,0)
where "something" is whatever you want to Sum.

Make a second field for 6 to 9.

HTH,
RichM

 

[jarheadjim]

like this?

IIf([TitleID] Between 1 and 5,[worker]=+1,0)
IIf([TitleID] Between 6 and 9,[supervisor]=+1,0)

and will that work if the numbers are 1 or 5, or must it be between them?

 

[RichMorrison]

Jim,

Are you trying to "count" something ?

If you want to count workers and supervisors then

worker: IIf([TitleID] Between 1 and 5,1,0)
supervisor: IIf([TitleID] Between 6 and 9,1,0)

and use Sum in the totals query.

RichM

 

[jarheadjim]

yes, i'm trying to count them. I used what you last posted, but it doesn't return a total, it is extracting the appropriate records and assigning them "1" as a result. I tried using count, and sum and it doesn't seem to make a differance.

 

[RichMorrison]

Jim,

Here is the SQL view from a query I made.

It counts all the people with 1, 2, or 3 in a UserLevel field.

SELECT Sum(IIf([UserLevel]=1,1,0)) AS LevelOne, Sum(IIf([UserLevel]=2,1,0)) AS LevelTwo, Sum(IIf([UserLevel]=3,1,0)) AS LevelThree
FROM tblUserPPB;

If you are not getting a result, check the conditions carefully in the IIF function.

HTH,
RichM

 

[jarheadjim]

got it, thanks for the help Rich.