Multi value field in Access 2016

Question

Hello,   I hope some one can help with an Access issue I am trying to resolve.

I saw several posts on this issue but cant seem to get the issue resolved.

A large number of projects have had data stored in SharePoint at the insistence of our management. A much shortened example of the data is shown below. We have a multi value field that is giving me a very tough time.

The data in SharePoint looks like this:

Project Name          Associates                                               StartDate    EndDate      %  Complete  More Fields follow

P1                            Jim, Helen, Joan                                                I omit these field values for brevity

P2                            Jim, Phil

P3                            Jim, Mary, Jack

P4                            Steve, Phil, Peter

P5                            Jim, Helen, Carl

The list continues.  There are currently 37 projects underway and 276 associates involved in these projects.  The export from SharePoint is not a problem.

I propose to have a Project table, for the current 37 projects, and any future projects with pertinent data on the projects and for all the associates that currently involved.

What I would like to also do is create a new table with a combined primary key of Project Name and Associates and populate as follows

Associates          ProjectName           

Jim                           P1

Helen                       P1                              

Joan                         P1

Jim                           P2

Phil                          P2

Jim                           P3

Mary                        P3

Jack                          P3

This table will continue for all 37 current projects and 276 associates.  I can them run queries to get data on specified projects.

I will create a form to add new projects, associates, and manage the projects.

Queries can be added as needed and reports to print for management

I am having a very difficult time normalizing the result of the data that is imported into Access.

Any further info can be provided.

Please help.

Hugh

Answer 1

My attention was diverted, temporarily.  This will be very helpful and I will give it a try today and report back.

Answer 2

You can extract the individual elements of a multi-valued field by means of its Value property in a query, so you should be able to create an 'append' query along these lines:

INSERT INTO ProjectAssociates(Associate, ProjectName)

SELECT Associates.Value, [Project Name]

FROM [YourImportedSharePointList];

Note that I've changed the name of the Associates column in your new table to Associate.  Conventionally, in a relational database, column names are singular nouns wherever possible to reflect the fact that a column represents an attribute, whereas table names are conventionally plural or collective nouns wherever possible to reflect the fact that a table represents a set.

You should also insert rows into a referenced table, Projects, with:

INSERT INTO Projects(ProjectName)

SELECT DISTINCT [Project Name]

FROM [YourImportedSharePointList];

and then create an enforced  relationship between the tables.