SUM variable values in a common field

[meltingpot]

As a beginer,Im trying to SUM values in a single field where they meet the critria below.

The field is wish to SUM is "SERVICELEVEL"
I wish to SUM the following
addressbook.ServiceLevel='Officer' +
addressbook.ServiceLevel='Senior Ranks' +
addressbook.ServiceLevel='Junior Ranks' = x

The critiria
SELECT *
FROM Courselist INNER JOIN (addressbook INNER JOIN Applications ON addressbook.PersonID = Applications.PersonID) ON Courselist.CourseID = Applications.CourseID
WHERE addressbook.Service='RN' AND addressbook.ServiceLevel='Officer' AND(Applications.StatusCode='Passed' OR Applications.StatusCode='Failed') AND courselist.CourseTitle=coursename AND courselist.EndDate BETWEEN enddate1 AND enddate2

Im a bit stummped

 

[r937]

if servicelevel is a string like 'Officer' you won't be able to add it

perhaps you meant count it instead?

r937.com | rudy.ca

 

[meltingpot]

Hi ... OK,I think ???

I think I want to add up (COUNT) all of the entries for 'Officer' and 'Snr Ranks' and have a total of all of them together.

So I guess Ill have to count all the entries for each of the variables 'officer' = 12 (COUNT) + 'Snr Ranks'=4 (count)
so the total would be 16 . Does that make any sense ?
CHEERS

 

[r937]

SELECT COUNT(*) AS my_count
  FROM addressbook
 WHERE ServiceLevel IN ( 'Officer'
                       , 'Senior Ranks'
                       , 'Junior Ranks' )

r937.com | rudy.ca

 

[meltingpot]

Cheers r937
Can I add that to my other critiria ..

i.e.

WHERE addressbook.Service='RN' AND addressbook.ServiceLevel='Officer' AND(Applications.StatusCode='Passed' OR Applications.StatusCode='Failed') AND courselist.CourseTitle=coursename AND courselist.EndDate BETWEEN enddate1 AND enddate2

coursename, enddate1 and enddate2 are Request.QueryStrings from an .ASP web page on our intranet

Cheers

 

[r937]

yes, i think you can

r937.com | rudy.ca

 

[PHV]

AND addressbook.ServiceLevel='Officer'
How do you expect to be able to count the 'Snr Ranks' ?

 

[meltingpot]

Thats just and example, ive removed that line from the WHERE clause ....

So infact it reads thus

WHERE addressbook.Service='RN' AND(Applications.StatusCode='Passed' OR Applications.StatusCode='Failed') AND courselist.CourseTitle=coursename AND courselist.EndDate BETWEEN enddate1 AND enddate2

coursename enddate1 enddate2 are Strings from an .asp form that sends the query ...

 

[dhookom]

I would step back and create a table/field (data) that stores something that says 'Officer' 'Senior Ranks' 'Junior Ranks' have something in common.

For instance rather than querying for "Twins", "White Sox", "Indians", "Royals", and "Tigers", you should have these grouped using data into the "American League Central".

Duane
Hook'D on Access
MS Access MVP

 

[meltingpot]

Hi...

Im not sure what you mean dhookom - I think its all already there in the table structure. They are already grouped by addressbook.Service='RN'

The query joins 3 tables

1.tbl.addresssbook (stores the name of the person and their rank)

2.tbl.applications (the PersonID field from tbl.addressbook is in a realationship with this table and links a name with an application)

3.tbl.courslist (this table contains the name of the course that has been applied for in the tbl.applications and is in a realationship)

I think its possible to nest the Select function say:

SELECT addressbook.servicelevel, (SELECT COUNT(addressbook.servicelevel) as officers Where addressbook.ServiceLevel='Officer'),
(SELECT COUNT(addressbook.servicelevel) as officers Where addressbook.ServiceLevel='Snr Rank'),
(SELECT COUNT(addressbook.servicelevel) as officers Where addressbook.ServiceLevel='Jnr Rank'),
GROUP BY addressbook.ServiceLevel

Im not sure the above will work but ive seen something like this in the past, the only trouble is the results need to be summeed together

Thanks for your help

Help !

 

[dhookom]

Are you suggesting that if Service='RN' than ServiceLevel must be 'Officer' or 'Snr Rank' or 'Jnr Rank'?

I'm not sure we understand what you are asking for. Perhaps if you provide a small sample of records and desired display in your query it would help.

Duane
Hook'D on Access
MS Access MVP

 

[meltingpot]

Your so right ... All I have to do is COUNT the 'service' field with the Pass or faild critiria and group them together ... IM AN IDIOT AND MADE MYSELF A PROBLEM.

TAXI !!!

Any many thanks for helping this fool out - some time one cannot see for looking !