How to display Compensation Rate by entering the Salary Grade and Step Level?

[JHJapay]

Hi guys! I wanted to automatically display the value in the "SG 2nd Tranch (2021)" table in the Compensation Rate cell by just entering the Salary Grade and the Step number.
Can anyone please help me? Your help is very much appreciated.
Thanks!

[Minty]

If you store your data correctly this becomes easy.

Salary_Grade	StepLevel	Salary
1	1	12034
1	2	12134
1	3	12236
1	4	12339
1	5	12442

If there is ever another step added you just add it as data and are not having to redesign your table and all the forms.

[Gicu]

I agree with Minty, best to normalize your data, you can probably get the look you have in SG 2nd Tranch (2021) with a crosstab query.

Until then maybe try a dlookup:
=DLookup("Step " & [Step],"SG 2nd Tranch (2021)","[Salary Grade]=" & [Salary Grade])

Cheers,
Vlad

[ssanfu]

Also, do not use spaces in object names.
Object names should be letters and/or numbers (exception is the underscore)

[JHJapay]

Hello Minty, as advised, I followed your table. Would you be so kind to recommend the next thing to do, especially with my compensation rate formula?

[JHJapay]

Thank you Gicu for the reply. Unfortunately, there was an error when I run your recommendation "=DLookup("Step " & [Step],"SG 2nd Tranch (2021)","[Salary Grade]=" & [Salary Grade])".
Would you mind advising what to do after following Minty's recommendation where I redesign my table?

[JHJapay]

Hello ssanfu! Thank you so much for the advise. It is well noted.

[Gicu]

What was the error. Have you checked if the field names matched?

Maybe try:
=DLookup("[Salary]","[SG Table]", "[Salary_Grade]=" & [Salary_Grade] & " AND [StepLevel] =" & [Step])
Cheers,
Vlad

[Gicu]

And probably better just bring the new table in your query, join it by Salary_Grade and Step and bring in the Salary field.

Cheers,
Vlad

[Minty]

I'd agree 100% with Vlad, simply add the table to your query and join on the Salary_Grade and Step.
The Salary field will then be the correct value.

[JHJapay]

What was the error. Have you checked if the field names matched?

Maybe try:
=DLookup("[Salary]","[SG Table]", "[Salary_Grade]=" & [Salary_Grade] & " AND [StepLevel] =" & [Step])
Cheers,
Vlad

Hi Vlad! This was magical. I can now sleep. Cheers to that!
So every time I just enter a salary grade and step level, the compensation rate automatically pops up so no need to input it every now and then.



Million thanks to you and the rest of the guys here!

[Gicu]

Glad to hear you got it working

Happy holidays and a happier new year!
Vlad