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
747 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
Sorry! Forgot to close it.

thanks

Rex
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
glad to help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

20 Experts available now in Live!

Get 1:1 Help Now