Convert columns to rows and get the sum of checkboxes in Access

Question

I have an Access database that has checkboxes in on of the tables. I need to get a Sum of the checkboxes but, I want to convert the checkbox columns into rows instead of columns.

Here is an example of how it appears in the table:

 Name         DND  Other  CAP   SAP   DAP   EXP
Person One    -1    -1    NULL  NULL  -1    -1
Person Two    NULL  -1    -1     -1   NULL  NULL
Person Three  NULL  -1    -1    NULL  NULL  NUll

Here is how I want the report to display the data:

 Discharge  Count
    DND        1
    Other      3
    CAP        2
    SAP        1
    DAP        1
    EXP        1

I tried using a Case statement but, it didn't come out correctly

score 1 · Answer 1 · answered Nov 06 '20 at 17:44

SELECT 'DND' AS Discharge, COUNT(*) AS [Count] FROM tbl WHERE DND = -1
UNION ALL
SELECT 'Other' AS Discharge, COUNT(*) AS [Count] FROM tbl WHERE Other = -1
UNION ALL

etc.

I don't think there is an easier way.

This is air code, not sure if you need a GROUP BY clause.

Compare How to simulate UNPIVOT in Access?

score 1 · Accepted Answer · answered Nov 06 '20 at 17:56

Use a union query and check for Null:

Select 
    T.Discharge, Abs(Sum(T.[Count])) As DischargeCount
From 
    (Select "DND" As Discharge, [DND] Is Not Null As [Count] From YourTable
    Union ALL
    Select "Other" As Discharge, [Other] Is Not Null As [Count] From YourTable
    Union ALL
    Select "CAP" As Discharge, [CAP] Is Not Null As [Count] From YourTable
    Union ALL
    Select "SAP" As Discharge, [SAP] Is Not Null As [Count] From YourTable
    Union ALL
    Select "DAP" As Discharge, [DAP] Is Not Null As [Count] From YourTable
    Union ALL
    Select "EXP" As Discharge, [EXP] Is Not Null As [Count] From YourTable) As T
Group By
    T.Discharge

Thanks. Both were helpful but, this was even better. – tnlewis Nov 06 '20 at 20:00 — tnlewis, Nov 06 '20 at 20:00

Convert columns to rows and get the sum of checkboxes in Access

2 Answers2