Record with multiple lines

[jenniferspnc]

So learning from the help i've gotten previously I've applied it to my
current situation, but now I've run into another road block.

Here's a sample of my table history (some fields just omitted for sake)
Countries (table)
Country_ID(PK)
Region_ID(FK)
Country
ISO_Code
Support_ID(FK)

tbl_supportdetails
Support_ID(PK)
email address
phone
fax

currencies
Currency_ID(PK)
Currency

tbl_currencybridge
UniqueID (PK)
Currency_ID(FK)
Country_ID(FK)

A country can be invoiced in many currencies and a currency can be tied to
many countries (the many to many relationship). Here's my problem: For
example, Albania invoices in USD and Euro which is showing two separate rows.
In my report I'll need it to show all currencies in one line item.

here is my sql statement (want it to show all countries regardless of
whether or not a currency is tied)
SELECT GlobalZoneTable.Country_ID, GlobalZoneTable.Country,
GlobalZoneTable.[serviced out of], GlobalZoneTable.[email Address],
GlobalZoneTable.[Local Freephone], GlobalZoneTable.[calling from abroad],
GlobalZoneTable.[Local Freefax], GlobalZoneTable.[sending a fax from abroad],
[tbl_currencybridge Query].Currency
FROM GlobalZoneTable LEFT OUTER JOIN [tbl_currencybridge Query] ON
GlobalZoneTable.Country_ID = [tbl_currencybridge Query].Country_ID;

So in my report I create a textbox b/c I want this data all in one field per
country
=([serviced out of])+Chr(13)+Chr(10) & ([email Address]+Chr(13)+Chr(10)) &
([Local Freephone]+Chr(13)+Chr(10)) & ([calling from abroad]+Chr(13)+Chr(10))
& ([Local Freefax]+Chr(13)+Chr(10)) & ([sending a fax from
abroad]+Chr(13)+Chr(10)) & ([Currency]+Chr(13)+Chr(10))
Again it's showing multiple entries for countries that have more than one
currency; whereas ideally it just would list all the currencies on one line
in the report.

Another workaround i thought was to put the currency textbox in the detail
section and put the above wrapping (minus currency) in the country
header...this results in the currencies being together, but not on the same
line, rather a listing (top to bottom).

Thanks for the help once again.

 

[S.Clark]

I think Duane Hookom's Concatenate function will help you.

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'