Conditional matching between tables, 2 or three variables
I am Working on database with Light Bulbs
I have a database of commercial light bulbs. There are about 20 fields of information per bulb.
One is: if it is Energy Star qualified.. “yes”, “no”, & “n/a”.
Another field is useful life in years… example could be “10” years.
If I am trying to match light bulbs to a specific need, that has been manually input from a job order on another table, and that need might be that a bulb can either have a useful life of over 8 years or be Energy Star Qualified.
Example bulbs in database:
Bulb A: useful life 12 years & Energy Star Qualified (this bulb would get a Yes / successful match)
Bulb B: useful life 5 years & not Energy Star Qualified (this bulb would get a No)
Bulb C: useful life 11 years & Not Energy Star Qualified (this bulb gets a Yes)
Bulb D: useful life 4 years & Energy Star Qualified (this bulb gets a Yes)
You can see the problem, if I match from field to field (twice: using both fields), only Bulb A would show up. But the correct answer is A, C, & D.
How do I have 2 fields: Energy Star Qualified & Useful life, and then in another table/portal (for job requirements), have Bulbs A, C, & D show up as meeting requirements?
And of course I could, will have three, or more, fields to contend with later.
Help! I am sure the answer is easy... if you know it.