473,847 Members | 2,427 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Combining two text field into a long list

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
4 3511
"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
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******** *********@newsr ead2.news.pas.e arthlink.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
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
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******** *********@newsr ead2.news.pas.e arthlink.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7356
by: Kenneth Fosse | last post by:
Hi. I'm currently working on a project which involves the creation of a web page which reports selected data to customers from two back-end systems. I use ASP script language on IIS, the server is MS SQL 2000. Now I'm struggling with combining two tables from the different databases. I'm sure it's simple enough, but I'm a little short on the SQL expertise. I've got two databases, db1 and db2, and then two tables, db1.t1 and
2
4720
by: Kristian | last post by:
Hey, I have some problems combining 2 fields in a query. For example: the first field is "1050-04" and the second field is "02". However, when i try to combine these two the zero in the second field disappears. So it reads "1050-042" but i want it to be "1050-0402". The number 02 increases, so when it reaches 10+ it should be like "1050-0410".
2
1567
by: Max | last post by:
Hello everyone, I have a task of combining two tables: tblPatient and tblSolution, related by a KEY field in a one-to-many relationship from tbl Patient to tblSolution, respectively. So, I have the following table layout: tblPatient ---------- REC: Autonum (Primary Key) KEY: Number (NOT UNIQUE)
4
3137
by: Omey Samaroo | last post by:
Dear Access Gurus, Can anyone provide me with some much needed assistance. I would like to combine the contents of 3 text fields into one field. Can someone provide some code or a method to do so. Please and Thanks Omey
1
6597
by: John Smith | last post by:
I am trying to combine two lookup fields into one field for display on a form and a report. I understand the basics behind how to do this: I set the control source for a new text box control to = & The problem is, this gets me a number. You see, HabitID and ClassID are both lookup fields on the Control Source table to the form. So, if I have a field on the form named HabitID, it would be a combobox that looks up values from tblHabit....
6
5662
by: Aussie Rules | last post by:
Hi, I have a datepicker that show a calender. The user picks a date and the time component is always 00:00. I then have a drop down that provides a list of times, (10:00, 11:00 etc), and I want to combine this with the date value, so that I can store it in a single field in the database. How can I combine these two values into one ?
1
1520
by: datasec23 | last post by:
Hi... I really appreciate your help with my question, I am pulling my hair out on this. I have not been able to find an answer on the other posts. I am using Access 2000. I have a form that has a list box that pulls a full list of medicines from a table. In the table I have two columns Medicine Name and Show When. I only show Medicine Name in the list box on the form. On the form I also have a text box (named Medicine), that is populated...
5
1961
by: MLH | last post by:
I'm using A97 import data wizard to import text file N2 a table. The text file is a DIR listing produced by running dir jdc*.* /s c:\JDCs.txt The wizard is chopping the lines off at the leftmost 80 chars. Dunno why - I don't know how to configure it to grab longer lines than that. I made the TARGET table field a memo field. That didn't help. Ideas anyone?
15
17402
by: pakerly | last post by:
How would i do this, convert a test file to excel? Lets say my text file has fields like this: NUMBER NAME ADDRESS PHONE 11002 Test1 TestAddress1 111-2222 lets say the number field is 10 characters long, there is a space and Name field is 15 characters long, there is a space, address can be 25 characters long, there is a space and phone is 10 characters long I want to...
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7879
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5907
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.