Help needed: How do I combine fields into one?

[kdagostino]

I have a table with a field for each participant's first and last name (i.e.
First1, First2, First3, First4, Last1, Last2, Last3, Last4, etc). How do I
create a query that will have two fields, First and Last, and combine all the
names into them?

 

['69 Camaro]

Hi.

I have a table with a field for each participant's first and last name (i.e.
First1, First2, First3, First4, Last1, Last2, Last3, Last4, etc). How do I
create a query that will have two fields, First and Last, and combine all the
names into them?

First, you must redesign your table. It should have at a minimum a primary
key (an AutoNumber is fine for this), the FirstName field, and the LastName
field. Each additional participant beyond the first participant would be
added by merely adding a new record, not a new pair of fields to the same
record. With this far more flexible table structure, you could create a new
query and place the following in the "Field" cell in the design grid:

FullName: FirstName & " " & LastName

Open the query in Datasheet View and it will show all of the participants
names, first name followed by a space, then the last name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[kdagostino]

I'm sorry. I should have been more specific. Those fields are within a
larger table. It has a primary key(autonumber) and each record is a
different conference. There are a ton of other fields for various
information regarding those conferences. 12 of them are for the names of the
people who went to them (6 First Name fields and 6 Last Name fields).

I have a search form with the first and last name of the individual, (to
search for a conference attended by ______) and I want to turn that into a
combo box with all the previously entered names as choices. As I understand,
that has to be a specific column within a table. I want to combine all the
first names that have been entered into one column and all the last names
that have been entered into another.

How I'm going to search all six fields using one criteria is a problem I
haven't gotten to yet.

 

[Brendan Reynolds]

I'll answer your original question, and hope that the answer will help to
illustrate why Gunny is correct, and you really, really, really need to
redesign that table.

Given your existing design, the answer to your original question is ...

SELECT First1, Last1
FROM tblTest
UNION SELECT First2, Last2
FROM tblTest
UNION SELECT First3, Last3
FROM tblTest
UNION SELECT First4, Last4
FROM tblTest
UNION SELECT First5, Last5
FROM tblTest
UNION SELECT First6, Last6
FROM tblTest;

If you decide to take our advice and redesign the table, you'll find links
to useful resources on database design here ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;289533

 

['69 Camaro]

Hi.

How I'm going to search all six fields using one criteria is a problem I
haven't gotten to yet.

You've built your house upside down and now you're wondering how to use the
toilet when it's "way up there." ;-)

Fortunately, rebuilding a database's table structure when it was designed
incorrectly isn't nearly as difficult or expensive as rebuilding a house that
was designed incorrectly.

The table with the conferences (tblConferences with ConfID -- example
primary key, ConfName, Location, ConfDate, et cetera) should only contain
information about the conferences. It shouldn't contain information about
everyone that attended, who all the keynote speakers were, when the next
conference is scheduled, et cetera.

Other related information should be stored in other related tables, such as
tblPersonnel storing the names of attendendees (PID -- example primary key,
FirstName, LastName, Phone, et cetera) and tblAttendance (AID -- example
primary key, PID -- from tblPersonnel for the person who attended, ConfID --
from tblConferences for the conference this person attended).

If you structured your database tables this way, you'd be able to:

1.) Create a JOIN query that the search form could use as the RecordSource
Property that lists the conference information and personnel information,
too; and

2.) Create a query listing the full name of each person; and

3.) Create your combo box based upon this query that allows the user to
select the person on the search form to search for. Please see the following
Web page for a link to Tom Wickerath's sep-by-step tutorial, "Using A Combo
Box To Find A Record":

http://www.Access.QBuilt.com/html/articles.html

The query for the search form's RecordSource Property could be as simple as:

SELECT tblPersonnel.PID, tblPersonnel.FirstName,
tblPersonnel.LastName, tblAttendance.AID,
tblConferences.ConfID, tblConferences.ConfName
FROM (tblPersonnel INNER JOIN tblAttendance ON
tblPersonnel.PID = tblAttendance.PID)
INNER JOIN tblConferences ON
tblAttendance.ConfID = tblConferences.ConfID;

The query for the combo box could be as simple as:

SELECT PID, (FirstName & " " & LastName) AS FullName
FROM tblPersonnel
ORDER BY LastName;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[kdagostino]

Thank you. I appreciate your help very much. I'm trying to train myself to
use Access and these boards are a tremendous help.

 

['69 Camaro]

You're very welcome. Access has a steep learning curve, so you'll have a
much easier time if you have someone who can show you the tricks and warn
you of the many pitfalls. If you can't find someone knowledgeable in
Access, then take a class. You'll be glad you did.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.