Combining rows into a single row

[NCYankee1]

In Access 97 I have a table that looks like this:

IncidentNumber text
SequenceNumber integer
Description memo

If I had data like this (I'll separate by commas for readability):
A0000001,1,Blah blah blah blah.
A0000002,1,This is the 1st desc.
A0000002,2,This is the 2nd desc.
A0000003,1, yadda yadda yadda yadda.

I have to produce a report based on this table, but the user wants it to look like this:
Incident Description
======== ============================================
A0000001 Blah blah blah blah.
A0000002 This is the 1st desc. This is the 2nd desc.
A0000003 yadda yadda yadda yadda

Notice how incident A00000002's rows are combined into one entry on the report. How would I query this table to combine the rows where needed?

As always, thanks for all your help!

 

[jjonesal]

Create an unbound textbox control on the report. Then, set the control source property of the textbox to:

=[IncidentNumber] & " " & [Description]

Good luck!

 

[PaulF]

probably the best bet would be to create an additional table, with two fields to hold the IncidentNumber and Combined Descriptions
This example use a table named tblIncidents with the same 3 fields as yours, and also has an index named idxIncidents based on IncidentNumber And SequenceNumber. It also has a new table with the name tblIncidentsCombined that has two fields in it, Incident and Data
Then base the report on this new table "tblIncidentsCombined"


Dim db As DAO.database
Dim rstOrig As DAO.Recordset, rstCombined As DAO.Recordset
Dim strIncident As String
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * From tblIncidentsCombined&quot
DoCmd.SetWarnings True
Set db = CurrentDb
Set rstOrig = db.OpenRecordset("tblIncidents&quot
Set rstCombined = db.OpenRecordset("tblIncidentsCombined&quot
With rstOrig
.MoveFirst
Do While Not .EOF
With rstCombined
If rstOrig!IncidentNumber = strIncident Then
rstCombined.MoveLast
rstCombined.Edit
Else
rstCombined.AddNew
rstCombined!incident = rstOrig!IncidentNumber
End If
rstCombined!Data = IIf(IsNull(rstCombined!Data), rstOrig!Description, rstCombined!Data & " " & rstOrig!Description)
rstCombined.Update
strIncident = rstOrig!IncidentNumber
End With
rstOrig.MoveNext
Loop
End With
MsgBox "Done"
Set db = Nothing
Set rstOrig = Nothing
Set rstCombined = Nothing

PaulF