Concatenating Fields

[Matt]

Hi,

I'm looking for a way to concatenate fields for use in a combo box.
Previously, I used a Make Table query and an Append Table query to
create a new table that placed the entries one under the other, and
then referenced it, but now I'm trying to do a combo box that combines
seven fields. Is there a way to do this without running seven
different queries?

Thanks,
Matt

 

[Brian]

You mean something like this.
SELECT DISTINCTROW tblLogger.ContractorCode, [FName] & " " & [LName] AS Name
FROM tblLogger WHERE (((tblLogger.Con)=-1)) ORDER BY
tblLogger.ContractorCode;

 

[Matt]

You mean something like this.
SELECT DISTINCTROW tblLogger.ContractorCode, [FName] & " " & [LName]
AS Name FROM tblLogger WHERE (((tblLogger.Con)=-1)) ORDER BY
tblLogger.ContractorCode;

Hi,

I'm looking for a way to concatenate fields for use in a combo box.
Previously, I used a Make Table query and an Append Table query to
create a new table that placed the entries one under the other, and
then referenced it, but now I'm trying to do a combo box that
combines seven fields. Is there a way to do this without running
seven different queries?

Thanks,
Matt

Sorry, I can see where you got that impression. Concatenate is a poor
choice of wording. I meant that I want to combine the data in several
different fields into one field, either creating one column in a new
table or allowing the query to select the data and then use it as the
source for a combo box. Instead of your example, which for me would
look like this:
QueryCell1 = [Contact1]&[Contact2]...

I want:
QueryCell1 = [Contact1]
...
QueryCell15 = [Contact2]
...

I can then reference the column QueryCell and get a list of all of the
data in all seven of my columns one after the other.

Thanks,
Matt

 

[Matt]

Hi,

I'm looking for a way to concatenate fields for use in a combo box.
Previously, I used a Make Table query and an Append Table query to
create a new table that placed the entries one under the other, and
then referenced it, but now I'm trying to do a combo box that

combines >seven fields. Is there a way to do this without running
seven >different queries?

WHOA. Two major mistaken assumptions here!

1. You're apparently assuming that you must have a Table to serve as
the source of a combo box.

2. You're also assuming that you can sort a Table.

Neither of these is true! You can - and must, in fact, in order to
sort the records - base your combo *ON A QUERY*, not on a Table.

A Combo can contain up to ten fields. Just create a Query which
returns the seven fields you want to see, and (as needed in your case)
a calculated field such as

FullName: [LastName] & ", " & [FirstName] & " " & [MiddleName]

or whatever else you want concatenated. If you make this the first
field in the Query with a nonzero width, it will be the value shown
when the combo box is not dropped down.

Thanks for your help.
In my response to Brian, I apologized for my poor use of the word
'concatenated'. In fact, I want the seven fields placed one under the
other (they all contain full names) in one column, either in a table if
necessary or, better, returned by a query without having to place the
data in a table. I want the combo box to drop down into all of the
names in the database, basically, even though there can be up to seven
for each record (Contact1 through Contact7). Previously, for combining
addresses, states, etc. I used a Make Table and Append Table query to
build a table with one field placed below the other. However, that was
for only two fields, and I think that solution would be prohibitive in
this case.
Do you have any ideas on how this can be done? Is there even a method
similar to the one above?


Thanks,
Matt

 

[John Spencer (MVP)]

Sounds as if you need a UNION query to do this. Which basically would combine
your seven queries into one.

SELECT Contact1
FROM TableName
UNION
SELECT Contact2
FROM TableName
UNION
SELECT Contact3
FROM TableName
....


OF COURSE, if your table has Contact1 through Contact7 in it, then your table
structure is wrong. You should have an additional table for Contacts that would
contain a field to hold the primary key from your main table and the Contact
information (just a name). That would make things much simpler. One query
could return all the contacts.

Hi,

