I have a field in Table A that does a lookup in Table B and it works just fine. Until I discovered that not All the records in Table B should be available for the lookup - the values of some records in Table B are off limits and not to be considered in this lookup. I don't want to simply get rid of the "off limits" records in Table B, I need to preserve the complete set of records for other reasons. So is there a way to have a field in Table A do a lookup to a "found set" of records in Table B or otherwise restrict the values in Table B.
My ham-handed initial approach is to make a copy of Table B, and have Table B-1 and Table-B2. Then I delete the extra records in Table B-1 and offer this slimmed down set of records for the lookup from Table A. Table B-2 will preserve the full set of Table B values for other purposes.
Of course this is not optimal because of changes in the future to Table B that will have to be duplicated for both Tables B-1 and Table B-2.