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
780 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
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…

828 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