Access query - multiple tables but same column

MoCA111

New Member
Joined
Feb 6, 2018
Messages
8
Hey everyone,

I am a complete access beginner so I apologize if this is a no-brainer / a dumb question.

I have two tables. One called MasterRepList and MastetTechList.

In both of these tables, I have a FirstName, LastName and UserID field. (The MasterTechList calls this field TechID)

--

What I am trying to do is create a query that shows me all FirstName's, LastName's and UserID's from both tables.

Example
Table A - franke
Table A - harrry
Table A - Bob
Table B - Sam
Table B - Matt
table B - you get my idea

Is there an easy way to accomplish this? The query designer seems to only allow one table field per column.

Thanks in advance!
Matt
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I'm not sure if the query designer can handle union queries but you can switch into SQL view and edit the sql by hand.

Code:
select [FirstName], [LastName], [UserID] from [Table A]
[B]union all[/B]
select [FirstName], [LastName], [UserID] from [Table B]
To eliminate duplicate use Union instead of Union All:
Code:
select [FirstName], [LastName], [UserID] from [Table A]
[B]union[/B]
select [FirstName], [LastName], [UserID] from [Table B]
 
Upvote 0
I'm not sure if the query designer can handle union queries but you can switch into SQL view and edit the sql by hand.

Code:
select [FirstName], [LastName], [UserID] from [Table A]
[B]union all[/B]
select [FirstName], [LastName], [UserID] from [Table B]
To eliminate duplicate use Union instead of Union All:
Code:
select [FirstName], [LastName], [UserID] from [Table A]
[B]union[/B]
select [FirstName], [LastName], [UserID] from [Table B]


Thanks Xenou, when I was searching the googlebox, it kept referencing union, but I wasn't sure if that was going down the wrong rabbit hole.

I'll give this a shot!
 
Upvote 0
Thanks Xenou, when I was searching the googlebox, it kept referencing union, but I wasn't sure if that was going down the wrong rabbit hole.

I'll give this a shot!

Perfect! Thanks again Xenou,

When I run the Union Query, I get a blank row at the top, would that be normal?




AaronFake4844
AdamName4405
AdamIsER09
AdamHere1328

<caption> UnionTest </caption><thead>
[TH="bgcolor: #c0c0c0"] FirstName [/TH]
[TH="bgcolor: #c0c0c0"] LastName [/TH]
[TH="bgcolor: #c0c0c0"] Sample [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>
 
Upvote 0
I wouldn't say that is normal, unless you actually have a blank row in one of the tables, in which case it would be a part of the results (I shouldn't say a blank row technically - just a row with blank firstname, lastname, and userid).
 
Last edited:
Upvote 0
When I run the Union Query, I get a blank row at the top, would that be normal?
As xenou said, that probably means that you have a blank record (or at least a record with those fields blank) somewhere in your data.

You could simply add criteria to each of the subqueries making up your Union query to filter out all records where some field is null (i.e. UserId).
That should resolve the issue of blank rows.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top