Access Concatenate rows into single rows: extra conditions needed

I'm working with an Access database and I need to concatenate different related rows into 1 row. I found a solution here and used it with great success. However I need to add extra conditions to it, it should only be done if certain other columns are equal too. For example:

1 X Alpha

2 Y Beta

1 X Gamma

1 Z Delta

should become

1 X Alpha,Gamma

1 Z Delta

2 Y Beta

Does anyone know to do this, especially for a newbie like me?

From stackoverflow

• It seems you are using the code supplied in http://stackoverflow.com/questions/230241/does-ms-access2003-have-anything-comparable-to-stored-procedure-i-want-to-run.

  There is no reason why you should not feed in two fields as one in your sql, so an example:

  SELECT Number & Letter,
  Concatenate("SELECT Letter & Alpha As FirstField FROM tblTable
       WHERE Number & Letter =""" & [Number] & [Letter] & """") as FirstFields
  FROM tblT
  

  David-W-Fenton : There is no Concatenate() function in Jet SQL, which is already pointed out in the answers to the cited question.

  onedaywhen : SQL (the language) does not have a concatentate function because it would violate 1NF by creating a non-scalar type. ACE (Microsoft Access Engine, introduced in Access2007 to replace Jet) has multi-values data types if you want an engine-supported way of violating 1NF ;-)

  Remou : As the OP remarked, this data is for feeding in to a statistical program. This would appear to be a Access used under point 3: http://stackoverflow.com/questions/469799/what-are-the-appropriate-uses-for-ms-access/471798#471798

• Thanks a lot!!! Works like a charm. I had never imagined the solution would be so simple.

  CodeSlave : Remember to mark the answer accepted.