Report query


I am trying to create a query which should return 4 columns
Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
I would be glad to even have just 3 columns since, the Total can be computed
in the display table (ColdFusion interface)

Facility | NoOfActiveApplicants | NoOfArchivedApplicants

So far I have the following query, which returns just 2 rows with both
NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
Am stuck here, any help is apprecialted. Thanks in advance!

select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblapplicant a, tblJobDB b, tblfacilities c
where a.jobid = b.jobid
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.facility
union all
select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
where a.JobVacancyNumber = b.JobVacancyNumber
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.facility
order by facility
.

Relevant Pages

  • Re: Counting mulitple groups
    ... I don't understand what the query is attempting to accomplish and i, ... >> then the Devices that belong to each unit in that facility. ... >> used to test patients as well. ... >> count duplicates. ...
    (microsoft.public.access.queries)
  • Re: Report Not working
    ... Here is simple sql query which i did. ... FROM [Surgery by Facility Query 01] ... Jeff Boyce wrote: ...
    (microsoft.public.access.reports)
  • Returning partial rows with Linq
    ... How do I specify what columns to include in a Linq to Dataset query? ... A facility can have several stations. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Returning partial rows with Linq
    ... this dataset contains the results of a query of stations. ... The combination of customer, facility, and station uniquely identifies a station. ... What I want is, for a given customer, query the table and get a list of facilities. ... This means that if you create a linq query like you did above, you are simply traversing DataRow objects in a datatable and if you want to return DataRow objects, they too have to be part of a DataTable. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: adding database to another
    ... > originally set up for each facility. ... Queries are great! ... Data viewed through and written to a Query is just the same as ...
    (microsoft.public.access.tablesdbdesign)