Access query - multiple tables but same column

[MoCA111]

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

 

[xenou]

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.

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:

select [FirstName], [LastName], [UserID] from [Table A]
[B]union[/B]
select [FirstName], [LastName], [UserID] from [Table B]

 

[MoCA111]

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.

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:

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!

 

[MoCA111]

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?

Aaron	Fake	4844
Adam	Name	4405
Adam	Is	ER09
Adam	Here	1328

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

</thead><tbody>
</tbody><tfoot></tfoot>

 

[xenou]

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).

 

[Joe4]

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.