?
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
?
791 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 51

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 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