String Concatination w/ Queries

[abombss]

I have two tables with a one to many relationship. The one table has workorder information. There is one record for every workorder entered. The many table includes service information for each workorder. There is a record for each service code associated with each workorder.

I want to concatinate the service codes for each workorder so that there is one record for each workorder and it includes a field which has all the service codes as one string.

I am currently doing this through code, but it is very slow and inefficient. Is there anyway to do this through SQL or a query in MS-Access. If anyone has any ideas please let me know.

[sig]<p>abombss<br><a href=mailto:abombss@hotmail.com>abombss@hotmail.com</a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

MyNewStr: [Field1] & &quot;, &quot; & [Field2] & &quot;, &quot; & [Field3] & &quot;, &quot; & ... & Fieldn (in the query grid)

[Field1] & &quot;, &quot; & [Field2] & &quot;, &quot; & [Field3] & &quot;, &quot; & ... & Fieldn as MyNewStr (in SQL)


BUT, something is awry. Thnis shouldn't be noticably 'slow' in code (by itself).

In fact, I would probably do the actual concatenation of the many fields in code - even if I used a query to generate the 'final' record. It is just too difficult for me to see the darn query/grid stuff and doing the SQL in code doesn't usually help execution time.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[abombss]

Thanks for the help Michael, but I think you misunderstood what I was trying to do. I do not want to concatinate multiple fields, rather I want to concatinate one field from multiple records grouped on a key field.

Using the code is much slower b/c I have to first open a dynaset table, then I have to move through every record in the table using the move next method. This is not the most efficient way to traverse through a recordset. The algorithm I am using looks something like this.


Do while not rs.eof
strKey = rs!Key
flgEndLoop = false
strServices = rs!Service
rs.MoveNext
Do while not rs.eof and not flgEndLoop
if rs!key = strKey then
strServices = strServices & &quot; &quot; & rs!Service
rs.MoveNext
Else
flgEndLoop = True
end if
Loop

rsFinal!Service = strServices
Loop

If anyone knows a faster or better way to accomplish this I would greatly appreciate it.
[sig]<p>abombss<br><a href=mailto:abombss@hotmail.com>abombss@hotmail.com</a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

gb18,

sorry about the mis-understanding.

Form the code fragment, the one thing I see is that you are traversing the entire recordset, looking for matches from the first field in the second field. This - of course - assumes rs is ordered on these fields. This is remenicient of the product of a crosstab query. You could try this, and - for each record in the crosstab, go back to the scheme I mentioned in My first post, just be sure to - somehow - take care of nulls and blanks.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]