Which pay table should pay be pulled from?
Suggest including ID in YRS-SERVICE query.
Adjust Query1:
Code:
SELECT ID, SM_Name, [pay-grade], [OCT] AS [October Pay],
DLookUp("[" & [OCT] & "]","fy23pay","Grade='" & [pay-grade] & "'") AS Pay
FROM [YRS-SERVICE];
Be aware that domain aggregate functions can cause slow performance in query with large datasets. But then so can UNION query.
There is no query wizard/builder for UNION, must type or copy/paste in SQLView. There is a limit of 50 SELECT. First SELECT defines field names and data types. Here is example of first 3 with your data.
SELECT ID, Grade, [>2 Yrs] AS Pay, ">2" AS Yrs FROM fy23pay
UNION SELECT ID, Grade, [2], "2" FROM fy23pay
UNION SELECT ID, Grade, [3], "3" FROM fy23pay;
Again, adjust Query1:
SELECT ID, [YRS-SERVICE].SM_Name, [YRS-SERVICE].[pay-grade], CStr(Nz([YRS-SERVICE]![OCT],0)) AS [October Pay]
FROM [YRS-SERVICE];
And Query3 using UNION and Query1:
SELECT Query1.ID, Query1.SM_Name, Query1.[pay-grade], Query2.Pay
FROM Query2 RIGHT JOIN Query1 ON (Query2.Yrs = Query1.[October Pay]) AND (Query2.Grade = Query1.[pay-grade]);
Use whichever approach you prefer.
Should field [>2 Yrs] actually be [<2 Yrs]. Do you want this pay to show for records that show 0 years?
Personnel ID 7 does not have pay-grade.
Strongly advise not to use spaces nor punctuation/special characters in naming convention.