Merging Two Crosstabs with Dynamic Row Headers into one

Question

Using MS Access I have two cross tab queries summarizing my data. One gets me the total count of the rows of data by location and fiscal quarter; the other query gets me the percent that meet a criteria by location and fiscal quarter.

Examples below (they are simplified for the sake of the question).

qryA: original summary table

Location    Period      CountIt    
Blue        FY13-Q3     Yes
Orange      FY13-Q1     No
Blue        FY14-Q1     No
Orange      FY13-Q1     Yes

I then have one cross tab that is getting the % of Yes values from the CountIt column and another that displays the total count for each fiscal quarter.

crossstab #1 - percentages

TRANSFORM Sum(IIf(qryA.CountIt Like "Yes",1,0))/Count(qryA.CountIt) AS PercentYes
SELECT qryA.[Location]
FROM qryA
GROUP BY qryA.[Location]
PIVOT qryA.[Period];

## Crosstab Output
Location    FY13-Q1 FY13-Q2 FY13-Q3 FY13-Q4 FY14-Q1
Blue        12%     15%     13.2%   19%     15%
Orange      9%      12%     1%      18%     12%

crossstab #2 - count it

TRANSFORM Count(qryA.CountIt) AS FiscalCount
SELECT qryA.[Location]
FROM qryA
GROUP BY qryA.[Location]
PIVOT qryA.[Period];

## Crosstab that display total count by location and Fiscal quarter
Location    FY13-Q1 FY13-Q2 FY13-Q3 FY13-Q4 FY14-Q1
Blue        102     111     54      124     122
Orange      91      321     122     158     129

desired final output

I want to createa a combined table that shows the % of Yes and the total count

            FY13-Q1     FY13-Q2     FY13-Q3     FY13-Q4     FY14-Q1
Location    Per%    Tot Per%    Tot Per%    Tot Per%    Tot Per%    Tot
Blue        12%     102 15%     111 13.2%   54  19%     124 15%     122
Orange      9%      91  12%     321 1%      122 18%     158 12%     129

Is this possible to do? As you can see, my row headers are fiscal periods and, as I add more data, I will be getting more rows (they are dynamic) ... I'm not sure how to union these into the desire result.

Thanks!

Do you have access to the original queries that have generated the crosstab queries, or these two crosstab queries are in the form of tables? If the former then you can build a new crosstab query based on the combined syntax of the queries (with a small trick). Please modify your question and provide the syntax of your two original queries and I will help — cha, Jan 23 '14 at 23:23

Fionnuala · Accepted Answer · 2014-01-24T16:24:32.100

As FY13-Q1,FY13-Q2,FY13-Q3,FY13-Q4,FY14-Q1 appears to be a comprehensive list, you can use IN

TRANSFORM Sum(aTable.aNumber) AS SumOfaNumber
SELECT aTable.aText, Sum(aTable.aNumber) AS [Total Of aNumber]
FROM aTable
GROUP BY aTable.aText
PIVOT "FY" & Format(ADate,"yy") & "-Q" & Format([ADate],"q")
  In ("FY13-Q1","FY13-Q2","FY13-Q3","FY13-Q4","FY14-Q1");

You can add the headers using the Column Header property in the query design window, or simply type in the SQL view window.

Note that if you omit a value form IN, the column will not show, even if there is data.

If you build the queries in VBA, you can change the headers list to suit the quarters you wish to use, or else omit the formatting (FY13-01 etc), so MS Access returns Qtr 1, Qtr 2 etc.

You can then join the data in which ever way suits:

SELECT atable_crosstab.[FY13-Q1],
       atable_crosstab2.[FY13-Q1] AS [Per Q1],
       atable_crosstab.[FY13-Q2],
       atable_crosstab2.[FY13-Q2] AS [Per Q2],
       atable_crosstab.[FY13-Q3],
       atable_crosstab2.[FY13-Q3] AS [Per Q3],
       atable_crosstab.[FY13-Q4],
       atable_crosstab2.[FY13-Q4] AS [Per Q4]
FROM   atable_crosstab
       INNER JOIN atable_crosstab2
           ON atable_crosstab.atext = atable_crosstab2.atext;

However, you will not be able to have a header that runs across two columns.

Here is a sketch in VBA

Sub BuildCrosstab()
Dim sBase As String
Dim iQtr, iYear, iStartYear, iEndYear
Dim sIn As String
Dim CurDate As Date
Dim qdf As QueryDef

sBase = "TRANSFORM Sum(aTable.aNumber) AS SumOfaNumber " _
& "SELECT aTable.aText, Sum(aTable.aNumber) AS [Total Of aNumber] " _
& "FROM aTable " _
& "GROUP BY aTable.aText " _
& "PIVOT ""FY"" & Format(ADate,""yy"") & ""-Q"" & Format([ADate],""q"") In "

iStartYear = Year(Date) - 1
iEndYear = Year(Date)

For iYear = iStartYear To iEndYear
    CurDate = DateSerial(iYear, 1, 1)
    For iQtr = 1 To 4
        If CurDate <= Date Then
            sIn = sIn & ",""" & "FY" & Format(CurDate, "yy") & "-Q" _
                & Format(CurDate, "q") & """"
            CurDate = DateAdd("q", 1, CurDate)
        End If
    Next
Next

sIn = "(" & Mid(sIn, 2) & ")"

''This query exists
Set qdf = CurrentDb.QueryDefs("ATable_Crosstab")
qdf.SQL = sBase & sIn

End Sub

Thanks for the post. My `FYXX-QX` are *not* a comprehensive list ... as I mentioned at the bottom of the post ... "as I add more data, I will be getting more" ... I was hoping for a solution that will not require me to hard code the FY periods. I will experiment with yours. — thornomad, Jan 24 '14 at 15:12
As I mention above, you can build the sql string in VBA and update the QueryDef SQL, which will allow you to change the quarters according to data selected by the user or other criteria that work for you. — Fionnuala, Jan 24 '14 at 15:18
@thornomad - okay, I can look at a VBA solution if there isn't a purely SQL answer. Thanks. — thornomad, Jan 24 '14 at 15:40

Merging Two Crosstabs with Dynamic Row Headers into one

qryA: original summary table

crossstab #1 - percentages

crossstab #2 - count it

desired final output

1 Answers1