Link to home
Start Free TrialLog in
Avatar of Terry Moore
Terry Moore

asked on

Query that provides one line.

I have written Access SQL that creates a query.  The issue is that I get the data in three rows and I would like them to be grouped in one row.  Can this query be combined to one line instead of three.  
SELECT OPM_Table.LOCNAME, LocationID.LocationDescription, '5/7/9' AS GRADE, 'GS-5/7/9' AS GRADE2, 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' And [GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9')<>False));

Open in new window

Thanks,
Terry
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image
Hi,

I think your query perform a cartesian product with the PayInflation table.
Can you do a join on it ?

Else, either add a GROUP BY clause, or do a SELECT TOP 1 
SELECT TOP 1 OPM_Table.LOCNAME, LocationID.LocationDescription, '5/7/9' AS GRADE, 'GS-5/7/9' AS GRADE2, 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' And [GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9')<>False));

Open in new window

Avatar of Terry Moore
Terry Moore

ASKER

I tried your SQL and I now only get data for FY2019.  From FY2020 on it is blank.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Terry Moore
Terry Moore

ASKER

Thank you so much ,it works!  This works.  Can I loop another SQL with the same SQL with different criteria?
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Yes, just remember to put filtered fields, that also are selected, under HAVING.
Avatar of Terry Moore
Terry Moore

ASKER

So If I have two scripts I want to combine, how could I do it.  I am new at SQL and still learning.  Attached are the two SQL Scripts that work.  Can you assist me with it or should I post the question again and you can get points helping me?
5_7_9.txt
PayRoll.txt
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
What does "combine" mean?
Avatar of Terry Moore
Terry Moore

ASKER

It means that if it is not GS-5/7/9 then run the other script.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
It might be doable with a union  query:

SELECT 
    OPM_Table.LOCNAME, 
    LocationID.LocationDescription, 
    '5/7/9' AS GRADE, 
    'GS-5/7/9' AS GRADE2,
    Sum(IIf(OPM_Table.GRADE2='GS-5',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2))) AS FY2019, 
    Sum(IIf(OPM_Table.GRADE2='GS-7',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2020],2))) AS FY2020, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2021],2))) AS FY2021, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL2*[PayInflation]![2022],2))) AS FY2022, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2023],2))) AS FY2023, 
    Sum(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 
    ([Forms]![Fee Review Summary]![Combo137]='GS-5/7/9' And [GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9')<>False
GROUP BY
    OPM_Table.LOCNAME, 
    LocationID.LocationDescription
HAVING
    LocationID.LocationDescription=[Forms]![Fee Review Summary]![Combo117]

UNION ALL

SELECT 
    OPM_Table.LOCNAME, 
    LocationID.LocationDescription, 
    OPM_Table.GRADE, 
    OPM_Table.GRADE2, 
    Sum(FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2)) AS FY2019, 
    Sum(FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2020],2)) AS FY2020, 
    Sum(FormatCurrency([FY2020]*[PayInflation]![2021],2)) AS FY2021, 
    Sum(FormatCurrency(OPM_Table.ANNUAL5*[PayInflation]![2022],2)) AS FY2022, 
    Sum(FormatCurrency([FY2022]*[PayInflation]![2023],2)) AS FY2023, 
    Sum(FormatCurrency(OPM_Table.ANNUAL6*[PayInflation]![2024],2)) AS FY2024
FROM 
    PayInflation, 
    LocationID 
INNER JOIN 
    OPM_Table 
    ON LocationID.LOCNAME = OPM_Table.LOCNAME
GROUP BY
    OPM_Table.LOCNAME, 
    LocationID.LocationDescription,
    OPM_Table.GRADE, 
    OPM_Table.GRADE2
HAVING
    LocationID.LocationDescription=Forms![Fee Review Summary]!Combo117 And 
    OPM_Table.GRADE2=Forms![Fee Review Summary]!Combo137;

Open in new window

Avatar of Terry Moore
Terry Moore

ASKER

I get an error message.  It says that subqueries cannot be used in the expression (FormatCurrency(FY2020]*[PayInflation]![2021],2)).
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
That's right. Shouldn't it read:

    Sum(FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2)) AS FY2019, 
    Sum(FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2020],2)) AS FY2020, 
    Sum(FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2021],2)) AS FY2021, 
    Sum(FormatCurrency(OPM_Table.ANNUAL5*[PayInflation]![2022],2)) AS FY2022, 
    Sum(FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2023],2)) AS FY2023, 
    Sum(FormatCurrency(OPM_Table.ANNUAL6*[PayInflation]![2024],2)) AS FY2024

Open in new window

or similar?
Avatar of Terry Moore
Terry Moore

ASKER

It should read this for a GS5/7/9.
Sum(IIf(OPM_Table.GRADE2='GS-5',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2))) AS FY2019, 
    Sum(IIf(OPM_Table.GRADE2='GS-7',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2020],2))) AS FY2020, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL1*[PayInflation]![2021],2))) AS FY2021, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL2*[PayInflation]![2022],2))) AS FY2022, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2023],2))) AS FY2023, 
    Sum(IIf(OPM_Table.GRADE2='GS-9',FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2024],2))) AS FY2024

Open in new window


And this for single grades.
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

Open in new window

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
But you can use the summed alias FY2020 and FY2024 in expressions.
So, try with:

    Sum(FormatCurrency(OPM_Table.ANNUAL3*[PayInflation]![2019],2)) AS FY2019, 
    Sum(FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2020],2)) AS FY2020, 
    Sum(FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2020]*[PayInflation]![2021],2)) AS FY2021, 
    Sum(FormatCurrency(OPM_Table.ANNUAL5*[PayInflation]![2022],2)) AS FY2022, 
    Sum(FormatCurrency(OPM_Table.ANNUAL4*[PayInflation]![2022]*[PayInflation]![2023],2)) AS FY2023, 
    Sum(FormatCurrency(OPM_Table.ANNUAL6*[PayInflation]![2024],2)) AS FY2024

Open in new window

Avatar of Terry Moore
Terry Moore

ASKER

It works, but when I run the report for a GS-5, the GS-5/7/9 also shows up in the query.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
Then you may to include the "reverse" WHERE statement in the last query:

WHERE 
    ([Forms]![Fee Review Summary]![Combo137]='GS-5/7/9' And [GRADE2]='GS-5' Or [GRADE2]='GS-7' Or [GRADE2]='GS-9') = False

Open in new window


In any case, the WHERE and HAVING clauses are what control what is included. So, tweak these until success.
Avatar of Terry Moore
Terry Moore

ASKER

I am going to work on tweaking it.  It still does not work.  GS-9 does not work at all.  It only shows GS 5/7/9.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image
OK. It's you that have the data.