Making phone numbers display in one row 1

[RLA]

Hi,

I am writing a report which lists clients and their phone numbers. I would like it to display as one row --
Client Name Phone 1 Phone 2 Phone 3 Phone 4

However, the phone numbers are stored in a separate table then the client info and when I join them, the query ends up with multiple rows per client (a row for each phone #). How can I stop this?

Thanks very much for the help!

 

[scriverb]

Assuming the following:
TABLES:
tblClients
ClientID Number Long
ClientName Text 30
tblPhoneNumbers
ClientID Number Long (primary Index)
PhoneNumber Text 10

DATABASE MODULE:
Function PhoneString(vClientID As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vPhoneString As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblPhoneNumbers", dbOpenDynaset)
vPhoneString = ""
rs.FindFirst "[ClientID] = " & vClientID
Do
If Not rs.NoMatch Then
vPhoneString = vPhoneString & Format(rs("PhoneNumber&quot, "(@@@)@@@-@@@@&quot & " "
rs.FindNext "[ClientID] = " & vClientID
Else
vPhoneString = "No Phone Numbers"
End If
Loop Until rs.EOF Or rs.NoMatch
PhoneString = vPhoneString
rs.CLOSE
db.CLOSE
End Function

Create a query and use this query SQL:
SELECT tblClients.ClientID, tblClients.ClientName, PhoneString([tblClients]![ClientID]) AS Phones
FROM tblClients;

You may have to adjust some of the table and field names to match what you have but this works with my temp tables and data. Let me know if you need any more assistance installing this.
Bob Scriver

 

[RLA]

Thank you very much, Bob!
I can't get this to work, however; I am getting a run-time error 'data type mismatch.' My contact_id field is not an integer (it's text) but I tried changing it to a number and I'm still getting the error. Sorry, I'm not a programmer, so I'm not sure how to debug the code -- I've never seen the rs. command before.
Thank you again!

 

[scriverb]

You can leave it as text or change it to numeric that is your choice. But, the code can handle it either way.

If your Contact_ID is numeric use this format:
rs.FindFirst "[Contact_ID] = " & vClientID

If text then use this format:
rs.FindFirst "[Contact_ID] = '" & vClientID & "'"

Also, change the Function statement to match the data type:
Function PhoneString(vClientID As Integer) As String

If numeric leave this statment as is as a Integer value is being passed. If text(string) then change the red code to String.


Bob Scriver

 

[RLA]

Hi Bob,
One more question -- it runs, but there's nothing in the Phones field. Where do the phone numbers fit in? I never actually name that field anywhere.

 

[scriverb]

The query returns a column with the phone numbers string in it. I supplied this code but you should update it with the correct name of the ClientID field. Update the correct table name and the correct field name(Contact_ID):

SELECT tblClients.ClientID, tblClients.ClientName, PhoneString([tblClients]![ClientID]) AS Phones
FROM tblClients;


Bob Scriver

 

[RLA]

Hi Bob,
Sorry to be so thick, but I used your code and it comes back blank. I changed the field names to what (I think) is correct; my main table is called tblContacts2 and the join field is [contact_id]; my phone numbers table is called [tblPhone_Numbers] and the join field is the same [contacts_id].

 

[scriverb]

The query SQL should read:
SELECT tblContacts2.contact_id, tblContacts2.Contacts_Name, PhoneString([tblContacts2]![contact_id]) AS Phones
FROM tblContacts2;

Please check the name field to see if I have it named correctly.

This line of the Function should be changed to match your table name:
Set rs = db.OpenRecordset("tblPhone_Numbers", dbOpenDynaset)

Now the rest has to do with the field type of your Contact_ID field. Follow my instructions above.

Bob Scriver

 

[RLA]

Thank you very much!

 

[scriverb]

Did it finally work? Bob Scriver

 

[RLA]

Yes, it did. Thank you.

 

[carfj1]

This is what I was needing, you deserve a Star

Thanks
Carlos