Query designer question

Posted on 2013-01-17
Last Modified: 2013-01-19
I'm trying to figure out how to create a select query that will pull records from two different tables if they have a common field name.  


tblOpenOrders and tblFinGoods

both have a field named "PartN" and I want the query to return all the records from tblOpenOrders AND ALSO all the records from tblFinGoods using form criteria like:


So if tblOpenOrders has 5 records equal to "Forms!frmCloseOrder!txtPartN" and tblFinGoods has 10 records equal to "Forms!frmCloseOrder!txtPartN" I want 15 records to display when I run the query.


Question by:SteveL13
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
  • 2

Accepted Solution

MMTadmin earned 500 total points
ID: 38789756
select [fields] from table 1 where [this] = that


select  [fields] from table 2 where [this] = that

Expert Comment

ID: 38789757
the fields must be the same for a union

research union it has some quirks
LVL 26

Expert Comment

ID: 38789777
Not for points:

select * from table1
where partN = Forms!frmCloseOrder!txtPartN
union all
select * from table2
where partN = Forms!frmCloseOrder!txtPartN

 will automatically group like values (for all fields)

Union all

allows like values (for all selected fields), although the source table would be lost.
LVL 20

Expert Comment

ID: 38792055
If you tables are different, create a query for each table and name the columns the same for both queries.  Use these 2 new queries in your union query.

Scott C

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

634 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