Ms Access - SQL Queries with Subqueries (all in one query)

[Patekos]

I have 4 Queries (Query1; Query2; Query3; Query4), I want to join the 4 queries into one query that would do what the 4 do, but I don't know how to do it. Can anyone help?

The SQL code for each query 1 is as follows:

Query1 - "SELECT DISTINCT ProductsACTIVE.PZN_Code, ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1, ProductsACTIVE.tbl_HMR_Market_Segment_3, ProductsACTIVE.tbl_HMR_Market_Segment_6, ProductsACTIVE.tbl_HMR_Class_Code_5, ProductsACTIVE.Product_Type FROM ProductsACTIVE WHERE (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Market_Segment_6)=1) AND ((ProductsACTIVE.Product_Type)="Health Product")) OR (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Class_Code_5) Like "H26.4.*") AND ((ProductsACTIVE.Product_Type)="Health Product")) ORDER BY ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1; "

Query2 - "SELECT Query1.tbl_HMR_Product_Name, Query1.tbl_HMR_Pack_Form, Query1.tbl_HMR_Pack_Strength, Query1.tbl_HMR_Class_Code_1 FROM Query1 GROUP BY Query1.tbl_HMR_Product_Name, Query1.tbl_HMR_Pack_Form, Query1.tbl_HMR_Pack_Strength, Query1.tbl_HMR_Class_Code_1; "

Query3 - "SELECT Query2.tbl_HMR_Product_Name, Query2.tbl_HMR_Pack_Form, Query2.tbl_HMR_Pack_Strength, Count(Query2.tbl_HMR_Class_Code_1) AS CountOftbl_HMR_Class_Code_1 FROM Query2 GROUP BY Query2.tbl_HMR_Product_Name, Query2.tbl_HMR_Pack_Form, Query2.tbl_HMR_Pack_Strength HAVING (((Count(Query2.tbl_HMR_Class_Code_1))>1)); "

Query4 - "SELECT ProductsACTIVE.PZN_Code, ProductsACTIVE.tbl_HMR_Prod_Pack_Code, ProductsACTIVE.tbl_HMR_Pack_Desc, ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1 FROM ProductsACTIVE INNER JOIN Query3 ON (ProductsACTIVE.tbl_HMR_Pack_Strength = Query3.tbl_HMR_Pack_Strength) AND (ProductsACTIVE.tbl_HMR_Pack_Form = Query3.tbl_HMR_Pack_Form) AND (ProductsACTIVE.tbl_HMR_Product_Name = Query3.tbl_HMR_Product_Name);"




Thank you,
Patekos

[ranman256]

Union query.
select * from query1
union
select * from query2
union
select * from query3


All queries must have the same # columns & types.

[Patekos]

Hi ranman256,
maybe I didn't explain my problem well.
I don't want to Join the resuls of the queries.
I want to join the queries in one. They work together, Query 4 works with Query 3 , Query 3 works with Query 2 and query 2 works with query 1.
Thnak you

[CJ_London]

in query 2 change

.....FROM Query1.....

to

.....FROM (SELECT DISTINCT ProductsACTIVE.PZN_Code, ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1, ProductsACTIVE.tbl_HMR_Market_Segment_3, ProductsACTIVE.tbl_HMR_Market_Segment_6, ProductsACTIVE.tbl_HMR_Class_Code_5, ProductsACTIVE.Product_Type FROM ProductsACTIVE WHERE (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Market_Segment_6)=1) AND ((ProductsACTIVE.Product_Type)="Health Product")) OR (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Class_Code_5) Like "H26.4.*") AND ((ProductsACTIVE.Product_Type)="Health Product")) ORDER BY ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1) AS Query1.....

Basically put the query1 sql into brackets between FROM and the query name

Then you can copy all that into brackets for query 3, etc

[JoeM]

I don't want to Join the resuls of the queries.
I want to join the queries in one. They work together, Query 4 works with Query 3 , Query 3 works with Query 2 and query 2 works with query 1.

[JoeM]

I don't want to Join the resuls of the queries.
I want to join the queries in one. They work together, Query 4 works with Query 3 , Query 3 works with Query 2 and query 2 works with query 1.

Note that with Dependent Select Queries like you have, there is no problem with having 4, like you have.
You do NOT need to open them all up each time. If you ultimately just want to see the results from Query4, then just open up Query4, and it will automatically run all the underlying queries at run-time.
So once all the queries have been built, there will probably seldom be a reason to open Queries 1-3 directly.

[Patekos]

Solved.
Thank You Ajax.

But in the last one "query4" I had to put it between INNER JOIN and the query name.

in query 2 change

.....FROM Query1.....

to

.....FROM (SELECT DISTINCT ProductsACTIVE.PZN_Code, ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1, ProductsACTIVE.tbl_HMR_Market_Segment_3, ProductsACTIVE.tbl_HMR_Market_Segment_6, ProductsACTIVE.tbl_HMR_Class_Code_5, ProductsACTIVE.Product_Type FROM ProductsACTIVE WHERE (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Market_Segment_6)=1) AND ((ProductsACTIVE.Product_Type)="Health Product")) OR (((ProductsACTIVE.tbl_HMR_Market_Segment_3)=1) AND ((ProductsACTIVE.tbl_HMR_Class_Code_5) Like "H26.4.*") AND ((ProductsACTIVE.Product_Type)="Health Product")) ORDER BY ProductsACTIVE.tbl_HMR_Product_Name, ProductsACTIVE.tbl_HMR_Pack_Form, ProductsACTIVE.tbl_HMR_Pack_Strength, ProductsACTIVE.tbl_HMR_Class_Code_1) AS Query1.....

Basically put the query1 sql into brackets between FROM and the query name

Then you can copy all that into brackets for query 3, etc