Cross Reference

[Steven.Allman]

Got a doozy...
Military Pay Chart
grade 2 4 6 8
1 500 1000 1500 2000
2 600 1050 1600 2200
3 700 1200 1800 2300
4 800 1300 1900 2500

Example here.
A different query gives me their length of service and pay grade;Query1
Query 2, I want it to take the results from query 1, and cross reference to give me the value.
IE; October 2023 person will have 4 years of service and is rank 3, so give me 1200 as a result in my query.
but Im using a query value AND grade value to return that 1200 and I cant figure out how to create that reference.


data.accdb

Any pointers?

[Welshgasman]

Normalize your table?

[Steven.Allman]

Table Analyzer says unless I manually split my data, that its good.
I am not positive what this means "normalize"

[Welshgasman]

Table Analyzer says unless I manually split my data, that its good.
I am not positive what this means "normalize"

You would have a table with fields
PayID (autonumber)
PayRank
PayYear
PayAmount

Then you would just DLookUp PayRank = 3 and PayYear = 4 and retrieve a value of 1200 from PayAmount

[Steven.Allman]

You would have a table with fields
PayID (autonumber)
PayRank
PayYear
PayAmount

Then you would just DLookUp PayRank = 3 and PayYear = 4 and retrieve a value of 1200 from PayAmount

I was trying to get away from having to create this table though.
What your saying is, im screwed.
gotta create a new table?

[Welshgasman]

No, wait and see if anything else is offered.
That is how I would do it. Then I could produce your cross reference as a crosstab.

You could create your own function to locate rank record, then walk the fields until you find the correct year of service.

[pbaldy]

You could create a union query to normalize the table, and run your queries against that instead of the actual table. It's a workaround to the non-normalized table you have now. Whatever the analyzer may say, you have a piece of data (years of service) as a field rather than a record. Data shouldn't be part of the structure of the table.

[June7]

Which pay table should pay be pulled from?

Suggest including ID in YRS-SERVICE query.

Adjust Query1:

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.