How to create a query from two tables that aren't related

[southhaven]

Hi. First post. I'm pretty much a novice at Access and hope you can help.

I have an event coming up and my Access file has two tables, one for the event volunteers and one for the event attendees. They contain these fields (simplified example):

Volunteers table fields
First Name, Last Name, Gender, Class, Phone, Role

Attendees table fields
Name First, Name Last, Phone Number, Address, Role

None of the volunteers exist in the Attendee table and no attendee exists in the Volunteer table. So there is no shared ID or any other field. Note also that the First Name field is named differently in the two tables as is the Last Name field.

However, I want to create a query (and then reports) that include both volunteers and attendees that will display these fields for BOTH volunteers and attendees:

First Name, Last Name, Phone, Role

So the query's list would look like:

John, Smith, 444-4444, Volunteer
Jane, Doe, 333-3434, Attendee
Sam, Jones, 234-5858, Attendee
Jen, James, 484-4848, Volunteer

How do I create a query that gives me results from BOTH tables? Any help appreciated. Thanks.

Dave

 

[isladogs]

Welcome to AWF
You create a UNION query

SELECT [Last Name], [First Name], Phone FROM Volunteers
UNION
SELECT [Name Last], [Name First], [Phone Number] FROM Attendees

The things to remember are:
1. You need the same number of fields in each part, in the same order with the same datatype
2. The query must be created in SQL view not the query designer
However you can create each part separately.
Change the second query to SQL view & copy it.
Now go to the first query change to SQL view, add UNION at the end then paste the other query

QED

 

[Beetle]

Crossposted at http://www.accessforums.net/showthread.php?t=71616

 

[Beetle]

@ Southhaven

If you're going to crosspost, please mention that you have done so, so that the volunteer responders in the forums are aware and can check if an answer has already been provided in another forum before they spend time and effort on a solution to your problem.

 

[southhaven]

Wow. I had no idea these forums were related in any way. Sorry about that.

 

[isladogs]

They aren't ... though many of us answer on more than one forum

You don't want to annoy forum members who aren't aware that an answer has been given elsewhere

 

[Pat Hartman]

Let me expand Colin's solution a little:

SELECT [Last Name], [First Name], Phone, "Volunteer" as PersonType FROM Volunteers
UNION
SELECT [Name Last], [Name First], [Phone Number], "Attendee" as PersonType FROM Attendees

 

[isladogs]

Let me expand Colin's solution a little:

SELECT [Last Name], [First Name], Phone, "Volunteer" as PersonType FROM Volunteers
UNION
SELECT [Name Last], [Name First], [Phone Number], "Attendee" as PersonType FROM Attendees

Good idea so you know which table each person comes from