Complex SQL IF Then, Else Statement

I'm sorry, I don't understand the question.  Are

GS-5 Annual 3 for FY 2019, GS-7 Annual 1 for FY2020, GS-9 Annual 1for FY2021, GS-9 Annual 2 for FY2022, GS-9 Annual 3 for FY2023, and GS-9 Annual 4 for FY2024

Column names?  or are they data values?


Is -- GS-5/7/9  -- a different table?


1. Column and table names should NEVER contain data  You will have a lifetime employment changing the entire application every year.  You should probably start by rethinking the application and normalizing the tables.  If you don't hate Access already, you certainly will next year.
2. You have a repeating group in the table.  Grade should exist only once.  Instead of 4 columns, you should have 4 rows.  Your query will select the range of years if you want a rolling four year selection.  If you want to pivot the years, you can use a crosstab query to put all the years on a single row for reporting purposes.
3. Object names should not include spaces or special characters.  Use only the upper and lower case letters, the numbers, and the underscore.
4. Always give your form controls meaningful names.  You aren't going to know what combo117 is next week, let alone next year and no one who comes after you will have a clue.

I understand your confusion.  The column names provide numbers in a query that is generated in ComboBox 117, which I can change the name later.  I need to pull a query that selects grade based on each year from ComboBox 137.  The problem is that the data that is provided by OPM does not allow for multiple grade positions.  So if I pick GS-5/7/9, then no data is pull from the OPM Table.  I need the query to pull the following columns based on the criteria listed below:
write a query that selects GS-5 Annual 3 for FY 2019, GS-7 Annual 1 for FY2020, GS-9 Annual 1for FY2021, GS-9 Annual 2 for FY2022, GS-9 Annual 3 for FY2023, and GS-9 Annual 4 for FY2024.  Does this help answer your questions?

Thanks,
Terry

Not much clearer.  I still can't tell data from column names.  So, I'll provide syntax rather than something you can actually use.

Select IIf(Something = "xxx", Field1, Field2) As FY2019,  IIf(Something = "xxx", Field3, Field4) As FY2020, ....
From YourTable;

which I can change the name later

Yes you can but no one does because it is a PITA.  If you give it a good name BEFORE you use it, you won't have to later find all the places you used it including code and queries and forms and reports to fix the name.

I tried to do what you told me but I keep getting an error.  Do you know what could be causing the issue?  I am getting the error AS FY2019.


 

SELECT OPM_Table.LOCNAME, LocationID.LocationDescription,'5/7/9','GS-5/7/9', IIF(OPM_Table.GRADE2='GS-5',(FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2) AS FY2019, IIF(OPM_Table.GRADE2='GS-7',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2020],2) AS FY2020, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2021],2) AS FY2021, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL2*[PayInflation]![2022],2) AS FY2022, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2023],2) AS FY2023, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2024],2) AS FY2024
FROM PayInflation, LocationID INNER JOIN OPM_Table ON LocationID.LOCNAME = OPM_Table.LOCNAME
WHERE (((LocationID.LocationDescription)=Forms![Fee Review Summary]!Combo117) And ((Forms![Fee Review Summary]!Combo137='GS-5/7/9'));
Else
SELECT OPM_Table.LOCNAME, LocationID.LocationDescription, OPM_Table.GRADE, OPM_Table.GRADE2, FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2) AS FY2019, FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2020],2) AS FY2020, FormatCurrency([FY2020]*[PayInflation]![2021],2) AS FY2021, FormatCurrency(OPM_Table.ANNUAL5*[PayInflation]![2022],2) AS FY2022, FormatCurrency([FY2022]*[PayInflation]![2023],2) AS FY2023, FormatCurrency(OPM_Table.ANNUAL6*[PayInflation]![2024],2) AS FY2024
FROM PayInflation, LocationID INNER JOIN OPM_Table ON LocationID.LOCNAME = OPM_Table.LOCNAME
WHERE (((LocationID.LocationDescription)=Forms![Fee Review Summary]!Combo117) And ((OPM_Table.GRADE2)=Forms![Fee Review Summary]!Combo137));

Select allOpen in new window

Thanks,
Terry

You embedded the AS xxxx inside the IIf().  Look again at my syntax example.

Do I need to include the false statement in the attached code?  Could you show me how I can write this so that it would work?

