Combining Records in a Report

monitor · April 14th, 2004, 09:25 AM

I would like to group records from a query together in a line rather than in a table in a report.

I have two tables, one Territory and the other Territory Groups
i.e
Territory
Australia
Argentina
Belgium etc.

and

TerritoryGp
North America
Europe
etc.

They are linked by a Territory Detail table on a 1 to many, many to one basis

I have set up the following query to create the following output
SELECT tblTerritoryGp.cTerritoryGp, tblTerritory.cTerritory
FROM tblTerritory INNER JOIN (tblTerritoryGp INNER JOIN tblTerritoryDetail ON tblTerritoryGp.nTerritoryGpID = tblTerritoryDetail.nTerritoryGpID) ON tblTerritory.nTerritoryID = tblTerritoryDetail.nTerritoryID;

TerritoryGp Territory
North America Canada
North America U.S.A.
North America Mexico
Europe UK
Europe France
Europe Italy etc.

I would however like to express these records in a report as follows:-

North America Canada, U.S.A., Mexico
Europe UK, France, Italy, etc.

How can I combine the territory records per territory group to print out in one text box with commas between them?

Thanks for your help.

Steven · April 14th, 2004, 07:02 PM

You'll probably be wanting to use a recordset that determines the textbox ro label value when the report is formatted, so you have something like:

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rst as Recordset
Dim strValues as String
set rst = CurrentDB.OpenRecordSet( _
  "SELECT Territory FROM tblTerritoryDetail WHERE " _
& "TerritoryGp = '" & Me.txtTerritoryGroup & "'")

While Not rst.EOF
  strValues = strValues & ", " & rst("Territory")
  rst.MoveNext
Wend
'Strip off the first ", "
strValues = right(strValues, len(strValues)-2)
Me.lblYourLabel.Caption = strValues
End Sub

Of course, you'll most likely have to change a fair bit of the code, but it should point you in the right direction.
This is NOT a definitive answer, just something off the top of my head. (Thought I should put that disclaimer in lest I be rebuked by JÃ¼rgen again)
:)

Steven

I am a loud man with a very large hat. This means I am in charge