Here is my problem it seems as if i

[kristin4]

Here is my problem it seems as if it should be easy, but I haven’t been able to figure it out:

I have two tables, and a link table between them. It is a one to many relationship. I try to display them in a report, but it shows the data on different lines, and I need it together on one line.

To clarify, this is the details of it:

Table RadioStations
one column radioStations
one column ID

table location
one column cities
one column city ID.

a link table
linking the cityID with the RadioStationID.

There can be up to five radio stations per city and I need it displayed something similar to this:
Toronto: CKNW / CKKK / CFPL etc.

If anyone could help me I would be very appreciative, since I need to have this project completed before the weekend and I have no idea how I am going to do it.

 

[Namsha]

I have used headers but that puts it on two different lines.....what about IIF functions? I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[kristin4]

Thanks for the reply Namsha. I have also tried headers and IIF statements (I've tried just about everything actually) and nothing works, IIF statements I cant write a proper one, and headers still create multiple lines. If anyone knows what kind of an IIF statement i should write, that would be great because it could work if I was smart enough to write one.

I think this is a typical problem with one to many relationships and access reports, I dont know why there arent more posts on the topic.

 

[sko]

Search Access help for multi-column reports.
Set the column layout to "Across, then Down"
Group by City
Go to the city header properties and set "New row or col" to "Before Section"

 

[danvlas]

Try the function below (change the table/field names if necessary):

Function ColumnToLine(argCityID)
On Error GoTo ErrInFunction
Dim myRs As DAO.Recordset
Dim ResultString As String
Set myRs = CurrentDb.OpenRecordset("Select RadioStations.RadioStations From RadioStations Inner Join LinkTable On RadioStations.RadioStationsID = LinkTable.RadioStationsID Where LinkTable.CityID = " & argCityID & ";&quot
If myRs.RecordCount > 0 Then
Do Until myRs.EOF
ColumnToLine = ColumnToLine & IIf(Len(ColumnToLine) = 0, "", "/&quot & myRs("RadioStations&quot
myRs.MoveNext
Loop
End If
FinishPoint:
On Error Resume Next
myRs.Close
Set myrs=Nothing
Exit Function
ErrInFunction:
ColumnToLine = "Error: " & Err.Description
Resume FinishPoint
Exit Function
End Function

HTH,
Dan

 

[kristin4]

I got it

I managed to get it working, it was something similar to what Dan was doing. Incase anyone with a similar problem finds these posts, you should read "Q141624 - ACC How to Concatenate a List of Items from a Many-Side Table" in the microsoft knowledge base, I found it really helpful.

Thanks everyone for your help!