How can i get only one entry in a one-to-many relationship?

[ruthcali]

i am using Access 97 in a Novell environment.<br><br>i am trying to produce a report using two tables.&nbsp;&nbsp;<br><br>Table1:&nbsp;&nbsp;&nbsp;Main Table with a field called ID <br>Table2:&nbsp;&nbsp;&nbsp;Work Ticket Table with a field called ID.&nbsp;&nbsp;<br><br>The IDs have a one-to-many relationship.<br><br>So, in Main table, i have a ID number (example: 1) and in the Work Ticket table, i might have two or three entries relating to ID 1:&nbsp;&nbsp;<br><br>for example,<br>ID:1<br>Type of ticket: Iron<br><br>ID: 1<br>Type of ticket: Power<br><br>ID:1<br>Type of ticket: Fiber<br><br>In my report, i want just one line for ID 1. i would like a text box that displays all the types of tickets for each ID.&nbsp;&nbsp;<br>So, in my report, i would like just one line for ID# 1<br><br>ID:1<br>Type of tickets created: Iron, Power, Fiber<br><br>Any ideas?&nbsp;&nbsp;i keep getting 3 lines in my report, one for:<br>ID:1<br>Type: Iron<br><br>ID:1<br>Type: Power<br><br>ID:1<br>Type: Fiber<br><br>But, i want just one line in my report.<br>ID:1<br>Type: Iron,Fiber,Power<br><br>Hope i wasn't too confusing.&nbsp;&nbsp;please help!<br><br>thanks in advance.

 

[jimmythegeek]

Create a function that will return a string to your report:

In the control source of your ticket type field put the following:

=GetTicketType(Me.ID)

Make sure you have the ID field in the details section, it is fine if it is hidden.

Now copy the following function into a blank module, changing the red areas appropriately. Close and save the module as anything you want.

===============
Public Function GetTicketTypes(IDNo As Long) As String
Dim db As Database
Dim rs As Recordset
Dim strSQL As String, strTypes As String

strSQL = &quot;SELECT Type FROM TableName WHERE(((ID)= &quot; & IDNo & &quot);&quot;
strtypes = &quot;&quot;

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
If strTypes = &quot;&quot; Then
strTypes = !Type
Else
strTypes = strTypes & !Type
End If
.MoveNext
Loop
GetTicketTypes = strTypes
Else
GetTicketTypes = &quot;&quot;
End If
End With

rs.CLOSE
db.CLOSE
End Function
===============

Now you should get a string listing all of the types.
Let me know if you have any trouble

 

[ruthcali]

Wow. thank you so much. i can't wait to try it out. that is very nice of you to help me. i really appreciate it!!