Use table data based on form field selection

[skwilliams]

I have 4 tables with data used for rating employee production in reviews. Each table holds data as in a scale. For example tblPa is the production scoring table for employees with 0 to 2 months experience. If any employee has a weekly production of 200 orders they would rate in the 3 point scale which is between 175 and 225 orders.

On the form where the weekly orders figure is located is also a scale number for the employees experience. This figure should correspond to the proper scale table.

I would like to select the scale number in the form's scale field and on clicking a refresh button, the application should reference that table and use the weekly order figure from the form to find the proper score for the weekly orders.

I hope this makes sense?

Thanks.

 

[MrTibbs]

Perhaps a Union query ...

Why have 4 tables? If you really want to query each underlying table depending on the form you could:
Build a union query from the 4 tables and base your form on that
Build an SQL-based recordset in code

Neither approach is elegant. An alternative approach would be to combine your 2 point and 3 point tables into a single table.

Good luck

Tony

 

[skwilliams]

Each table contains different data within the same fields. These figures are changed periodically through a form by the department supervisor. By combining these into one table, I felt it would be very confusing for the supervisor. I'm trying to make this as user-friendly as possible.

Could this be done using a Select Case Statement to choose the appropriate table and a recordset to use the information from that table?

 

[skwilliams]

OK, I now have a table for Quality scales named tblQuality with a unique field named "Scale". Could someone provide me with an example of the code used to call a recordset to pull a specific record from the table?

Thanks.