1

I have an excel sheet with 500,000 rows imported into MS Access. It has 3 columns in the below format

 Staff    Locations     Roles
   1      Location1     Role1
   1      Location2     Role1
   2      Location2     Role2
   3      Location3     Role3
   3      Location3     Role4

I am trying to get the output in the below format

 Staff      Locations                 Roles
   1        Location1, Location2      Role1
   2        Location2                 Role2
   3        Location3                 Role3
   3        Location3                 Role4

Any suggestions on how I can accomplish the same in MS Access?

Thanks in advance

Potta Pitot
  • 175
  • 1
  • 5
  • 15
  • Try using the solution posted at [this SO question.](http://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query) – Bill Feb 28 '15 at 04:07
  • 1
    @bill Thanks I have had looked into it. That solution concatenates based on similar values in one column. I am trying to accomplish the same based on similar values in two columns. – Potta Pitot Feb 28 '15 at 06:58

1 Answers1

2

I am not sure how the code provided by @Bill works, as I have not used it or tested it, although I have used one that has proven to be a bit more flexible to the needs. The function is written by Allen Browne, called ConcatRelated.

The function takes in three main arguments, similar to a Domain function.

  1. The Field you are trying to combine/concatenate, is your first argument.
  2. The Table you are looking up to, is your second argument.
  3. The Condition by which it should group by.

There are two other optional arguments, in which you can tell how to Order the result (fourth argument) and what Separator you want (fifth argument), default is comma.

Then the function can be used in a Query, something like.

SELECT
    Staff,      
    ConcatRelated("Locations", "yourTableName", "Staff = " & yourTableName.Staff & " AND Roles = '" yourTableName.Roles "'") As NewLocation              
    Roles
FROM
    yourTableName
GROUP BY 
    Staff,
    Roles;

That should give you the right information.

PaulFrancis
  • 5,748
  • 1
  • 19
  • 36
  • Based on your suggestion I tried the query as below SELECT myTable.Staff, ConcatRelated("Locations","myTable","Staff = '" & myTable.Staff & "' AND Roles = '" & myTable.Roles & "'") AS NewLocation, myTable.Roles FROM myTable; The query performed the concatenation but I noticed it does not remove the duplicate cell after concatenation. So I am ending up with the same number of rows in the below format Staff Locations Roles 1 Location1, Location2 Role1 1 Location1, Location2 Role1 2 Location2 Role2 – Potta Pitot Feb 28 '15 at 15:39
  • 1
    @PottaPitot, you are missing the GROUP BY. I have included that in my answer. Any reason why you have not included that? – PaulFrancis Feb 28 '15 at 16:21
  • 1
    Yup that fixed it. That was my bad for not including it. Thanks a lot for the solution. You saved a huge amount of my time. – Potta Pitot Feb 28 '15 at 17:47