use one of 2 fields for lookup between 2 tables

[mightyduckling]

Question:

I have a database with two tables (an inventory of items and a work system). In my inventory I have serial numbers and inventory tags. I want to be able to look up items from my inventory using either of the 2 fields. Currently if I relate both fields it requires that I input both pieces of info to look up the details of the item in my work system. I want to be able to use either number to look up the details from my inventory. Is there any way to do this?

I keep looking for an "or" relation option but I have yet to find it. Any help will be greatlky appreciated.

[Ender]

Welcome Duckling!

Using a Serial# field and a TagID field in your Work table: Set Serial# to be a lookup by the "Inventory by TagID" relationship, the TagID to be a lookup by the "Inventory by Serial#" relationship, and the other fields to be lookups by "Inventory by Serial#".

You will need two table occurances of the Inventory table (one for the TagID relationship, one for the Serial# relationship.)

[mightyduckling]

Thanks, I will be trying it first thing Monday Morning!

[Ender]

It has one flaw: If both keys are already populated, it won't let you change the Serial#.

[mightyduckling]

Thanks, I finally got it working!