Perhaps your could merge your two secondary tables into one table? Just going by the brief description in your post, it seems possible that you could do that fairly easily. You might not need to do much more than add one more field to your merged table that shows something like "Diamond" or "Ambersol" to distinguish between the two sets of records.
Otherwise, you're going to need two fields. One field would receive the lookup from the "diamond" table and the other from the "blue ambersol" table. A calculation field could then select which field displays the data if you need a unified field to show information from both sources.
The calculation might not need to be anymore sophisticated than:
Table1lookupField & Table2lookupField.
Assuming that the same record never has looked up data from both tables.
What I have done is add a field to the lookup table called Type to give me two fields to match; both type of media and the media number.
Diamond Disc's have two numbers - Matrix and Coupling while Blue Amberol's only have one number. So, I combined the Matrix Number and Blue Amberol numbers into one field - thank goodness they are not even close to being the same. Now when I enter the Blue Amberol number it pulls data just fine. However, I have a problem when it comes to the Diamond Discs.
This is the calculation used or the Album_Title (Blue Amberol) - If ((Medium=BA_DD::Type and Number=BA_DD::Matrix ) ; BA_DD::Title 1 )
However, now I need to check Album_Title for a Diamond Disc - If ((Medium=BA_DD::Type and Number=Coupling_DD ) ; BA_DD::Title 1 )
How can I put both these calculations into the Album_Title's calculated value field so that if either of these conditions is met, the title is pulled from the look-up table? I have tried using both with an 'or' but it does not work.
Coupling_DD is a field with a Lookup auto-enter option? If so it should be empty for Blue Amberol and show a value for Diamond Disks.
You can nest your calculations as shown:
If ( IsEmpty ( Coupling_DD ) ; If ((Medium=BA_DD::Type and Number=BA_DD::Matrix ) ; BA_DD::Title 1 ) ; If ((Medium=BA_DD::Type and Number=Coupling_DD ) ; BA_DD::Title 1 ) )
This may be overkill as you say the values for Blue Amberol and Diamond Disk are very different, but it should do the job if I understand your design/implementation correctly.
Here is an example of both:
'Number' is the lookup field - looks for a match in 'Matrix' Number = 1501
'Medium' can have a value of 'Blue Amberol' or 'Diamond Disc' Medium = Blue Amberol
Title = Semiramide overture
'Number' is the lookup field - should look at 'Matrix' for a match Number = 2789-B
'Medium' can have a value of 'Blue Amberol' or 'Diamond Disc' Medium = Diamond Disc
Title = Blumenlied
CouplingNumber = 50052-L
When I enter a new record, the first two items requested from the user are the Medium and Number. The requested Number and Medium must match in the lookup table to be valid.
Hopefully this helps.
I found a solution - one table for Blue Amberol look-up and one table for Diamond Disc look-up - works perfect!