concatenating the many side results into one result

[casey]

Hello,

i am trying to join the results of a many-side of a query into one result field separated by commas...

Table: tblWorkOrderStoresFinal
Row 1: LogNumber: 1 StoreNumber: 2
Row 2: LogNumber: 1 StoreNumber: 3
Row 3: LogNumber: 1 StoreNumber: 4

I would like to make a query that combines the StoreNumber into one field CombineStoreNumbers separated by commas as follows...

LogNumber: 1 CombineStoreNumbers: 2, 3, 4

I tried the following crosstab query, but it's not quite right.
TRANSFORM StoreNumber
SELECT LogNumber
FROM tblWorkOrderStoresFinal
WHERE (((LogNumber)=1))
GROUP BY LogNumber
PIVOT StoreNumber;

Any ideas? Hope this makes sense.

Thanks, Casey

 

[pbaldy]

Not how I'd do it, but here's one way:

http://support.microsoft.com/kb/210163/en-us

 

[casey]

Thanks for your response. Any ideas how i could get this from a query?

Right now i have a function to do just what this article suggests, but this information is used in several places so a temp table is being used to hold this de-normalized data and the "concatenated" string may exceed the fieldsize limitations so i need to generate this info. without storing it somewhere.

 

[RoyVidar]

You can check out Duane Hookoms suggestion here
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[pbaldy]

What does your function do? What I would typically do is create a function that accepts the LogNumber as an argument and returns the concatenated string. Then I'd call that from a query, so for each LogNumber it would return the string.

 

[casey]

Then I'd call that from a query, so for each LogNumber it would return the string.

pbaldy,

My function does just that...loops through the StoreNumber fields for each LogNumber and creates a string with all the StoreNumbers in one field stored in a temp table.

If your solution requires the string to be stored in a field in a table somewhere... then this is what I'm trying to get away from. I would like to do this on-the-fly without storing the string in a table while I'm moving to SQL Server and, apparently???, the memo fieldsize in SQL Server is limited to 8000 characters???


RoyVidar,

I'll look into this article and see....Thanks.

boblarson,

I'm using a memo in Access. But my SQL Server gurus are limiting me to a vchar 8000 characters. Thanks.

 

[pbaldy]

I wouldn't store it. Mine would return the string directly to whatever called it:

Public Function Concat(ID As Long) As String
  'code to build the string
  Concat = the string
End Function

and called from a query:

ConcatentatedString: Concat(LogNumber)

 

[casey]

pbaldy,

Great! I'll look at that and see if it works. Thanks a lot.

Casey