concatenate records

  • Thread starter Thread starter db
  • Start date Start date
D

db

so i followed these directions from Sidney Linkers and it works great:
http://groups.google.com/group/micr...304ee/740038393f778528?hl=en#740038393f778528

I'd like to add a twist i was hoping someone could help!!

[RecID] [SubID] [Description]
1 a Tree
1 a Shrub
1 b Plant
1 c Rose
2 a Plant
2 a Lily
2 b Shrub
3 a Herb
3 a Shrub
3 b Rose


results:

1a Tree, Shrub
1b Plant
1c Rose
2a Plant,Lily
2b Shrub
3a Herb, Shrub
3b Rose

using the method from Sidney Linkers above works great, but really need
to have it grouped by [SubID] AND [RecID]. Any ideas/help would be
greatly appreciated!!

thanks.db
 
This will work if you only have two description to work with. Use two
queries. I named the first one Query58 from Table20 so you can change
accordingly.

SELECT [RecID] & [SubID] AS Identification, First(Table20.Description) AS
FirstOfDescription, Last(Table20.Description) AS LastOfDescription
FROM Table20
GROUP BY [RecID] & [SubID];


SELECT Query58.Identification,
IIf([FirstOfDescription]=[LastOfDescription],[FirstOfDescription],[FirstOfDescription] & ", " & [LastOfDescription]) AS Type
FROM Query58;
 
concatenating the [recID] & [SubID] columns is the answer!! Thanks for
the response Karl. I just modified the other example in the link and
concatenated the columns to concatenate the records...wheeew.

in the second query, i created two new expressions that extract the
characters from the RecID and SubID out so i can link that as a
subreport for the main report.
 
Back
Top