Counting A Bit Field

[lars7]

Hi Guy's,

I'm trying to change my Access query into a Server View but I am having trouble counting my Yes/No field that has become a bit field [RevReq].

This is what I have tried but [RevReq] gives me the same count as the [PayNumber] field which is counting the records in the table:


SELECT TOP (100) PERCENT StaffListSQL.dbo.StaffList.Level1, StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline AS Disc,
COUNT(dbo.tblReviews.PayNumber) AS Staff, COUNT(dbo.tblReviews.RevReq) AS Rev, COUNT(dbo.tblReviews.AppStatus) AS RevApp
FROM dbo.tblReviews RIGHT OUTER JOIN
StaffListSQL.dbo.StaffList ON dbo.tblReviews.PayNumber = StaffListSQL.dbo.StaffList.[Pay Number]
GROUP BY StaffListSQL.dbo.StaffList.[Group], StaffListSQL.dbo.StaffList.Discipline, StaffListSQL.dbo.StaffList.Level1
ORDER BY StaffListSQL.dbo.StaffList.Level1

Here is what is returned:

Acute Clyde Acute Services PTB 16 16 0
Acute Clyde Acute Services S&P 0 0 0
Acute Diagnostic Services A&C 16 16 0
Acute Diagnostic Services AFC 15 15 0
Acute Diagnostic Services AHP 482 482 0
Acute Diagnostic Services ASC 338 338 0
Acute Diagnostic Services MAINT 1 1 0
Acute Diagnostic Services MED 0 0 0
Acute Diagnostic Services MISC 0 0 0
Acute Diagnostic Services NURSE - Q 765 765 0
Acute Diagnostic Services NURSE - U 62 62 0
Acute Diagnostic Services PTB 85 85 0
Acute Diagnostic Services S&P 10 10 0
Acute Emergency Care & Medical Specialties NULL 1 1 0
Acute Emergency Care & Medical Specialties A&C 48 48 0

Hope this makes sense and thanks in advance.

 

[lars7]

Sorry think this is the wrong forum :~/

Moved to thread183-1435386