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
760 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
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 49

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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39283306
glad to help
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now