By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,029 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Combining two text field into a long list

P: n/a
Hello Experts!

I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query. It appears I can not
use the OR operator since these are two separate fields in the original
table.

I hope this is clear. Thanks.

Justin
Jan 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"justin tyme" <ns************@yahoo.com> wrote
I would like to combine (which may not be the
correct technical term) two text fields from the
same table in a query. Specifically, text field A and
text field B are both lists of names.


What, exactly, is the format of the text field that contains a "list of
names"? You mean two text fields in the very same record contain many names?
Are they on separate lines, delimited by commas, or ??? And how do you want
the resulting list to appear?

Larry Linson
Microsoft Access MVP
Jan 17 '06 #2

P: n/a
Justin, I'm not really clear here, but I think you have person in field A,
and another person in field B, and you want to make a label for every person
in both fields.

If that's the idea, you can create a UNION query that combines the people
from both fields into one long list. Access cannot show you a UNION query
graphically, but try this:
1. Create a query that gets the name from field A, and all the other fields
you want for your labels.

2. In the Field row in front of field A, enter an alias and a colon, such
as:
ThePerson: [A]
In the Criteria row under this field, enter:
Is Not Null

3. Create a second query that gets the same fields in the same order, but
uses field B instead of A.

4. Use the same alias:
ThePerson: [b]
and the same Criteria:
Is Not Null

5. Switch both queries to SQL View (View menu.)

6. In the first query, replace the trailing semicolon with:
UNION ALL
and paste in all the text of the 2nd query statement.

You will end up with something like this:

SELECT [A] AS ThePerson, Address, City, Zip
FROM [Table1]
WHERE A is Not Null
UNION ALL
SELECT [b] AS ThePerson, Address, City, Zip
FROM [Table1]
WHERE [b] Is Not Null;
Optional Extensions
===============
a) If you want the query to deduplicate the names form fields A and B,
change:
UNION ALL
to just:
UNION

b) If you want to know which field a name came from, add a calculated field,
e.g.:
SELECT [A] AS ThePerson, "FieldA" AS TheSourceField, City
FROM [Table1]
WHERE A is Not Null
UNION ALL
SELECT [b] AS ThePerson, "FieldB" AS TheSourceField, City
FROM [Table1]
WHERE [b] Is Not Null;

c) If you want to sort the query, add an ORDER BY clause to end of the query
statement. (Generally this doesn't matter, as you use the Sorting And
Grouping box in your report (View menu in Report Design view) to handle the
sorting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"justin tyme" <ns************@yahoo.com> wrote in message
news:vX*****************@newsread2.news.pas.earthl ink.net...
Hello Experts!

I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query. It appears I can
not use the OR operator since these are two separate fields in the
original table.

I hope this is clear. Thanks.

Justin

Jan 17 '06 #3

P: n/a
On Tue, 17 Jan 2006 01:41:47 GMT, "justin tyme" <ns************@yahoo.com> wrote:
Hello Experts!

I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query. It appears I can not
use the OR operator since these are two separate fields in the original
table.

I hope this is clear. Thanks.

Justin


You would need to use a union query.

SELECT FieldA As ListName FROM tblMyTable
UNION SELECT FieldB FROM tblMyTable;
Wayne Gillespie
Gosford NSW Australia
Jan 17 '06 #4

P: n/a
Thanks to all. I had never heard of the UNION function. In fact, I went
back to my 423 page instructional manual, and it was not even listed!

justin

"justin tyme" <ns************@yahoo.com> wrote in message
news:vX*****************@newsread2.news.pas.earthl ink.net...
Hello Experts!

I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query. It appears I can
not use the OR operator since these are two separate fields in the
original table.

I hope this is clear. Thanks.

Justin

Jan 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.