0

If I have a table called Activities:

ActivitiesTable

..and another table called Tutors:

TutorsTable

If I were to do a simple LEFT JOIN for the tutor names on their LearnerId / InstanceId records in the activities table I would get a result like this:

QueryResult

(Sql for the above result):

SELECT Activities.LearnerId, Activities.InstanceId, 
Activities.LastName, Activities.FirstName, Activities.Activity, 
Tutors.TutorLastName, Tutors.TutorFirstName
FROM Activities 
LEFT JOIN Tutors ON (Activities.InstanceId = Tutors.InstanceId) 
AND (Activities.LearnerId = Tutors.LearnerId);

I would prefer that the number of records in the Activities table is maintained (i.e. the query results in 4 records rather 7).

I would also like every tutor matched to a LearnerId / InstanceId record to be collected in another column called Tutors(s), where the names of the related tutors are all listed in one field with commas separating each tutor. In other words, a results that looks like this:

PrefQueryResult

...is it possible to do this just in SQL, or can this only be achieved in VBA?

Matt Hall
  • 2,412
  • 7
  • 38
  • 62
  • 2
    First read this: http://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query And then read this: http://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843 – nelucon Jul 23 '14 at 14:32

1 Answers1

0

It is quite possible to get what you want using a VBA Function.

Allen Browne has well documented code for this operation. It is called "Concatenate Vales from Related records". Have a browse through. http://allenbrowne.com/func-concat.html

Hightower
  • 968
  • 4
  • 24
  • 58
PaulFrancis
  • 5,748
  • 1
  • 19
  • 36