IIF(OPM_Table.GRADE2='GS-5',(FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2) AS FY2019

Select allOpen in new window

Thanks,
Terry

Do I need to include the false statement in the attached code?

Yes.  That's the point of the IIf(), isn't it?  In one condition you want to pick up the value from fld1 but in another you want to pick up the value from fld2.  This allows you to not adversely affect the downstream process.  It doesn't matter where FY2019 came from.

I took out the IIF statement because I decided to change my process to where instead of IF.  The issue I have is that for FY2019 the Grade2 should be a GS-5, and in FY2019 it needs to be a GS-7 and in FY2020-2024 it needs to be a GS-9, but I don't know how to do a where clause based on a formula.  The table that I have does not allow me to combine Grades so can you provide me any ideas on how to do where clause based on a formula?

SELECT OPM_Table.LOCNAME, LocationID.LocationDescription, '5/7/9' AS GRADE, 'GS-5/7/9' AS GRADE2, (FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2)) AS FY2019, IIF(OPM_Table.GRADE2='GS-7',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2020],2)) AS FY2020, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2021],2)) AS FY2021, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL2*[PayInflation]![2022],2)) AS FY2022, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2023],2)) AS FY2023, IIF(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2024],2)) AS FY2024
FROM PayInflation, LocationID INNER JOIN OPM_Table ON LocationID.LOCNAME = OPM_Table.LOCNAME
WHERE (((LocationID.LocationDescription)=Forms![Fee Review Summary]!Combo117) And ((Forms![Fee Review Summary]!Combo137='GS-5/7/9' AND GRADE2='GS-5')));

Select allOpen in new window

Try normalizing your tables.  NO object name should contain data and your's have nothing but data.   All the names you used are meaningless.  I can't tell whether you are attempting to reference columns or use column contents.  Posting the schema might help.  Include a description in words of what you are trying to accomplish.

Let me try to describe what I am doing better.

ComboBox 117 is the Location (OPM GS Payscale) drop down on the Fee Review Summary Form and Combox 137 is the Grade on the same form.  The SQL works for all positions except for multiple grades such as: GS-5/7/9, GS-9/11/12, ISO-1  GS-7/9, ISO-2 GS-11/12, and SISO GS-13/14.  The reason the query will not pull these pay cost is that the table does not breakout grades by this format.  The assumption is that all positions start at a Step 3, which is Annual3 in the OPM_Table.  Let me walk you through what query would select from the OPM_Table for the items that do not work.  It would be:
GS-5/7/9
GS-5 Annual3 for FY2019,  GS-7 Annual1 in FY2020, GS-9 Annual1 in FY2021, GS-9 Annual2 in FY2022, GS-9 Annual3 in FY2023 and GS-9 Annual4 in FY2024.
GS-9/11/12
GS-9 Annual3 for FY2019,  GS-11 Annual1 in FY2020, GS-12 Annual1 in FY2021, GS-12 Annual2 in FY2022, GS-12 Annual3 in FY2023 and GS-12 Annual4 in FY2024.
ISO-1  GS-7/9
GS-7 Annual3 for FY2019,  GS-9 Annual1 in FY2020, GS-9 Annual2 in FY2021, GS-9 Annual3 in FY2022, GS-9 Annual4 in FY2023 and GS-9 Annual4 in FY2024.
ISO-2  GS-11/12
GS-11 Annual3 for FY2019,  GS-12 Annual1 in FY2020, GS-12 Annual2 in FY2021, GS-12 Annual3 in FY2022, GS-12 Annual4 in FY2023 and GS-12 Annual4 in FY2024.
SISO GS-13/14
GS-13 Annual3 for FY2019,  GS-13 Annual1 in FY2020, GS-13 Annual2 in FY2021, GS-13 Annual3 in FY2022, GS-13 Annual4 in FY2023 and GS-13 Annual4 in FY2024.
 I also have a table that include inflation that is multiplied by the annual salary to reflect projected costs.  Please let me know if you have a better understanding of what I am trying to do.
PayRoll.txt
OPM_Table.xlsx

Normalize, normalize, normalize.  

You can flatten at the end for reporting by using a crosstab.

