Multiple Lookups - Design Advice (1 Viewer)

danielw.au

Registered User.
Local time
Today, 22:53
Joined
Mar 12, 2014
Messages
10
Hi Everyone,

I have an existing access database that I use for a number of purposes and I need some advice when using Lookups in a table.

In my database, I have a table called RTBL_Paycodes. This lists all the payment codes from our payroll database (screen sample attached). A field in this table determines if the pay code type is hours, leave, before tax allowance, after tax allowance, before tax deduction or an after tax deduction.

I have another table called LTBL_PaymentAdjustments. In this table, users select a Pay code using a Lookup. Based on the field selected, the lookup only displays a specific type of pay code (e.g. hours). I do this as each field is specific to a pay code type.

Rather than creating a table where the user can add as many transactions as they wish (as this is not required), I have creating a number of fields that relate to a specific pay code type. For example, I have 6 fields where the user can enter pay codes relating to the “hours” type.

For example:

PA_Line1_HRL_Code
PA_Line2_HRL_Code
PA_Line3_HRL_Code
PA_Line4_HRL_Code
PA_Line5_HRL_Code
PA_Line6_HRL_Code

The concerns I have, which I need help with are:

1. The above six fields all lookup the RTBL_Paycodes table. Is this safe to do so? Will I have any long term issues?

2. The fields only display the code. When the user selects the code, I need them to be able to see the description on the form or a report. How do I look up the Pay code description value and display it based upon the code entered/selected?

Thank you so much for your advice.

Daniel
 

Attachments

  • LTBL_PaymentAdjustments.JPG
    LTBL_PaymentAdjustments.JPG
    53.1 KB · Views: 70
  • RTBL_Paycodes.jpg
    RTBL_Paycodes.jpg
    98.1 KB · Views: 72

Jutt123

New member
Local time
Today, 04:53
Joined
Oct 29, 2014
Messages
1
Rather than creating a table where the user can add as many transactions as they wish (as this is not required), I have creating a number of fields that relate to a specific pay code type. For example, I have 6 fields where the user can enter pay codes relating to the “hours” type.



HP
 

Users who are viewing this thread

Top Bottom