How to display Compensation Rate by entering the Salary_Grade and StepLevel? (1 Viewer)

jhjapay

New member
Local time
Today, 21:31
Joined
Dec 28, 2020
Messages
4
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!
1.jpg

2.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:31
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Don't do it in the table, use a query and check out the DLookup() function.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:31
Joined
Jul 9, 2003
Messages
16,245
Just a Guess, but is this something like you want?

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,175
create a Query Joining the two tables:
Code:
SELECT T1.[Full Name],
    T1.[Date of Original Appointment],
    T1.[Years in Service],
    T1.[Salary Grade],
    T1.[Step]
    T2.[Salary] AS [Compensation Rate]
FROM [Years in Service and Step increments] AS T1
LEFT JOIN [SG Table] AS T2
    ON T1.[Salary Grade] = T2.[Salary_Grade] AND
    T1.[Step] = T2.[StepLevel];
 

jhjapay

New member
Local time
Today, 21:31
Joined
Dec 28, 2020
Messages
4
create a Query Joining the two tables:
Code:
SELECT T1.[Full Name],
    T1.[Date of Original Appointment],
    T1.[Years in Service],
    T1.[Salary Grade],
    T1.[Step]
    T2.[Salary] AS [Compensation Rate]
FROM [Years in Service and Step increments] AS T1
LEFT JOIN [SG Table] AS T2
    ON T1.[Salary Grade] = T2.[Salary_Grade] AND
    T1.[Step] = T2.[StepLevel];
Hi Arnel! I've tried this code of yours but I'm getting a systax error. May I know were did I go wrong?
 

Attachments

  • 1.jpg
    1.jpg
    182.2 KB · Views: 327
  • 2.jpg
    2.jpg
    254.9 KB · Views: 419
  • 3.jpg
    3.jpg
    382.7 KB · Views: 425

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,175
show me the table structure of "SG Table" and "Years in Service and Step Increments" table.
 

jhjapay

New member
Local time
Today, 21:31
Joined
Dec 28, 2020
Messages
4
show me the table structure of "SG Table" and "Years in Service and Step Increments" table.
Attaching the "SG Table".
However, the "Years in Service and Step Increments" is not a table but a query.
Thanks in advance arnel.
 

Attachments

  • SharedScreenshot.jpg
    SharedScreenshot.jpg
    255.9 KB · Views: 414
  • Query.jpg
    Query.jpg
    432.9 KB · Views: 415

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,175
you already has the Compensation Rate on your Query!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:31
Joined
May 7, 2009
Messages
19,175
here is the step:
1. Create->Query Design.
2. Pick your "Employee Table" and "SG Table"
3. Drag the "employee Grade field" to the "grade field of SG Table"
4. a relationship line will be drawn.
5. right-click this line to modify the relationship (Join properties...).
6. choose (2) include all records from "the query".

7. drag the "step field" from employee table to sg table.
8. repeat step 5 and 6, on the relationship line.

now double click on the fields (on either table) that you
want the query to show.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:31
Joined
Feb 19, 2002
Messages
42,981
Having multi-field primary keys is not wrong but it does make joins more awkward and it will prevent you from using combo and list boxes to help you to pick values.

You will eventually run into problems as you try to build your interface so I would suggest using an autonumber as the PK to the table and then adding a unique index to enforce the business rule that the combination of Grade and Level must be unique. To change the tables, add an autonumber to the salary table. Name it SalaryID. Then add SalaryID as a long integer to the other table. Join the two tables on Grade and Level and update the Years in Service table with the SalaryID value. Once you are sure that values have been updated correctly, remove the three extraneous fields from the Years in Service table. You can pick up the three fields whenever you need them by using a join.

As arnel suggested, I would almost always use a query with a join. Domain functions run separate queries so they should be avoided inside other queries or in code loops. If you use a domain function in a query that has a thousand records, the query needs to run 1000 other queries. One for each row to get the answer from the DLookup(). The join is far more efficient.

Not sure why this popped up but it did and so I responded:)
 

Users who are viewing this thread

Top Bottom