I have an Excel worksheet that shows counts of 11 Bins (column ranges) – see image – that result from one Access data set. Instead of linking from Excel, I want to do this in Access. I can create individual queries – no problem. I have created a Crosstab query for each of the 11 Bins as well. The image is of 1 of the CT queries. To get a result like the Excel result I have to then design a second query with the 11 CT results, then add each of those “rows” as a separate field. I just know that running nested queries in a query is not the right approach. While it works, it is incredibly inefficient and consumes a lot of resources. I’m certain there is a SQL solution – not UNION – where the script will perform each of the 11 crosstabs using the Bin criteria, then creating the array. I’m great at Excel and good at Access VBA, but not much experience in SQL. Please help (and be patient).
9
Consider conditional aggregation (portable to other databases) by converting your WHERE calculated condition into a calculated SELECT column using IIF() to flag the data that is then summed to count the flags. You can also include other aggregates like the count of records.
SELECT
CURRENT_STATE AS State,
COUNT(CREDID) AS Volume,
SUM(IIF([Days Ac] BETWEEN 0 AND 8, 1, 0)) AS [0 - 8 Days Ac],
SUM(IIF([Days Ac] BETWEEN 9 AND 15, 1, 0)) AS [9 - 15 Days Ac],
SUM(IIF([Days Ac] BETWEEN 16 AND 20, 1, 0)) AS [16 - 20 Days Ac],
SUM(IIF([Days Ac] BETWEEN 21 AND 25, 1, 0)) AS [21 - 25 Days Ac],
SUM(IIF([Days Ac] BETWEEN 26 AND 30, 1, 0)) AS [26 - 30 Days Ac],
SUM(IIF([Days Ac] BETWEEN 31 AND 40, 1, 0)) AS [31 - 40 Days Ac],
SUM(IIF([Days Ac] BETWEEN 41 AND 60, 1, 0)) AS [41 - 60 Days Ac],
SUM(IIF([Days Ac] BETWEEN 61 AND 90, 1, 0)) AS [61 - 90 Days Ac],
SUM(IIF([Days Ac] BETWEEN 91 AND 110, 1, 0)) AS [91 - 110 Days Ac],
SUM(IIF([Days Ac] BETWEEN 111 AND 130, 1, 0)) AS [111 - 130 Days Ac],
SUM(IIF([Days Ac] > 130, 1, 0)) AS [>130 Days Ac]
SUM(IIF([Days Ac] IS NOT NULL, 1, 0) AS [CheckValue]
FROM (
SELECT
CURRENT_STATE,
CREDID,
Date() - [last_state_changed] + [nettime] AS [Days Ac]
FROM tblBreakOut
) sub
GROUP BY CURRENT_STATE
However, MS Access’ crosstab can still work if you calculate the bins beforehand using nested IIF(). See how string values of calculated column in inner query become pivoted columns in outer query.
TRANSFORM COUNT(CREDID) AS [Count]
SELECT
CURRENT_STATE AS [State],
COUNT(CREDID) AS [Volume]
FROM (
SELECT
CURRENT_STATE,
CREDID,
Date() - [last_state_changed] + [nettime] AS [Days Ac],
IIF([Days Ac] BETWEEN 0 AND 8, '0 - 8 Days Ac',
IIF([Days Ac] BETWEEN 9 AND 15,'9 - 15 Days Ac',
IIF([Days Ac] BETWEEN 16 AND 20, '16 - 20 Days Ac',
IIF([Days Ac] BETWEEN 21 AND 25, '21 - 25 Days Ac',
IIF([Days Ac] BETWEEN 26 AND 30, '26 - 30 Days Ac',
IIF([Days Ac] BETWEEN 31 AND 40, '31 - 40 Days Ac',
IIF([Days Ac] BETWEEN 41 AND 60, '41 - 60 Days Ac',
IIF([Days Ac] BETWEEN 61 AND 90, '61 - 90 Days Ac',
IIF([Days Ac] BETWEEN 91 AND 110, '91 - 110 Days Ac',
IIF([Days Ac] BETWEEN 111 AND 130, '111 - 130 Days Ac',
IIF([Days Ac] > 130, '>130 Days Ac',
IIF([Days Ac] IS NOT NULL, 'CheckValue', NULL)
)
)
)
)
)
)
)
)
)
)
) AS [Days Ac Bin]
FROM tblBreakOut
) sub
GROUP BY CURRENT_STATE
PIVOT [Days Ac Bin] IN (
[0 - 8 Days Ac],
[9 - 15 Days Ac],
[16 - 20 Days Ac],
[21 - 25 Days Ac],
[26 - 30 Days Ac],
[31 - 40 Days Ac],
[41 - 60 Days Ac],
[61 - 90 Days Ac],
[91 - 110 Days Ac],
[111 - 130 Days Ac],
[>130 Days Ac],
[CheckValue]
)
Notice how Access allows aliased columns in other expressions within same SELECT for compact writing (one rare perk of Access SQL).
4