-1

I am writing a query where I need to return a label based on the contents from several columns. Below are the column examples.

Source Table

What I need to return is for all accounts that only have a1 in the agency columns then the label of undesignated be assigned, where a1 is in one of the agency columns, and another agency name (ex a3) is in one if the other columns, then a label of partially designated would be assigned.

below would be the column where the results would go.

[resulting label][2]

The basic IIF or Switch function didn't seem to meet the needs given that there were multiple criteria to be met for each label.

Additional Table information. Additional Table information

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Don85203
  • 9
  • 3
  • Are agency1, agency2 and agency3 the only three columns to check for the a(x) values? – Sturgus May 16 '16 at 14:25
  • Thanks for taking the time to answer. There could be up to 20 columns. I added another image of the actual table headings and sample data from the table I'm using. – Don85203 May 17 '16 at 13:16
  • You'll find that this query becomes extremely difficult to create. This is solely because the data are modeled very poorly. If agency+accounts(+$Designated) were placed in an intersect table (many:many), then this problem would be trivial. What are the criteria for how many DX_Agency columns are there? You say it goes up to D20_Agency!? – Sturgus May 17 '16 at 13:37
  • Also, what determines which agency "a1" is? – Sturgus May 17 '16 at 13:39
  • The number of agency columns are determined by the donors. If donorA donates say $5000 and chooses to direct the funds to 10 different agencies the number of columns would be 10. Realistically the number of columns is usually around 4 or 5, but at times the number could go up to 20. The determination for the order of the agencies is determined by order the agency name is added. For instance, one donor may enter A1 Main Agency; A2 St.Jude, and another donor could enter A1 St. Jude; A2 Main Agency. They both would be labeled as partially designated. – Don85203 May 17 '16 at 14:19
  • If the number of agencies/columns 'varies', then I have to assume that your supplied "Source Table" isn't actually a table; it's a query output. Writing a query to accomplish what you want based on data from your actual source tables would likely be easier. If you must work with this "Source Table" that you've provided (that has variable columns), then I don't see any way to write a single query for it to accomplish what you want without creating an Access VBA function for it to loop through the similarly-named columns. – Sturgus May 17 '16 at 15:11
  • Thanks for taking the time to help. I'm thinking that the suggestion to un-pivot might be a solution. I think if I take that idea and create tables from the source that have the different labels - one table for undesignated, another for designated, etc. then I might be able to pull those labels back into the original query for the label on each account. – Don85203 May 17 '16 at 15:41

1 Answers1

0

To achieve the desired result in Access, I would try to un-pivot the first table and get the min and max of each agency as such:

SELECT A.[Account#], MIN(A.Agency) AS MinAgency, MAX(A.Agency) AS MaxAgency
FROM
(

    SELECT B.[Account#], B.Agency1 AS Agency
    FROM SourceTable AS B
    UNION
    SELECT C.[Account#], C.Agency2
    FROM SourceTable AS C
    UNION
    SELECT D.[Account#], D.Agency3
    FROM SourceTable AS D

) AS A
GROUP BY A.[Account#]

I might save this query as "MinMaxAgencyQuery" and then use it to create my "Letter Type" column. Then, I would build this column in a union query as follows:

SELECT E.[Account#], "Undesignated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN MinMaxAgencyQuery AS A ON E.[Account#] = A.[Account#]
WHERE MinAgency = "a1" AND MaxAgency = "a1"

UNION

SELECT E.[Account#], "PartiallyDesignated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN MinMaxAgencyQuery AS A ON E.[Account#] = A.[Account#]
WHERE MinAgency = "a1" AND MaxAgency <> "a1"

UNION

SELECT E.[Account#], "Designated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN MinMaxAgencyQuery AS A ON E.[Account#] = A.[Account#]
WHERE MinAgency > "a1"

Of course, this solution relies on "a1" actually being "a1" (and thus, less than the other agencies when doing a string-comparison).

If I needed to do this all in the same query, I would substitute my first query in each of the three unioned queries like so:

SELECT E.[Account#], "Undesignated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN (
SELECT A.[Account#], MIN(A.Agency) AS MinAgency, MAX(A.Agency) AS MaxAgency
    FROM
    (

        SELECT B.[Account#], B.Agency1 AS Agency
        FROM SourceTable AS B
        UNION
        SELECT C.[Account#], C.Agency2
        FROM SourceTable AS C
        UNION
        SELECT D.[Account#], D.Agency3
        FROM SourceTable AS D

    ) AS A
GROUP BY A.[Account#]

) AS F ON E.[Account#] = F.[Account#]
WHERE MinAgency = "a1" AND MaxAgency = "a1"

UNION

SELECT E.[Account#], "PartiallyDesignated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN (
SELECT A.[Account#], MIN(A.Agency) AS MinAgency, MAX(A.Agency) AS MaxAgency
    FROM
    (

        SELECT B.[Account#], B.Agency1 AS Agency
        FROM SourceTable AS B
        UNION
        SELECT C.[Account#], C.Agency2
        FROM SourceTable AS C
        UNION
        SELECT D.[Account#], D.Agency3
        FROM SourceTable AS D

    ) AS A
GROUP BY A.[Account#]

) AS F ON E.[Account#] = F.[Account#]
WHERE MinAgency = "a1" AND MaxAgency <> "a1"

UNION

SELECT E.[Account#], "Designated" AS [Letter Type]
FROM SourceTable AS E LEFT JOIN (
SELECT A.[Account#], MIN(A.Agency) AS MinAgency, MAX(A.Agency) AS MaxAgency
    FROM
    (

        SELECT B.[Account#], B.Agency1 AS Agency
        FROM SourceTable AS B
        UNION
        SELECT C.[Account#], C.Agency2
        FROM SourceTable AS C
        UNION
        SELECT D.[Account#], D.Agency3
        FROM SourceTable AS D

    ) AS A
GROUP BY A.[Account#]

) AS F ON E.[Account#] = F.[Account#]
WHERE MinAgency > "a1"

You never specified what becomes of account#'s that have no values for Agency1, Agency2, and Agency3, and so they would not be shown given this solution.

Sturgus
  • 666
  • 4
  • 18
  • If only MS Access supported Common Table Expressions, the last query would be much shorter (and execute faster)... – Sturgus May 16 '16 at 15:55