My goal is to take data that looks like this:
and make it look like this:
1 A, B
4 A, Z
In other words, I want to flatten and concatenate the data for reporting purposes using queries (not code). The first listing is the result of a query. I can use this query as the recordsource for a crosstab query and get to an intermediate result that looks something like this:
ID A B D Z
1 A B
4 A Z
My problem is getting to the final form. If I could predetermine what the ultimate values will be (the values are actually strings, but I am using alphas here for ease of description) the answer would be simple. I could hard-code a calculated field like this:
=IIF(ISNULL(A),"",A) +", " + IIF(ISNULL(B),"",B) +", " + IIF(ISNULL(D),"",D) +", " + IIF(ISNULL(Z),"",Z)
However I don't know what the values will be beforehand, so I need a solution that will work on the fly in Access. Thoughts?
In SQL Server I can do this using code a SELECT statement like this:
SET @outRoleIDList = ''
SELECT @outRoleIDList = @outRoleIDList + ',' + cast(RoleID as varchar(10))
WHERE LoginID = @inLoginID
SET @outRoleIDList = STUFF(@outRoleIDList, 1, 1, '') --remove leading comma