SQL Union results groupby smiliar to "LIKE"

Question

Using Access 2007, I am trying to run a query that outputs and organizes a long string of union queries into a list of data for a report. The report is something my supervisor passes to me and I want SQL just to do the leg work so I can export to an Excel file then copy paste. The issue:

The data I am using is imported from a separate Miscrosoft Excel from another department, and the clerk does not follow basic database rules and there is no hope of changing the system. The "LOSING_UNIT" column contains more than one example at times (i.e. some LOSING_UNITS are "A Trp" others are "A Trp B Trp C Trp" with no comma, semi-colon or any other form besides whitespace to determine the next Unit)

I need the queries to perform similar to a Like relation for the groupby if this is possible, so that any record containing "A Trp" becomes one total for Completed, one total for Remaining, etc. (currently my output comes as

Title                       LOSING_UNIT                                  Total
Completed Directives        A TRP 3-71 CAV                               12
Delinquent Directives       A TRP 3-71 CAV                                2
Missing GUIC                A TRP 3-71 CAV                                2
Remaining Directives        A TRP 3-71 CAV                                8
Missing GUIC                A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV   1
Remaining Directives        A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV   1

If this is not possible option 2: be able to orderby the length of the string so that all the solo LOSING_UNITS come out on top, with an alphabetical ORDERBY second. I have not been able to find a way to do this without generating a "Aggregate Function" error.

Select 'Completed Directives' AS Title, LOSING_UNIT, Count(PSD_ID) AS Total

FROM [Lateral_Transfers_Closed_Canceled_LINKED]

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT

UNION

SELECT 'Remaining Directives', LOSING_UNIT, COUNT(PSD_ID)

FROM [Lateral_Transfers_LINKED] AS LT

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT

UNION

SELECT 'Delinquent Directives' AS Title,LOSING_UNIT, Count(PSD_ID) AS TOTAL
FROM [Lateral_Transfers_LINKED] AS LT
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT

UNION

SELECT 'Missing GUIC', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT

UNION

SELECT 'Missing LUIC', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND LUIC Is Null
AND GUIC Is NOT Null
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT

UNION

SELECT 'On Hold', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT;

EDIT:

To be a bit more clear, here is how I have tried to adjust the SQL based off of other articles SQL Query - Using Order By in UNION

Select '' AS Title, LOSING_UNIT, '' AS Total
FROM(
Select 'Completed Directives', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_Closed_Canceled_LINKED]
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")

UNION

SELECT 'Remaining Directives', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")

UNION

SELECT 'Delinquent Directives' AS Title,LOSING_UNIT, Count(PSD_ID) AS TOTAL
FROM [Lateral_Transfers_LINKED] AS LT
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")

UNION

SELECT 'Missing GUIC', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null

UNION

SELECT 'Missing LUIC', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND LUIC Is Null
AND GUIC Is NOT Null

UNION 

SELECT 'On Hold', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT)

GROUP BY LOSING_UNIT
ORDER BY MID(LOSING_UNIT, 1, 5), LOSING_UNIT;

I did also attempt to put the aggregate into the first SELECT with no change in results.

EDIT:

A TRP 3-71 CAV
A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV
A TRP 3-71 CAV C TRP 3-71 CAV HHT 3-71 CAV
A TRP 3-71 CAV B TRP 3-71 CAV
A TRP 3-71 CAV C TRP 3-71 CAV
B TRP 3-71 CAV
B TRP 3-71 CAV A TRP 3-71 CAV
B TRP 3-71 CAV C TRP 3-71 CAV
C TRP 3-71 CAV B TRP 3-71 CAV HHT 3-71 CAV
C TRP 3-71 CAV *HHT 3-71 CAV
HHT 3-71 CAV A TRP 3-71 CAV C TRP 3-71 CAV
HHT 3-71 CAV A TRP 3-71 CAV
HHT 3-71 CAV A TRP 3-71 CAV B TRP 3-71 CAV C TRP 3-71 CAV
HHT 3-71 CAV C TRP 3-71 CAV

The Final Solution

Select 'Completed Directives' AS Title, ValuesToFind.LOSING_UNIT, Count(PSD_ID) AS Total

FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

UNION

SELECT 'Remaining Directives', ValuesToFind.LOSING_UNIT, COUNT(PSD_ID)

FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

UNION

SELECT 'Delinquent Directives' AS Title, ValuesToFind.LOSING_UNIT, Count(PSD_ID) AS TOTAL

FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
       on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

UNION

