How to create a query from two tables that aren't related (1 Viewer)

southhaven

New member
Local time
Today, 07:36
Joined
Apr 13, 2018
Messages
2
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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF
You create a UNION query

Code:
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

Duly Registered Boozer
Local time
Today, 06:36
Joined
Apr 30, 2011
Messages
1,808
@ 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

New member
Local time
Today, 07:36
Joined
Apr 13, 2018
Messages
2
Wow. I had no idea these forums were related in any way. Sorry about that.
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 19, 2002
Messages
42,970
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

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom