473,699 Members | 2,141 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Union Query Question

From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.
Nov 12 '05 #1
3 10543
Each query in the union must return the same number and order of fields, the
tables do not have to match. You can use a calculated field to fill-in data
for fields the underling table is missing. Also look-up the difference
between UNION and UNION ALL to ensure you get all the records you need. The
easiest way to build the UNION is to create each query separately then cut
and past between the SQL windows.

"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...
From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.

Nov 12 '05 #2
you already have the tables (so you don't need new tables)
you can use two queries but you don't have to.
the fieldnames have to be the same.
look at the examples in the help (they use the AS keyword)
also consider the ALL keyword (UNION ALL SELECT)
(or you may get less records than you expect)
"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...
From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.

Nov 12 '05 #3
You need to make sure you alias the field names so the aliases/names
match... Say you have two tables that are union-compatible (field
types are the same).

TableA(Field1, Field2, Field3)

TableB(FieldA, FieldB, FieldC)

SELECT TableA.Field1 As FieldA, TableA.Field2 As FieldB, TableA.Field3
As FieldC
FROM TableA
UNION ALL
SELECT FieldA, FieldB, FieldC
FROM TableB...

then you can query that result... but NOTE one thing... You CANNOT
update it. (because there's no way to find the original record's table
from a union query).
Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2024
by: dmb000006 | last post by:
My question is about a UNION query to deal with an (annoying) JOIN over two tables. I am joining over a double column primary key (where the order of the columns can be changed). This is sooo slooow using a join, but very fast using a union. How come this is? Here is more detail... I have two tables of 'associations' between pairs of objects.
3
5372
by: Edward | last post by:
I am having conceptual trouble with the following query: select r.ServiceID,r.ContractID,sum(Total) from ( select csc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Total from iwms_tbl_CustomerSiteContainers csc, iwms_tbl_ContractLines cl, iwms_tbl_Contracts c,
5
1699
by: Arvin Portlock | last post by:
I can't come up with a query that works. Can anyone help? Conceptually the relationships are easy to describe. I have a table for books (Entries), a table for authors (Authors), and a linking table between the two because books often have more than one author (OA_Link). This situation is simple and common. A query to list each title and all the authors associated with that title looks like this: SELECT Entries.TitleStatement,...
3
3421
by: Chip R. | last post by:
Hi, This isn't really an access question, but as I'm really new to SQL I don't know where I would post this. Any suggestions on that would also be helpful. Now on to the question... I am trying to extract data from a Timberline database through an OBDC driver to MS Excel. I'm using Microsoft Query and a crude knowledge of SQL to build my query.
2
9765
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified) problem can avoid these technicalities: the original table has columns A1, A2, B1, B2, C1, C2.
2
805
by: Thomas | last post by:
Hi All. I think that if I explain the database layout first it may be easier to ask my question. Table Name Field Name Test TestCode Description SpecimenRequirements FeeSchedule TestCode CPTCode
5
3843
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big table, we try separate this big table into twelve tables and create a view
27
13787
by: MLH | last post by:
How can I turn the following into a make-table query? SELECT & " " & AS Recipient FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID(); UNION SELECT AS Recipient FROM tblVehicleJobs INNER JOIN tblLienHolders ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID WHERE
2
4944
kcdoell
by: kcdoell | last post by:
Hello: I am trying to create a union query but do not have a lot of experience. Basically I have the below tables: The Tables: Table Name = tblPrior CreditRegIDFK; Number; Foreign Key for the Credit Region ID
0
8705
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9199
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9054
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8943
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7785
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5884
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4391
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2362
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.