Microsoft Access: query with multivalued field

Question

I need to Open a specificQuery in Design View, and then modify it by adding a particular field to the Design grid. This particular field I added is a multivalued field, and each option should appear on a separate row. I need help making each option appear on separate row.

Answer 1

This particular field I added is a multivalued field, and each option should appear on a separate row.

If you open the query in design view you will see in the table's field list that  the multi-valued field is shown like this:

    NameOfField

          NameOfField.Value

Click and drag the NameOfField.Value into an empty column in the design grid.

When you open the query as a datasheet it will return multiple rows for each value per row in the multi-valued field.  The values for each of the other fields in those rows will consequently be repeated.  If you are expecting it to return only one distinct instance of the other fields' values, then that is not how queries work.  To do that you would need to return the results in a form or report, not a datasheet.

Answer 2

What is the name of the MVF? 

If you are doing this manually through Query Design mode, you don't need what Daniel suggested. However, if you want to do it through code, you will.

Answer 3

Heres my SQL Statement

SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName, AdRep.Insurance

FROM AdRep;

Answer 4

Through automation?

I think you'll need to get familiar with using VBA to parse the query's SQL statement.

You'd get the SQL statement by doing something like

    Dim qdf         As DAO.QueryDef

    Dim sSQL      As String

    Set qdf = CurrentDb.QueryDefs("sQryName")

    sSQL  = qdf.SQL

    'Add your code to edit/parse the sSQL to add whatever fields you need

    'Your code so perform the above would go here

    qdf.SQL = sSQL  'push the modified SQL statement back to the query to redefine it.

    Set qdf = Nothing

Answer 5

Can you show us your SQL statement (Open SQL View and copy and paste). Generally I think you need to add the .Value entry as a column in your query. But most developers don't bother with MVFs preferring to use the more traditional junction tables to model a many to many relationship.