Query to one Column

B

Bob Vance

If I have a table with say 10 Fields, Client1--->Client10, How can I create
a query to list all clients in to one column from all 10 fields?
 
F

fredg

If I have a table with say 10 Fields, Client1--->Client10, How can I create
a query to list all clients in to one column from all 10 fields?
Click to expand...

How about just using some DAO code.

Add a new table to the database "tblNewTable".
FieldName [ID] AutoNumber NoDuplicates PrimeKey field
FieldName [NewField] Text datatype

Paste the below code into a new module.
Change the table and field names as needed.
My sample uses 3 fields. Just add your additional ones as needed.

Public Sub AddToOneField()
Dim db As DAO.Database
Dim rsTo As DAO.Recordset
Dim rsFrom As DAO.Recordset
Set db = CurrentDb
Set rsTo = db.OpenRecordset("tblNewTable")
Set rsFrom = db.OpenRecordset("tblOriginalTable")
rsFrom.MoveFirst
Do While Not rsFrom.EOF
With rsTo
.AddNew
!Newfield = rsFrom![Client1]
.Update
.AddNew
!Newfield = rsFrom![Client2]
.Update
.AddNew
!Newfield = rsFrom![Client3]
.Update
End With
rsFrom.MoveNext
Loop

rsTo.Close
rsFrom.Close
Set rsFrom = Nothing
Set rsTo = Nothing

End Sub
 
J

John Spencer

I'm not sure I understand what you want.
Do you want 10 records in the query for each record you now have or do you
just want one record with ten values in the field?

A union query can be used to get (up to) ten records for each current record.
Union will give you all the names with no cuplicates UNION ALL will give you
all names with duplicates

Concatenating the ten fields into one can be used to get (up to) ten names
listed in one field. For four fields Client1 to Client4 the UNION ALL query
would look like the following. Change UNION ALL to UNION if you want to
remove duplicates.

SELECT Client1
FROM TheTable
WHERE Client1 is not NULL
UNION ALL
SELECT Client2
FROM TheTable
WHERE Client2 is not NULL
UNION ALL
SELECT Client3
FROM TheTable
WHERE Client3 is not NULL
UNION ALL
SELECT Client4
FROM TheTable
WHERE Client4 is not NULL

For one record with Multiple Values you might use something along the lines of
the following.
Field: (Client1 + Chr(13) + Chr(10)) & (Client2 + Chr(13) + Chr(10)) &
(Client3 + Chr(13) + Chr(10)) & (Client4 + Chr(13) + Chr(10))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
B

Bob Vance

Thanks John, I am getting an error:
Syntax error in query! Incomplete query clause

SELECT ActualOwner1
FROM tblPrintForms
WHERE ActualOwner1 is not NULL
UNION
SELECT ActualOwner2
FROM tblPrintForms
WHERE ActualOwner2 is not NULL
UNION
SELECT ActualOwner3
FROM tblPrintForms
WHERE ActualOwner3 is not NULL
UNION
SELECT ActualOwner4
FROM tblPrintForms
WHERE ActualOwner4 is not NULL
UNION
SELECT ActualOwner5
FROM tblPrintForms
WHERE ActualOwner5 is not NULL
UNION
 
B

Bob Vance

Got it Brilliant
Thanks Bob
SELECT ActualOwners1 As Owners
FROM tblPrintForms
WHERE ActualOwners1 Is Not Null

UNION

SELECT ActualOwners2 As Owners
FROM tblPrintForms
WHERE ActualOwners2 Is Not Null

UNION

SELECT ActualOwners3 As Owners
FROM tblPrintForms
WHERE ActualOwners3 Is Not Null

UNION

SELECT ActualOwners4 As Owners
FROM tblPrintForms
WHERE ActualOwners4 Is Not Null

UNION SELECT ActualOwners5 As Owners
FROM tblPrintForms
WHERE ActualOwners5 Is Not Null

UNION SELECT ActualOwners6 As Owners
FROM tblPrintForms
WHERE ActualOwners6 Is Not Null

UNION SELECT ActualOwners7 As Owners
FROM tblPrintForms
WHERE ActualOwners7 Is Not Null

UNION SELECT ActualOwners8 As Owners
FROM tblPrintForms
WHERE ActualOwners8 Is Not Null

UNION SELECT ActualOwners9 As Owners
FROM tblPrintForms
WHERE ActualOwners9 Is Not Null

UNION SELECT ActualOwners10 As Owners
FROM tblPrintForms
WHERE ActualOwners10 Is Not Null


UNION SELECT ActualOwners11 As Owners
FROM tblPrintForms
WHERE ActualOwners11 Is Not Null

UNION SELECT ActualOwners12 As Owners
FROM tblPrintForms
WHERE ActualOwners12 Is Not Null
ORDER BY Owners;
 
Top