Access Concatenate rows into single rows: extra conditions needed

Question

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?

Solution

It seems you are using the code supplied in Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess.

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

Comments

[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 ;-)

[Fionnuala]

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: stackoverflow.com/questions/469799/…