Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I want to make a Union Query in Access 2010, but not use all the Fields in both Tables

Posted on 2013-06-24
5
Medium Priority
?
795 Views
Last Modified: 2013-06-27
I have always used the structure that combines two tables in their entirety, but now I need to union parts of two tables that are not alike, but do contain some of the same fields.

Can someone please give me the syntax for doing that? A Union of two tables with some fields, not all?

Thanks

Rex
0
Comment
Question by:Rex85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39271453
Generally you would do something like:

SELECT A, B, C, NULL as D, NULL as E, NULL as F
FROM Table1
UNION ALL
SELECT A, NULL, NULL, D, E, F
FROM Table2

Sometimes, I'll even start out with a row that has madeup data of the correct data types to ensure that the datatypes are correct:

SELECT intNumber as A, "B" as B, #12/31/1899# as C, cdbl(1.0) as D, clng(1) as E, "F" as F
FROM tblNumbers WHERE intNumber = 0
UNION ALL
SELECT A, B, C, NULL as D, NULL as E, NULL as F
FROM Table1
UNION ALL
SELECT A, NULL, NULL, D, E, F
FROM Table2

If I do this, then I will wrap that up with a WHERE clause that excludes the first row

WHERE [A] <> 0
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39271454
Just add expressions for the missing fields:

Select Field1, Field2, 0 As Field3, "N/A" As Field4, Null As Field5 From tblWithMissingFields

/gustav
0
 
LVL 32

Expert Comment

by:awking00
ID: 39271644
Union requires that the number of columns and their datatypes be the same in both select statements so, if a given column does not exist in one of the selects, you must substitute a literal value or null as has been indicated. In that instance the use of union all will perform better since no duplicates can exist. If there are no missing columns, but you only wish to select certain ones, just list the column names that meet the criteria for the number of columns and datatypes.
0
 

Author Closing Comment

by:Rex85
ID: 39282233
Sorry! Forgot to close it.

thanks

Rex
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39283306
glad to help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question