asked on

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.

Mike Eghtebas

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

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

Eric Sherman

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

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shaun Wingrin

ASKER

eghtebas Is your SQL code for SQL Server or Access 2013? I need Access 2013 please

Mike Eghtebas

sorry, it is for SQL Server.

PatHartman

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.

Shaun Wingrin

ASKER

PS What about code for MySql - as I have access to this.