Multiple PHone Numbers

[Propoflady]

My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

[ghetto_banjo]

This can be done via a cross tab query. I think you will need to
query for one more additional data than just name and phone, as the
crosstabs require 3 fields. Here is an example of what i did with
Name, Phone, and Type (cell, work etc)

New Crosstab query wizard. Choose your table OR a query that pulls
the info fields you need.

Row Header = [Name]
Column Header = [Type]
Choose "First" for the function on the [Phone] Field.

Uncheck the box for "Yes, Include row sums".
Then that should do it for you.

[Roger]

On Jun 10, 8:46*am, Propoflady <cha...@monticu le.comwrote:

My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

what is the structure of your data, how many tables

[Mark]

You need to create a function, Function StrTelephoneNum ber(ClientID) As
String that creates a string of the multiple telephone numbers for a given
Client ID and then put the function in a standard module. Then you base your
form or report on a query and in an empty field of the query you put:
ClientPhoneNumb ers:StrTelephon eNumber(ClientI D).

Steve

"Propoflady " <ch****@monticu le.comwrote in message
news:c9******** *************** ***********@l64 g2000hse.google groups.com...

My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

[Phil Stanton]

You will need a query which calls a function to retrieve the numbers

In the query based on Clients you probably will have
ClientLastName, ClientFirstName , ClientID
Add a Field PhoneNos(Client ID)
I assime you have a table called "Phones" with ClientID and PhoneNo

Then in a module Air Code

Option Explicit
Option Compare Database

Function PhoneNos(Client ID as long) As String

Dim MyDb as database
Dim PhoneSet as RecordSet
Dim SQLStg as string, PhoneStg as String

SQLStg = "SELECT Phones.* FROM Phones WHERE ClientID = " & ClientID

Set MyDb = currentDb
Set PhoneSet = Mydb.OpenRecord set(SQLStg)

With PhoneSet
Do until .EOF
PhoneStg = PhoneStg & !PhoneNo & ", " ' Add a comma &
space
. MoveNext
Loop
.Close
Set PhoneSet = Nothing
End With

PhoneNos = Left(PhoneStg, Len(PhoneStg) -2) ' Remove last comma &
space

exit function

HTH

Phil
"Propoflady " <ch****@monticu le.comwrote in message
news:c9******** *************** ***********@l64 g2000hse.google groups.com...

My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

[Tony Toews [MVP]]

Propoflady <ch****@monticu le.comwrote:

>My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

If you only need them on a report then consider a multi-column subreport.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Chuck]

On Tue, 10 Jun 2008 07:46:43 -0700 (PDT), Propoflady <ch****@monticu le.com>
wrote:

>My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables - client
and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

Put a txt box in your report. Set the width of the box to the maximum your
report can use. Delete the label that goes with the box.
For the control source of the txt box use:

=[contact a] & " - " & iif([phone number 1] is not null, ", " &[phone number
1], "") & iif([phone number 2] is not null, ", " &[phone number 2], "") &
iif([phone number 3] is not null, ", " &[phone number 3], "") & iif([phone
number 4] is not null, ", " &[phone number 4], "")

etc, etc, etc

Just be sure there is sufficient horizontal space in your report. This will
not move any other controls to the right of the phone numbers. If you set the
width of the txt box to something less than what is needed for all the numbers
and allow the box to grow, you will be right back to where you are now, which
you don't want.

As you can see. Many ways to skin a cat. And they will all get you to where
you want to be.

Chuck
--

[Bob Quintal]

Propoflady <ch****@monticu le.comwrote in
news:c9e72023-ad32-4cf6-aea9-efe31c23ea67
@l64g2000hse.go oglegroups.co
m:

My contacts can have as many as five or six phone numbers - is it
possible to make a query that puts the name, and each phone number
after it on the same line - for this reason I have two tables -
client and phone linked by client id

right now I get
contact a - phone number 1
contact a - phone number 2

I would like
contact a - phone number1, phone number 2

Dev Ashish has published the fConcatChild function to do exactly
what you want.
http://www.mvps.org/access/modules/mdl0004.htm

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **