CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Ethan
Ethan

Reputation: 2784

Using Multi-Value Field in MS Access Query

I have a MVF field (I am fully aware that this is not a best practice) and I need to create a query where the result looks like this:

PersonName  MVF_Opt_1   MVF_Opt_2   MVF_Opt_3
Tim         X           X           X
John                                X
Jake        X                       X

I tried using an expression for each one that looks like:

MVF_Opt_1: IIf([Options].[Value] = 1,"X","")

For each of the query columns I need, but this seems to only be working if the option in the expression happens to be the first value in the MVF.

I also have about 20 options that do not need to be listed in columns that I can disregard.

Any ideas?

Upvotes: 2

Views: 2377

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

This seems to be working for me:

SELECT 
    mvfTest.PersonName, 
    IIf(DCount("*","mvfTest","PersonName=""" & [PersonName] & """ And Options.Value=""1""")=0,"","X") AS MVF_Opt_1,
    IIf(DCount("*","mvfTest","PersonName=""" & [PersonName] & """ And Options.Value=""2""")=0,"","X") AS MVF_Opt_2
FROM mvfTest;

Upvotes: 2

Related Questions