concatenation? or rollup of info?

[krismtx]

I have imported insurance loss information from another database. For some reason, each Loss_ID, can have more than one Loss_Description.
Ex:
12300 The patron fell on the floor
12300 while he ate his burger
12588 The child slipped on the steps

How do I combine all of the descriptions with the same ID number? Some have 4 lines of descriptions, and the same id!
Thanks very much in advance!

 

[shacket]

There may be a more efficient way, but here is a long way around. The only issue I really see is concatenating the fields in the right order. You wouldn't want your sample to end up saying "while he ate his burger The patron fell on the floor". (Well, that one kind of works, but you get the idea!)

With an update query, add the table to the query design grid 4 times. They will look like this:

tblYourTableName
tblYourTableName_1
tblYourTableName_2
tblYourTableName_3

Link all of them to each other by the Loss_ID. This will give you only the records that have 4 entries in the table. Concatenate them and delete the other 3 entries.

Do the same thing with 3 tables (i.e. 3 entries) and then 2.

HTH

 

[krismtx]

I can't wait to try this!
Thanks so much!

 

[krismtx]

I have added the table 4 times.
I've added Loss_id to the first grid.
Do I create a new field in the second grid called Concatenated_Description:[Loss_Description]&" "&[Loss_Description],etc.??
TIA!
Kris

 

[shacket]

To be safe, I would create a new field and update to that field. That way you are not overwriting information that you may need later. (Once finished, you would delete the old field and use the new one)

For the concatenation, you want to use an update query. So you would choose the new field you created and in the "Update To" line, write:

[tblYourTableName].[Loss_Descripton] & " " & [tblYourTableName_1].[Loss_Description] & ...

You need to tell it which of the 4 "tables" to draw from.