Access - Combine Data in a Query

[Al Mackay]

Been searching through the newsgroup and can't see any reference to
this, so don't know if this is even possible.

I have two tables of data that I'm querying. One contains general
details about problems, the second is a country table.

e.g. Problem Table:
Prob #, Summary, Type, Problem Owner

Country Table:
Source Country, Additional Countries Impacted

The Prob # exists within each table and is therefore how this data
links in.

If one problem has several countries impacted, each country will be
duplicated on a new row, e.g.

Orig:
Impacted:
PL1, Summary of the Prob is...., Technical, Al Mackay, England, France
PL1, Summary of the Prob is...., Technical, Al Mackay, England,
Germany
PL1, Summary of the Prob is...., Technical, Al Mackay, England, Italy

What I would like to be able to do is combine the results, if multiple
countries impacted exists, as per the example below:

PL1, Summary of the Prob is...., Technical, Al Mackay, England,
France; Germany; Italy

(therefore only one row will come out but will still contain all of
the data).

Is this possible in Access?, alternatively can MS Query do this @ all?

Many Thanks, Al. ( (e-mail address removed) )

 

[Chris Large]

You can do this by creating a Public Function in a Module:-

try the following (air-code), which assumes a table called
tblCountry and fields called Prob# and Impacted:-

Public Function GetImpacted(ByVal lngProb As Long) As
String
Dim rst As DAO.Recordset
Dim str As String

str="SELECT Impacted FROM tblCounrty WHERE Prob#=" &
lngProb

Set rst = CurrentDb.OpenRecordset(str)

str = ""

While Not rst.EOF
str = str & rst!Impacted & ", "
rst.MoveNext
Wend

If Len(str) > 0 Then str = Left(str, Len(str) - 2)

rst.Close
Set rst = Nothing

GetImpacted = str

End Function


You can then use this GetImpacted function in a calculated
field in a query, just as you would another other function,
i.e.

Impacted Countries:GetImpacted([Prob#])


hth

Chris