Union Query with Union Criteria

[ilikebirds]

Is it possible to create a union query that contains criteria from another union query?

I would like the query to only display the order numbers from TeamUnions that are either contained in FromMortASA and FromMortO.

Is that possible?

---
SELECT [order_num], [employee_id], [from_order_step _num], [to_order_step_n um], [move_time], [from_step_durat ion_sec]
FROM [dbo_FromMortO]
WHERE [order_num]=[TeamUnions].[order_number]
UNION
SELECT [order_num],[employee_id], [from_order_step _num], [to_order_step_n um], [move_time], [from_step_durat ion_sec]
FROM [dbo_FromMortAsA]
WHERE [order_num]=[TeamUnions].[order_number];
---
?? I'm sure that is wrong.

[FishVal]

Hi, there.

Did you try this or read Access help topic "UNION operation"

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned.

[ilikebirds]

Hi, there.

Did you try this or read Access help topic "UNION operation"

Yes I have done both. What i'm trying to accomplish is to only Union the data from 2 tables that match the Order numbers from another Union Query.

[FishVal]

Hi, there.

Make joins and union them. Like this.

[code=sql]
SELECT [dbo_FromMortO].[order_num], [dbo_FromMortO].[employee_id], [dbo_FromMortO].[from_order_step _num], [dbo_FromMortO].[to_order_step_n um], [dbo_FromMortO].[move_time], [dbo_FromMortO].[from_step_durat ion_sec]
FROM [dbo_FromMortO] INNER JOIN [TeamUnions] ON
WHERE [dbo_FromMortO].[order_num]=[TeamUnions].[order_number]
UNION
SELECT [dbo_FromMortAsA].[order_num], [dbo_FromMortAsA].[employee_id], [dbo_FromMortAsA].[from_order_step _num], [dbo_FromMortAsA].[to_order_step_n um], [dbo_FromMortAsA].[move_time], [dbo_FromMortAsA].[from_step_durat ion_sec]
FROM [dbo_FromMortAsA] INNER JOIN [TeamUnions] ON
WHERE [dbo_FromMortAsA].[order_num]=[TeamUnions].[order_number];
[/code]

[ilikebirds]

Hi, there.

Make joins and union them. Like this.

Thanksf or the tip. I ended up getting it to work:

SELECT dbo_FromMortO.o rder_num, dbo_FromMortO.e mployee_id, dbo_FromMortO.f rom_order_step_ num, dbo_FromMortO.t o_order_step_nu m, dbo_FromMortO.m ove_time, dbo_FromMortO.f rom_step_durati on_sec
FROM dbo_FromMortO INNER JOIN TeamUnions ON dbo_FromMortO.o rder_num=TeamUn ions.order_numb er
WHERE dbo_FromMortO.o rder_num=TeamUn ions.order_numb er;
UNION SELECT dbo_FromMortAsA .order_num, dbo_FromMortAsa .employee_id, dbo_FromMortAsa .from_order_ste p_num, dbo_FromMortAsa .to_order_step_ num, dbo_FromMortAsA .move_time, dbo_FromMortAsA .from_step_dura tion_sec
FROM dbo_FromMortAsA INNER JOIN TeamUnions ON dbo_FromMortAsA .order_num=Team Unions.order_nu mber
WHERE dbo_FromMortAsA .order_num=Team Unions.order_nu mber;

[FishVal]

You are welcome.

Best regards,
Fish