Reducing Access table - SQL code required

Say, I've an Access table in Access 2013 and they structure is as follows : Name, Surname and Volunteer.
The Name and Surname are repeated exactly but Volunteer changes at each instance ie. for each name and surname there are a number of volunteers.  Sample table follows :
Name Surn   Volunteer
Peter  Smith  Harry
Peter Smith   Jeff
Peter Smith    Archie
William Jones  Shaun
William Jones Neville

We wish to create a table showing each of the unique Names and Surnames with all of the corresponding volunteers in 1 single, Memo field separated by a semicolon or even with a CR+LF. Please assist with possible SQL code to do this or alternative method.
shaunwinginAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We wish to create a table showing each of the unique Names and Surnames with all of the corresponding volunteers in 1 single, Memo field separated by a semicolon or even with a CR+LF
You really should normalize the structure and break the data out into Names, Volunteers, and then a Join table between those two. You can create three tables:

tblName
--------------
NameID
Name
SurName

tblVolunteer
------------------
VolunteerID
Volunteer

tblNames_Volunteers
--------------------------------
NameID
VolunteerID

You can then insert into the Name and Volunteer tables:

INSERT INTO tblName(Name, SurName) SELECT DISTINCT Name, Surname FROM  YourTable
INSERT INTO tblVolunteer(Volunteer) SELECT DISTINCT Volunteer FROM YourTable

Now you can use code to fill the Join table:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tblName")

Do Until rst.EOF
  Dim rst2 As DAO.Recordset
  Set rst2 = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE Name='" & rst("Name") & " AND Surname='" & rst("SurName") & "'")
  Do Until rst2.EOF
    Currentdb.Execute ("INSERT INTO tblNames_Volunteers(NameID, VolunteerID) VALUES(" & rst("ID") & "," & DLookup("VolunteerID", "tblVolunteer", "Volunteer='" & rst2("Volunteer") &"'"))
    rst2.MoveNext
  Loop
  rst.MoveNext
Loop

That's air code so there may be some minor errors, but hopefully you get the gist.

You may be able to do the insert into the Join table with pure SQL, but I don't know the syntax for that.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT Distinct Name, Surn,
   (
      SELECT Volunteer + ' '
      FROM #t
      WHERE Name = A.Name
         AND Surn = A.Surn
      FOR XML PATH('')
   ) AS Volunteers
FROM #t AS A

Open in new window


I got:
Name Surn   Volunteer
Peter	Smith	Harry Jeff Archie 
William	Jones	Shaun Neville 

Open in new window


When tried using:
create table #t(Name varchar(30), Surn     varchar(30),Volunteer  varchar(30));
--delete From #t;
insert #t values
('Peter',        'Smith', 'Harry'),
('Peter',        'Smith', 'Jeff'),
('Peter',        'Smith', 'Archie'),
('William',        'Jones','Shaun'),
('William',        'Jones','Neville') 

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
BTW, you can change line 3 from:

SELECT Volunteer + '  '

to

SELECT Volunteer + ', '

To have:
Peter      Smith      Harry, Jeff, Archie,
William      Jones      Shaun, Neville,

This was SQL Server solution I though you had the option to work with.

Mike
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eric ShermanAccountant/DeveloperCommented:
We wish to create a table showing each of the unique Names and Surnames with all of the corresponding volunteers in 1 single, Memo field separated by a semicolon or even with a CR+LF.

Are you wanting to create the table in Access or SQL Server???

ET
0
 
PatHartmanCommented:
Modifying the table structure is not necessary.  Converting to the normalized schema suggested by Scott is the best solution.  But occasionally you want to export a flat version and so in that case, here is a link to a post that shows how to do this in a query.

http://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
0
 
shaunwinginAuthor Commented:
eghtebas Is your SQL code for SQL Server or Access 2013? I need Access 2013 please
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
sorry, it is for SQL Server.
0
 
PatHartmanCommented:
In Access, you will need to use something like the function I posted.  Access SQL is still in the dark ages and doesn't have any direct way to do this.
0
 
shaunwinginAuthor Commented:
PS What about code for MySql - as I have access to this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.