[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Query designer question

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
SteveL13
Asked:
SteveL13
  • 2
1 Solution
 
MMTadminCommented:
select [fields] from table 1 where [this] = that

union

select  [fields] from table 2 where [this] = that
0
 
MMTadminCommented:
the fields must be the same for a union

research union it has some quirks
0
 
jerryb30Commented:
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
 
clarkscottCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now