danielw.au
Registered User.
- Local time
- Today, 13:02
- 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
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