Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query designer question

Posted on 2013-01-17
4
Medium Priority
?
284 Views
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.  

Example:

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:

Forms!frmCloseOrder!txtPartN

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.

Possible?

--Steve
0
Comment
Question by:SteveL13
  • 2
4 Comments
 
LVL 2

Accepted Solution

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

union

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

Expert Comment

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

research union it has some quirks
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38789777
Not for points:

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

Union
 will automatically group like values (for all fields)

Union all

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

Expert Comment

by:clarkscott
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

927 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