I have a database with 3 tables - 1 Main Table, 1 contains lookup data for a Diamond Disc Recordings, and 1 contains data for Blue Amberol Recordings.
My Main Table has multiple tabs - one for each type of recording.
On the Diamond Disc Tab of the main table, I have a lookup field called 'Matrix Number' which searches a table for Diamond Discs and if a match is found pulls data into some of the Main Table fields one of which is a field called 'Number'. In this case 'Number' would be the actual Album Number.
On the Blue Amberol Tab of the main table, I want to setup the a field called 'BA Number' to be the lookup field. However, the 'BA Number' is the same field as 'Number' in the main table.
Since 'Number' is defined as an 'Auto-Enter Calculation' for the Diamond Disc search, how can I also use 'Number' as a look-up field for the Blue Amberol Table?