Multiple criteria in a query problem

[Erynn013]

I have an unbound field on a form that pulls part numbers from a table that
is not the main form base table. I have figured out how to make the source
query pull information from 1 field on the form to designate the part numbers
to pull into the field, but I need it to come from multiple fields. I need
it to pull Harness Family, Carline, Model Year, and Build Phase. I can only
get 1 to work at a time. Any suggestions, I am using the code:
[forms]![Setup Form]![HarnessFamily]

 

[Tom Ellison]

Dear Erynn:

I suggest you at least simulate a proper data design, as you do not now have
one. Equivalent information like your part numbers should not be stored in
consecutive columns of a table. Instead, a related table needs to be set up
to store them all, one per row. This can be simulated with a UNION query:

SELECT KeyValue, "Harness Family" AS Category,
[Harness Family] AS PartNumber
FROM YourTable
WHERE [Harness Family] IS NOT NULL
UNION ALL
SELECT KeyValue, "Carline" AS Category,
[Carline] AS PartNumber
FROM YourTable
WHERE [Carline] IS NOT NULL
UNION ALL
SELECT KeyValue, "Model Year" AS Category,
[Model Year] AS PartNumber
FROM YourTable
WHERE [Model Year] IS NOT NULL
UNION ALL
SELECT KeyValue, "Build Phase" AS Category,
[Build Phase] AS PartNumber
FROM YourTable
WHERE [Build Phase] IS NOT NULL

I have included something called KeyValue, which may be one or more
necessary additional columns required to JOIN to this query and find the
necessary information.

The above query will give the appearance and most of the function (except
indexing and performance I expect) that would have been available were the
data structured properly in the first place. It will also give you the
appearance of how such data is properly designed.

You need to use some real column name(s) for KeyValue and the actual table
name for YourTable.

Tom Ellison