Combining Two Expressions into the Same Field

[Luke]

My boss owns about 100 companies. Some of these companies have people to Contact while others don't. I have a table with the following fields:

EntityName
Contact1First
Contact1Last
...Contact1Phone
...Contact1Cell
...Contact1Email
Contact2First
Contact2Last
...Contact2Phone
...Contact2Cell
...Contact2Email

Some companies don't have any contacts or info in these fields. I have created a query with expressions to combine the first and last names of each Contact:

Contact1: ([Contact1Last] & (", "+[Contact1First]))
Contact2: ([Contact2Last] & (", "+[Contact2First]))



Here's my problem: I want to be able to combine both of these expressions into ONE field or expression so that I can make a Report that sorts these names alphabetically. (Right now I can only sort "Contact1" alphabetically). I have tried using a Union Query that looks like this:

SELECT [Contact1] & [Contact2] FROM qryContacts;

The problem with this Union query is that it combines both Contact1 and Contact2 into a new expression within the same cell. Let's say for Company A Contact1 is "Smith, John" and Contact2 is "Williams, Ryan". The expression returns Smith, John Williams, Ryan in the same cell.

I want to basically combine the Contact 1 and Contact 2 Expressions so that I can make a report that looks like this:

Entity Name.......Contact........Phone........Cell....... ..Email

Right Now it looks like this:

Entity Name.......Contact1........C1Phone...........C1Cel l................C1Email
.......................Contact2........C2Phone.... .......C2Cell................C2Email

If anyone knows how to do this please let me know. Thanks a lot.

-Luke

[June7]

Maybe this is what you want:

SELECT [Contact1Last] & ", " & [Contact1First] As Contact, Contact1Phone As Phone, Contact1Cell As Cell, Contact1Email As Email FROM Contacts
UNION SELECT [Contact2Last] & ", " & [Contact2First], Contact2Phone, Contact2Cell, Contact2Email FROM Contacts;

There is no designer or wizard for UNION query. Must type in the SQL View window.

Now use this query as report RecordSource. Use report Grouping & Sorting features.