Merging two access fields, one of which is a .Value

[Kabeebabee]

I am trying to create a query where there are two fields, CCE Analysts and FMPD Analysts. CCE Analysts have multi-values where FMPD Analysts are single value. The query should create one field with all names but I kept getting the message "The multi-valued field '[CCE Analyst]' is not valid in the expression [CCE Analyst] &""& [FMPD Analyst]'. I then created a flattened field named CCE Analyst.Value and renamed the Expression ]CCE Analyst.Value] &""& [FMPD Analyst]. Now the message is Invalid bracketing of name [CCE Analyst.Value]. Can someone please give me some well needed guidance?

[Kabeebabee]

I was able to concatenate by bracketing [CCE Analyst].[Value] but that's not what I want. I want a list of all names from both columns

[ItsMe]

Are you using lookup fields in your table? Are you using a Value List or a Table?

[Kabeebabee]

It is with a Value List

[ItsMe]

Create a query using the Query Designer. Add the one table that has the lookup field to the design surface. Add the Primary Key field and also the field that is the Lookup to the Grid. In the Criteria for the column that is the Lookup, add the following text.

In ('foo','bar','baz')

You will need to edit your version of criteria. The criteria I posted here is for a Lookup field that has the options of foo, bar, and baz. So replace the text with the values from your lookup field (retrieve from your table while in design view).

Save and name your new query. See if this new query is helpful when creating other queries. For instance, add it as a subquery to another query object.