Cross tab query with boolean fields as row headers.

Question

I currently have a table structure that looks something like this(some details omitted):

  ColumnName         || Type
  Date_Of_Job           DateTime
  Reparied_Service      Boolean 
  Disconnect_Service    Boolean 
  Relayed_Service       Boolean 
  Reparied_Stopcock     Boolean 
  Replaced_Stopcock     Boolean 
  TPFNR_Repaired        Boolean 
  TPFNR_Replaced        Boolean 
  TPFNR_Capped          Boolean 
  Poor_Pressure_Tested  Boolean 
  Flow_Test             Boolean 
  Meter_replaced        Boolean

What I want to be able to show is a cross tab query with the month(*Date_Of_Job*) as the column headers and the different types of jobs as the row headers with the count of each job for that month.

So something like this:

==Job Type===========01=====02=====03 etc
Reparied_Service     5      20     30
Disconnect_Service   15     45      9
Relayed_Service      18      7      6
Reparied_Stopcock    18 
Replaced_Stopcock    20 
 etc

The problem that I have is that the each job type is stored as a boolean column because each record can included multiple job type. For example you can log a *Reparied_Stopcock* job as well as a *Flow_Test* for the one record.

Does anyone know how I can do this in access or MS SQL, it doesn't really matter which one.

Answer 1

Assuming Date_Of_Job values are from the same year, you can base an Access cross tab query on a UNION subselect.

TRANSFORM Count(q.[job_type]) AS num_jobs
SELECT q.[job_type]
FROM (
    SELECT
        Month(Date_Of_Job) AS job_month
        , "Reparied_Service" AS job_type
    FROM Jobs
    WHERE Reparied_Service=True
    UNION ALL
    SELECT
        Month(Date_Of_Job) AS job_month
        , "Disconnect_Service" AS job_type
    FROM Jobs
    WHERE Disconnect_Service=True
    ) AS q
GROUP BY q.[job_type]
PIVOT q.[job_month];