0

Let's say I have the following 2 tables (this is made up data served as example).

Table 1 contains employees info (Name and Departments). Table 2 track late clock-in time daily.

My task is to fill in Table 2 and occasionally update Table 1.

Since each employee is identified by Name and Departments, I want to merge these 2 fields into one single column. Then in Table 2 select that as lookup.

Important: I also want that any changes made to Table 1 (Name/Departments) is automatically reflected in Table 2.

The issue is: I cannot created a Relationship involving a calculated field in Access.

Is there a better way? I'm new to Access so any help is appreciated. Thank you very much.

Example below:

Table 1

EmployeeID EmployeeName Departments NameAndDepartments (calculated from the other 2 fields)
1 Ann Sales Ann from Sales
2 Bob Marketing Bob from Marketing
3 Charles IT Charles from IT

Table 2

Instance NameAndDepartments (lookup field to Table 1 - dropdown menu) Late clock-in Date
1 Ann from Sales 15 minutes 24-04-2023
2 Charles from IT 5 minutes 24-04-2023
Gnad
  • 25
  • 6
  • 1
    Table2 should NOT save name, only employee ID. Tables are link on key fields. Basic RDBMS principle. – June7 Apr 24 '23 at 16:41
  • Filling Table 2 using EmployeeID will be slower since I have to check the ID back and forth. Is there no other way? – Gnad Apr 25 '23 at 00:27
  • 1
    What do you mean by 'check the ID back and forth' - check where and when? I won't even touch 'other way' with a 10-ft pole. – June7 Apr 25 '23 at 02:32
  • Say I need to fill Table2. I know "Ann from Sales" is late by 15 min, but I don't know their EmployeeID. To get the EmployeeID I need to open Table1 to find that their ID is "1". Only then I can fill Table2. It will take a lot of time if I have to check their EmployeeID each time. – Gnad Apr 25 '23 at 03:10
  • 1
    No, build a multi-column combobox that displays name but saves ID. This is common technique https://stackoverflow.com/questions/11762395/ms-access-display-two-columns-in-combo-box – June7 Apr 25 '23 at 03:15
  • 1
    I don't build lookups in tables, I build comboboxes on form. If you really must work with tables directly instead of forms, table lookup field should also be set up as multi-column to display name but save ID. – June7 Apr 25 '23 at 06:27
  • After your comment I was able to build multi-column combobox on tables, and it's exactly what my original intention was. I will look more into building combobox on forms. Thank you very much. – Gnad Apr 25 '23 at 07:51

0 Answers0