Combo Box based on 4 fields

[mfallon]

I am trying to design a query for a combo box to select all distinct materials in my original table. My original table has Project#, Project Title, Affected Material 1, Affected Material 2, Affected Material 3, and Affected Material 4. I want the users to be able to search for Project# based on the Affected Materials, therefore I need a query that will list all affected materials from AM1, AM2, AM3, and AM4. Once I have the query then I will be able to create the drop down.

Any ideas?

 

[MStef]

I don't know your task, but it seems to me that you have got a problem with DATABASE NORMALIZATION.

 

[ajetrumpet]

I am trying to design a query for a combo box to select all distinct materials in my original table.

You don't need a query for this.

I want the users to be able to search for Project# based on the Affected Materials, therefore I need a query that will list all affected materials from AM1, AM2, AM3, and AM4.

Again, you don't need to go through a query to get this done. Specify what you want in the RowSource of the box, and that should suffice....

Combo Rowsource = SELECT project#, am1, am2, am3, am4 FROM table ORDER BY project#;

Once I have the query then I will be able to create the drop down.

Dropdown = Combo Box?? Queries are not needed for Boxes.

therefore I need a query that will list all affected materials from AM1, AM2, AM3, and AM4.

If there is more than one value listed in any of the AM fields, than MStef is right, and your data structure is not normalized.