Combine Customer Names

[Guest]

Hi, All
I have a customer contact info database, "Customer Name" & "Spause Name",
address, city..., when i am printing a report i like to combine "Customer
Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
make it show "David & Betty Howe" on my report?
thanks Carol.

 

[Brendan Reynolds]

If you are storing the customer's full name and the spouse's full name in
one field each, then there isn't any very reliable way to do it. You could
look for the space between first and last name, but that isn't always
reliable, because there can be more than one space within a name ('Billy
Bob', 'Mary Kate', 'van der Vorst' etc.).

You can do it easily enough if you store the first and last names in
separate fields, e.g. [CustomerFirstName] & " and " & [SpouseFirstName] & "
" & [CustomerLastName]. However, this assumes that the customer's last name
and the spouse's last name are the same. Do you really think that is a safe
assumption to make?

 

[Stefan Hoffmann]

hi Carol,

I have a customer contact info database, "Customer Name" & "Spause Name",
address, city..., when i am printing a report i like to combine "Customer
Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
make it show "David & Betty Howe" on my report?

Not really, because you have to extract the single strings, which is a
kind of sophisticated task.

Normally these kind of data should be stored normalized in your tables
(in separated fields):

Table Customer:
---------------
CustomerFirstName
CustomerSurname
SpouseFirstName
SpouseSurname

So you can create a query like this and use it as record source for your
report:

SELECT
IIf(CustomerSurname = SpouseSurname,
CustomerFirstName & " & " & SpouseFirstName & " " &
CustomerSurname,
CustomerFirstName & ", " & CustomerSurname &
SpouseFirstName & ", " & SpouseSurname)
FROM Customer

mfG
--> stefan <--

 

[Al Campagna]

Carol,
It's really your table design taht is causing this problem to begin with. It strongly
suggest that you fix that first, than to struggle with this problem again and again in the
future.
Right now, you can't even sort your Customers by Last Name/First Name.

You should create a CustFirstName and CustLastName and a SpouseFirstName field in your
table, and break out those values into their respective discreet fieldss.. (avoid spaces
in field names)

Then this simple concatenation would do the job...
= CustFirstName & " & " & SpouseFirstName & " " & CustLastName

 

[Guest]

sorry, i just post the same question.
thank you all for replying, that's my book says have to create with last &
first name...and this that, I know i should done it when i create the
datebase, now it's to hard to change it there are about 2000 records. i have
one more question, how to combine the address, ie. city & state & zipcode in
one line, thank you.

 

[Rick B]

=[City] & ", " & [State] & " " & [ZIP]

 

[Guest]

thank you very much Rick.

=[City] & ", " & [State] & " " & [ZIP]


--
Rick B

sorry, i just post the same question.
thank you all for replying, that's my book says have to create with last &
first name...and this that, I know i should done it when i create the
datebase, now it's to hard to change it there are about 2000 records. i
have
one more question, how to combine the address, ie. city & state & zipcode
in
one line, thank you.

 

[Al Campagna]

Carol,
Not sure what your "book" says to do, but I think you can see that "discreet" fields
are the right way to go.
To do as you wanted originally...
** This will only work with data that has a firstname and a lastname in both fields,
separated by a space**
Any initials, or commas will cause the concatenation to display incorrectly...
(I'll use CustName SpouseName for my example)

=Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
")) & Mid([CustName],InStr([CustName]," "))

 

[Guest]

Thank you Al Campagna for your help. I used your example, and found there is
another problem, if customer has no spouse, so there will be error showing,
sometimes mexicans customers even has 2 last names, so i'm going to do the
wright way, i'm going to add a field for first, last names and spousefirst
names, lst names, i really appreciate your help.

Carol,
Not sure what your "book" says to do, but I think you can see that "discreet" fields
are the right way to go.
To do as you wanted originally...
** This will only work with data that has a firstname and a lastname in both fields,
separated by a space**
Any initials, or commas will cause the concatenation to display incorrectly...
(I'll use CustName SpouseName for my example)

=Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
")) & Mid([CustName],InStr([CustName]," "))

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

sorry, i just post the same question.
thank you all for replying, that's my book says have to create with last &
first name...and this that, I know i should done it when i create the
datebase, now it's to hard to change it there are about 2000 records. i have
one more question, how to combine the address, ie. city & state & zipcode in
one line, thank you.

 

[Al Campagna]

Carol,
I hear what your saying, but this is all a consequence of a table design problem.You're going to go nuts trying to program around all the data that does not conform to
the above rule.

You can break out the current data into discreet fields using Update queries. It's not
simple, but it's probably easier than struggling with what you have now, and worth the
investment. (hand editing 2000 records) I can't go into ALL the details, but here's a
snapshot on how to do it using String functions (Left, Mid, and InStr... so bone up on
those)

How to break "Bob Smith" into "Bob and Smith"
Add two new fields to your table... CustomerFirstName and CustomerLastName
1. Create an update query against the table that updates the new FirstName field with
Left(CustomerName, Instr(CustomerName, " ") - 1)
that puts "Bob" in the new CustomerFirstName field. Goodness...
2. Create an update query (or combine this with the previous query) that updates the
CustomerLastName field with...
Mid(CustomerName, Instr(CustomerName, " ") + 1)
that puts "Smith" in the new CustomerLastName field.
3. Repeat this same process for SpouseName to a new SpouseFirstName and
SpouseLastName field (if SpouseLastName is really needed... maybe just a firstname would
do... your call)

Expected result...
CustomerName CustomerFirst CustomerLastName
Bob Smith Bob Smith
SpouseName SpouseFirstName SpouseLastName
Mary Smith Mary Smith

This won't wotk on all your records, but I'll bet it will do most of them. You would
then hand edit those names that don't conform, and finally delete the old Name fields
altogether. Then you are all set to go... labels, sorting, finding names, everything will
be a breeze!
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Thank you Al Campagna for your help. I used your example, and found there is
another problem, if customer has no spouse, so there will be error showing,
sometimes mexicans customers even has 2 last names, so i'm going to do the
wright way, i'm going to add a field for first, last names and spousefirst
names, lst names, i really appreciate your help.

Carol,
Not sure what your "book" says to do, but I think you can see that "discreet" fields
are the right way to go.
To do as you wanted originally...
** This will only work with data that has a firstname and a lastname in both fields,
separated by a space**
Any initials, or commas will cause the concatenation to display incorrectly...
(I'll use CustName SpouseName for my example)

=Left([CustName],InStr([CustName]," ")) & "& " & Left([SpouseName],InStr([SpouseName],"
")) & Mid([CustName],InStr([CustName]," "))

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

sorry, i just post the same question.
thank you all for replying, that's my book says have to create with last &
first name...and this that, I know i should done it when i create the
datebase, now it's to hard to change it there are about 2000 records. i have
one more question, how to combine the address, ie. city & state & zipcode in
one line, thank you.

:

Carol,
It's really your table design taht is causing this problem to begin with. It
strongly
suggest that you fix that first, than to struggle with this problem again and again
in
the
future.
Right now, you can't even sort your Customers by Last Name/First Name.

You should create a CustFirstName and CustLastName and a SpouseFirstName field in
your
table, and break out those values into their respective discreet fieldss.. (avoid
spaces
in field names)

Then this simple concatenation would do the job...
= CustFirstName & " & " & SpouseFirstName & " " & CustLastName
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi, All
I have a customer contact info database, "Customer Name" & "Spause Name",
address, city..., when i am printing a report i like to combine "Customer
Name & Spause Name", ie "David Howe" & "Betty Howe", is there some way to
make it show "David & Betty Howe" on my report?
thanks Carol.