SELECT 'Missing GUIC', ValuesToFind.LOSING_UNIT, Count(PSD_ID)

FROM 
   [Lateral_Transfers_LINKED] AS LT
   left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null

GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT


UNION

SELECT 'On Hold', ValuesToFind.LOSING_UNIT, Count(PSD_ID)

FROM 
    [Lateral_Transfers_LINKED] AS LT
    LEFT JOIN [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'

WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

Many thanks to Kyle

Is your sole problem the separation of Trps (or whatever they are)? Then I would suggest to have two parts of your program: one to create a sanitized table (which you then can correct by hand if necessary) and the other that generates the output. - Are the LOSING UNITS a limited amount of types (CAV, TRP...) and are they always specified? Could you make examples of as many as possible LOSING UNITS-lines as possible? — , May 29 '14 at 16:16
The best solution, which I think may be impossible without actually designing a database that follows standard rules, would be to have the COUNT or SUM functions grab everything that contained "A TRP" for example Second option would be simply order the results so that any result containing a single "Trp" is placed at the top (A, B, C, HHT are the only 4 "types" of Trp) Any combination of LOSING UNITS is possible, with no order standard (i.e **A Trp B Trp C Trp** or **C Trp B Trp A Trp** are both in the Excel) The only piece that changes is the A, B, C, or HHT. Trp CAV always exists. Helps? — Gabriel C., May 29 '14 at 16:21
Added each "type" of LOSING UNIT line currently existing. They do change because each new Excel file adds/deletes rows. — Gabriel C., May 29 '14 at 16:32
I think you would be better off in excel (since you get the tabels in excel and want the output in excel anyways). Anyway: in VBA this should not be a problem. (use SPLIT(fieldvalue,"TRP 3-71 CAV") for example. you get an array of A, B or Cs which you can scan and count. — , May 29 '14 at 16:39

Kyle Hale · Accepted Answer · 2014-05-29T19:29:38.387

Create a table with one field containing all the values you want to "break out" from the LOSING_UNIT column. Let's call it ValuesToFind.

Then your query looks like so:

select Count(PSD_ID), ValuesToFind.Value from 
[Lateral_Transfers_LINKED] LT
left join ValuesToFind
on LT.LOSING_UNIT LIKE '*' + ValuesToFind.Value + '*'
GROUP BY 
ValuesToFind.Value

Obviously adding whatever filters you'd like to limit the data.

Also, I would recommend using a Switch function to decode your various fields into your Title column so you can control that mapping logic in one place. So something like

  select 
    ValuesToFind.Value,
    Count(PSD_ID), 
    Switch(STATUS = "Complete", 'Completed Directives',
LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE(), 'Delinquent Directives',
GUIC Is Null OR GUIC = "Not Activated", 'Missing GUIC',
LUIC Is Null AND GUIC Is NOT Null, 'Missing LUIC',
LT.SERIAL_NUMBER  LIKE "Temp. Hold", 'On Hold',
1 =1, 'Remaining Directives') as Title
    from 
    [Lateral_Transfers_LINKED] LT
    left join ValuesToFind
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.Value + '*'
    GROUP BY 
        ValuesToFind.Value,
Switch(STATUS = "Complete", 'Completed Directives',
LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE(), 'Delinquent Directives',
GUIC Is Null OR GUIC = "Not Activated", 'Missing GUIC',
LUIC Is Null AND GUIC Is NOT Null, 'Missing LUIC',
LT.SERIAL_NUMBER  LIKE "Temp. Hold", 'On Hold',
1 =1, 'Remaining Directives')

UPDATE: I see your Completed Directives come from another table, it's up to you whether to UNION your two tables together and then perform this aggregation or perform each aggregation on each table individually and then UNION just the results.

I believe I understand what you are doing, and I really like the SWITCH() idea; it cleans the code up quite a bit. However, the only way I am getting a result is if the ValuesToFind table contains the exact characters as LOSING_UNIT; putting in just "A TRP" does not return a result. I am guessing this is because there is no way to put a wildard after ValuesToFind.Value? — Gabriel C., May 29 '14 at 18:59
Works perfectly! I didn't realize you could use '*' on either side of a column name that way to make wildcards work. I realized that I had an oversight about how SWITCH() works, but now I see that it is not going to work because the other expressions, **GUIC Is Null** for example, can be true of Delinquent directives, Remaining Directives, and Completed Directives. Back to the obnoxious numbers of UNIONS. — Gabriel C., May 29 '14 at 20:36

SQL Union results groupby smiliar to "LIKE"

1 Answers1