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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.