Shaun Wingrin
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
eghtebas Is your SQL code for SQL Server or Access 2013? I need Access 2013 please
sorry, it is for SQL Server.
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.
ASKER
PS What about code for MySql - as I have access to this.
Open in new window
I got:
Open in new window
When tried using:
Open in new window