Every one of the numeric suffixes should end up as a separate row.  So you will have a row for Annual, Hourly, and Overtime for each of the 10 years.  If the years represent calendar years so that 24049.00 is the annual rate for GS-1 In 2016 then another column for the actual year is needed.  If the years represent length of time in job, then then should just be numbered 1-10.  Although what happens after that is a mystery unless you just lay off everyone after their 10th anniversary. The PK for the table should be an autonumber for ease of use but you will also need a multi-field unique index because the combination of LocName, Grade, and DataYear must be unique.

I can't normalize the table since it is not reflected based on year.  Attached are two queries that work but I need them to work if the other one is not the right choice.
5_7_9.txt
PayRoll.txt

I can't normalize the table since it is not reflected based on year

Looks to me like the columns are years.  Stand back and think about how you are going to have to change the query next year when you want 2020 - 2025 rather than 2019-2024.  You are effectively using a spreadsheet when you need to use a table.  Relational databases work very differently from spreadsheets.  You are coding yourself into a hole that will require maintenance every year.  That would not be the case if you properly normalized the table.

but I need them to work if the other one is not the right choice.

I don't know what that means.  Are you asking how to use one query rather than another as the RecordSource of a form or report?  To do that, you need code in the form/report's open event that determines which query you want and places the querydef name in the RecordSource property.

If "x" Then
    Me.RecordSource = "qry1"
Else
    Me.RecordSource = "qry2"
End If

Also, the WHERE clause of the first query is probably not correct.  When a compound expression contains different relational operators, you almost always have to use parentheses to ensure that the expression is evaluated as you intend.
WHERE ((([Forms]![Fee Review Summary]![Combo137]='GS-5/7/9' And [GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9')<>False))

As written combo137 is only relevant related to GS-5.  So the expression is actually (GS-5/5/9 AND 5) Or 7 Or 9
I'm guessing that you want  GS-5/7/9 when Grade2 is 5,6, or 7 NOT just 5

Change the expression to surround the OR parts with parentheses.  I removed the extraneous parentheses that Access loves to add so you can see the important part.

WHERE [Forms]![Fee Review Summary]![Combo137] = 'GS-5/7/9' And ([GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9')
OR simplifying further
WHERE [Forms]![Fee Review Summary]![Combo137] = 'GS-5/7/9' And GRADE2 IN ('GS-5','GS-7','GS-9')

And Finally, the use of the numbered suffixes doesn't make sense.  Are there typos in query 1?  For some Grades you are using Annual3 as one year but for other grades it is a different year.  Even in a spreadsheet that would be bizarre.

There are no typos.  The issue is that I am taking an OPM table and trying to demise a way of taking specific parameters from a drop by specific location and by General Service Grade.  The issue is that for single grades, the process is simple.  ANNUAL3 for FY2019, ANNUAL4 for FY2020, ANNUAL4 FY2021, ANNUAL5 for FY2022,  ANNUAL5 for FY2023, and ANNUAL6 for FY2024.  I also multiply an inflation factor in those costs for future years.  For grades that are multiple in steps, such as a GS-5/7/9.  The table does not give the option of selecting GS-5/7/9.  That is issues that I am dealing with right now.  All multiple steps with three grade increase follow this process.   The first year(FY2019) would be Annual3 Grade 5, second year (FY2020) would be Annual1 Grade 7, third year (FY2021) would be Annual1 Grade 9, fourth year (FY2022) would be Annual2 Grade 9, fourth year (FY2023) would be Annual3 Grade 9, and the fifth year (FY2024) would be Annual4 Grade 9.  Do you understand what I am trying to do now?  I have two SQL Scripts that work independently for all grades and the other one that only works for GS-5/7/9.

Am I supposed to know what OPM is?

For grades that are multiple in steps, such as a GS-5/7/9.  The table does not give the option of selecting GS-5/7/9

So fix the table.  It is far better to simply fix the table so that it has the rows you need.  Once you do that, you can normalize it.

So, what is your plan for next year? Are you planning on modifying all the queries or perhaps you're hoping you'll have a new job and not have to deal with it?  It is far better to normalize NOW and create queries based on properly structured tables.

When you are working with a relational database, rows are FREE.  Columns are EXPENSIVE.  Spreadsheets are wide and short and tables are thin and tall.  Queries based on tables will not need to change next year.  Queries based on a spreadsheet will need to change next year and EVERY YEAR.

Normalize, Normalize, Normalize.