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.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
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
Also, do not use spaces in object names.
Object names should be letters and/or numbers (exception is the underscore)
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?
Hello ssanfu! Thank you so much for the advise. It is well noted.
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
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
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.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Glad to hear you got it working
Happy holidays and a happier new year!
Vlad