I'm looking for a way to concatenate fields for use in a combo box.
Previously, I used a Make Table query and an Append Table query to
create a new table that placed the entries one under the other, and
then referenced it, but now I'm trying to do a combo box that

combines >seven fields. Is there a way to do this without running
seven >different queries?

WHOA. Two major mistaken assumptions here!

1. You're apparently assuming that you must have a Table to serve as
the source of a combo box.

2. You're also assuming that you can sort a Table.

Neither of these is true! You can - and must, in fact, in order to
sort the records - base your combo *ON A QUERY*, not on a Table.

A Combo can contain up to ten fields. Just create a Query which
returns the seven fields you want to see, and (as needed in your case)
a calculated field such as

FullName: [LastName] & ", " & [FirstName] & " " & [MiddleName]

or whatever else you want concatenated. If you make this the first
field in the Query with a nonzero width, it will be the value shown
when the combo box is not dropped down.

Thanks for your help.
In my response to Brian, I apologized for my poor use of the word
'concatenated'. In fact, I want the seven fields placed one under the
other (they all contain full names) in one column, either in a table if
necessary or, better, returned by a query without having to place the
data in a table. I want the combo box to drop down into all of the
names in the database, basically, even though there can be up to seven
for each record (Contact1 through Contact7). Previously, for combining
addresses, states, etc. I used a Make Table and Append Table query to
build a table with one field placed below the other. However, that was
for only two fields, and I think that solution would be prohibitive in
this case.
Do you have any ideas on how this can be done? Is there even a method
similar to the one above?

Thanks,
Matt

 

[Matt]

Sounds as if you need a UNION query to do this. Which basically would
combine your seven queries into one.

SELECT Contact1
FROM TableName
UNION
SELECT Contact2
FROM TableName
UNION
SELECT Contact3
FROM TableName
...


OF COURSE, if your table has Contact1 through Contact7 in it, then
your table structure is wrong. You should have an additional table
for Contacts that would contain a field to hold the primary key from
your main table and the Contact information (just a name). That
would make things much simpler. One query could return all the
contacts.

Hi,

I'm looking for a way to concatenate fields for use in a combo
box. >Previously, I used a Make Table query and an Append Table
query to >create a new table that placed the entries one under
the other, and >then referenced it, but now I'm trying to do a
combo box that combines >seven fields. Is there a way to do this
without running seven >different queries?

WHOA. Two major mistaken assumptions here!

1. You're apparently assuming that you must have a Table to serve
as the source of a combo box.

2. You're also assuming that you can sort a Table.

Neither of these is true! You can - and must, in fact, in order to
sort the records - base your combo *ON A QUERY*, not on a Table.

A Combo can contain up to ten fields. Just create a Query which
returns the seven fields you want to see, and (as needed in your
case) a calculated field such as

FullName: [LastName] & ", " & [FirstName] & " " & [MiddleName]

or whatever else you want concatenated. If you make this the first
field in the Query with a nonzero width, it will be the value
shown when the combo box is not dropped down.

Thanks for your help.
In my response to Brian, I apologized for my poor use of the word
'concatenated'. In fact, I want the seven fields placed one under
the other (they all contain full names) in one column, either in a
table if necessary or, better, returned by a query without having
to place the data in a table. I want the combo box to drop down
into all of the names in the database, basically, even though there
can be up to seven for each record (Contact1 through Contact7).
Previously, for combining addresses, states, etc. I used a Make
Table and Append Table query to build a table with one field placed
below the other. However, that was for only two fields, and I
think that solution would be prohibitive in this case.
Do you have any ideas on how this can be done? Is there even a
method similar to the one above?

Thanks,
Matt

Hmm...I didn't think of building my table that way. You're right, that
would make this much easier.
However, for now, your Union query does the job perfectly! I figured
that there had to be a way to combine fields, but I couldn't find out
how. Thanks a lot!

Regards,
Matt