Combining many records in one field

[Ulcom]

Hi
I have 2 Tables
Tbl Main that contain 5 fields ID, Name, ....
TblSecond that contain 2 fields ID and City

For each ID in the second table I can have many cities (Toronto, Atlanta,
Vancouver, etc) .
Some ID can have as much as 20 cities

TblMais has 50 records
TblSecond has 600 records.

When i make a query with the 2 tables (ID is the link) I end with 600
records.
Instead I would like to end with 50 records but the query should contain one
field that would combine all the cities for each ID I guess in one
calculated field
Can that be done within a query ? or do I need code to do it ?

This is to be able to print a report on 1 page instead of 10

thanks

 

[Sandy H]

I think the best way to accomplish this would be to have a function in a
module like below.

Function getCountry(lngCountryID as Long) as String

dim rs as recordset
dim strCountries as string
set rs = dbengine(0)(0).OpenRecordset ("SELECT FROM tblCountries WHERE
NameId = " & lngCountryID)
While Not rs.EOF
If strCountries = "" then
strCountries = rs!Country
else
strCountries = strCountries & ", " & rs!Country
end if
Wend
rs.Close

if strCountries = " " Then
strCountries = "None"
end if

getCountry = strCountries

End Function

Create a field in your query to call this function. Ie CountryName:
getCountry(NameId)

Sandy