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
