Field concatenation into a combo box

Hello Gurus,



I have inherited an "Animal Info" database (in Access 2003).



It has a form which enters information into an animal "Pedigree Table" which
is the main part of the database.



In the form there is a combo box which runs a query from a separate "Person
Table".



"Person Table" has 5 fields:

Last Name, Title, First Name, Address, Postcode



The query lists the "Person Table" in Last Name order and I can select a
Last Name for the combo box, which is then entered into the "Owner" field of
the "Pedigree" table.



Instead of it just entering the Last Name of the Owner, I would like to be
able to concatenate and enter "Title, First Name, Last Name" into the
"Owner" field.



Can it be done?



Can you explain how - to a computer literate but Access Newbie?



Many TIA



Joskin

"Joskin" reply@newsgroup wrote in message
...
Hello Gurus,

I have inherited an "Animal Info" database (in Access 2003).

It has a form which enters information into an animal "Pedigree Table"
which is the main part of the database.

In the form there is a combo box which runs a query from a separate
"Person Table".

"Person Table" has 5 fields:

Last Name, Title, First Name, Address, Postcode

The query lists the "Person Table" in Last Name order and I can select a
Last Name for the combo box, which is then entered into the "Owner" field
of the "Pedigree" table.

Instead of it just entering the Last Name of the Owner, I would like to be
able to concatenate and enter "Title, First Name, Last Name" into the
"Owner" field.

Can it be done?

Can you explain how - to a computer literate but Access Newbie?


This can be done by the simple expedient of changing the combo box's
rowsource query so that it concatenates all the fields you want into one
field. The table design has a serious flaw, though, in that the only way
you can confidently distinguish between two people with the same first and
last names is to include all the other fields in the table as well, but
addresses are very much subject to change. How do you know that the "John
Smith, 123 Main St." you have in one pedigree entry is the same person as
"John Smith, 321 Minor St.", when John Smith relocated between the times the
entries were made?

It would be better to have a system-assigned autonumber key in the Person
table, or else a compound index field that would probably include last name,
first name, and a "name-distinguisher" field. Then you would store the key
field(s) of the Person record in the Pedigree record, rather than any
specific concatenation of the Person data.

Queries could easily pull up the information about the person whenever you
want it, formatted however you'd like to see it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

See this article for how to write the value from a combobox into a textbox
on a form:
http://www.mvps.org/access/forms/frm0058.htm

You would simply modify the code to concatenate the desired columns' values:

Me.TextBoxName.Value = Me.ComboBoxName.Column(2) & ", " & _
Me.ComboBoxName.Column(1) & ", " & Me.ComboBoxName.Column(0)

assuming that Title is in the third column of the combo box query, first
name is in the second column of the combo box query, and last name is in the
first column of the combo box query.
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/





"Joskin" reply@newsgroup wrote in message
...
Hello Gurus,



I have inherited an "Animal Info" database (in Access 2003).



It has a form which enters information into an animal "Pedigree Table"
which is the main part of the database.



In the form there is a combo box which runs a query from a separate
"Person Table".



"Person Table" has 5 fields:

Last Name, Title, First Name, Address, Postcode



The query lists the "Person Table" in Last Name order and I can select a
Last Name for the combo box, which is then entered into the "Owner" field
of the "Pedigree" table.



Instead of it just entering the Last Name of the Owner, I would like to be
able to concatenate and enter "Title, First Name, Last Name" into the
"Owner" field.



Can it be done?



Can you explain how - to a computer literate but Access Newbie?



Many TIA



Joskin

Dirk / Ken,

Many thanks for your pointers (and cautions).

Festive good wishes to you both,

Joskin

On Wed, 24 Dec 2008 19:06:25 -0000, "Joskin" reply@newsgroup wrote:

Hello Gurus,



I have inherited an "Animal Info" database (in Access 2003).



It has a form which enters information into an animal "Pedigree Table" which
is the main part of the database.



In the form there is a combo box which runs a query from a separate "Person
Table".



"Person Table" has 5 fields:

Last Name, Title, First Name, Address, Postcode

Does it not have a Primary Key, a person ID!? It certainly should.



The query lists the "Person Table" in Last Name order and I can select a
Last Name for the combo box, which is then entered into the "Owner" field of
the "Pedigree" table.



Instead of it just entering the Last Name of the Owner, I would like to be
able to concatenate and enter "Title, First Name, Last Name" into the
"Owner" field.

Well... that would be one way to manage the problem of two different owners
who both happen to be named Jones, but it's certainly not a good relational
design. Storing data redundantly (in the Pedigree table and the Person table)
is a bad idea; storing multiple values in one field is an even worse idea!

I'd REALLY recommend adding some sort of unique ID field to the Person table -
an Autonumber would do; and storing a Long Integer PersonID field in the
Pedigree table, using that ID as the bound column of the combo box. You can
then create a query joining the two tables to see the owner's personal
information in conjunction with the pedigree information.
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Wed, 24 Dec 2008 19:06:25 -0000, "Joskin" reply@newsgroup wrote:

Hello Gurus,

I have inherited an "Animal Info" database (in Access 2003).

It has a form which enters information into an animal "Pedigree Table"
which
is the main part of the database.

In the form there is a combo box which runs a query from a separate
"Person
Table".

"Person Table" has 5 fields:

Last Name, Title, First Name, Address, Postcode

Does it not have a Primary Key, a person ID!? It certainly should.

The query lists the "Person Table" in Last Name order and I can select a
Last Name for the combo box, which is then entered into the "Owner" field
of
the "Pedigree" table.

Instead of it just entering the Last Name of the Owner, I would like to be
able to concatenate and enter "Title, First Name, Last Name" into the
"Owner" field.

Well... that would be one way to manage the problem of two different
owners
who both happen to be named Jones, but it's certainly not a good
relational
design. Storing data redundantly (in the Pedigree table and the Person
table)
is a bad idea; storing multiple values in one field is an even worse idea!

I'd REALLY recommend adding some sort of unique ID field to the Person
table -
an Autonumber would do; and storing a Long Integer PersonID field in the
Pedigree table, using that ID as the bound column of the combo box. You
can
then create a query joining the two tables to see the owner's personal
information in conjunction with the pedigree information.
--

John W. Vinson [MVP]

Many thanks, John - the relational side of this database seems to be non
existent at the moment but it DOES work (in a limited way). I hope to
change it to a better 'layout' but I'm rather scared of making big changes
with my very limited understanding of Access (I've already made one change
that I had to roll back because of unforeseen results!).

I plan to make a couple of copies of the database & play with those until I
am more competent and can achieve the desired results.

Thanks for your pointers - I'll go and practice.

Joskin