Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    61

    Red face Unanswered: How would I convert this Access Query to a SQL View

    How would I convert this Access Query so that it runs as SQL view:


    SELECT UDTEXT24C
    Count(IIf([EMP_TYPE]="Sworn",[CURRENT_BADGE_NO])) AS UMOS,
    Count(IIf([EMP_TYPE]="Non sworn",[CURRENT_BADGE_NO])) AS CMOS,
    Count(CURRENT_BADGE_NO) AS TotMOS
    FROM dbo_OFFICERS

    I tried the CASE WHEN statement but apparently I can't combine the three count statements into the same view. I can only replicate the output of above Access query by using creating four SQL views. One view for each Count statement and the forth to combine them together.
    Last edited by Chumpie999typla; 05-13-03 at 20:02.

  2. #2
    Join Date
    Oct 2002
    Posts
    61

    Re: How would I convert this Access Query to a SQL View

    I figured it out, here is my solution:

    SELECT UDTEXT24C,
    Count(case when EMP_TYPE = 'Sworn' then '1' else null end) as UMOS,
    Count(case when EMP_TYPE <> 'Sworn' then '1' else null end) as CMOS,
    Count([EMP_TYPE]) as Total
    FROM dbo.OFFICERS


    Any comments on my code?

  3. #3
    Join Date
    Feb 2003
    Posts
    109

    in order to do a view

    in order to do a view you cant do the case when then statement

    do something like this:

    SELECT UDTEXT24C,
    (select Count(*) From dbo.Officers subQ where = subQ.EMP_TYPE = 'Sworn' AND subQ.UDText24C = Officers.UDText24C) as UMOS,
    (select Count(*) From dbo.Officers subQ where = subQ.EMP_TYPE <> 'Sworn' AND subQ.UDText24C = Officers.UDText24C) as CMOS,
    (select Count(*) From Officers) as Total
    FROM dbo.OFFICERS
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •