how to solve my query

[masoud_sedighy]

I have a main table that is consists of 4 fields like below and Doc No is a primary key in the table.

Doc No
Rev
Transmit
Ct_Cs

data of my table1 is like below:

Doc No
Rev
Transmit
Ct_Cs

BB2-004-8115-1(4)
00
tt-0001
ct-cs-0001

BB2-004-8115-1(6)
01
tt-0200


BB2-004-8115-2(4)
01
tt-0002


BB2-004-8115-3(4)
01
tt-0100
ct-cs-0002

Because I cannot change design of main table and some problem about Doc No field, I have made a query that gets part of Doc No field as a Main Doc, so it consists of 5 fields

Doc No
Main Doc No
Rev
Transmit
Ct_Cs

And I have put its name, Main List.

Now I need 2 list from the main list, list 1 and list 2

List1 is a query that shows max(rev) of Main Doc No field from main list when Ct_Cs is not Null

List 2 is query that shows max (rev) of Main Doc No field from main list when Ct_Cs id Null and Main Doc No field is not in list 1

And then i wanted to adhere these 2 lists with union query as a final list

I have tried to solve my problem with several nested queries but it is very slow.

I would like to know what is optimized and best query for solving my problem.

I have used 6+1 queries for making the final list and that is very slow.


Query for creating main list is like below:

 
SELECT Table1.[Doc No], InStr([table1].[Doc No],"(")-1 AS Main_Doc_Pos, IIf([Main_Doc_Pos]>0,Mid([table1]![Doc No],1,[Main_Doc_Pos]),[table1]![Doc No]) AS Main_Doc, Table1.Rev, Table1.Transmit, Table1.Ct_Cs
FROM Table1;

For creating list1 I have used 3 queries like below:

Query1:

 
SELECT Main_list.[Doc No], Main_list.Main_Doc, Main_list.Rev, Main_list.Transmit, Main_list.Ct_Cs
FROM Main_list
WHERE (((Main_list.Ct_Cs) Is Not Null));

Query2:

 
SELECT Query1.Main_Doc, Max(Query1.REV) AS MaxOfREV, Max(Query1.Transmit) AS MaxOfTransmit
FROM Query1
GROUP BY Query1.Main_Doc;

Query3:

 
SELECT Query1.[Doc No], Query1.Main_Doc, Query1.Rev, Query1.Transmit, Query1.Ct_Cs
FROM Query1 INNER JOIN Query2 ON (Query1.Transmit = Query2.MaxOfTransmit) AND (Query1.Rev = Query2.MaxOfREV) AND (Query1.Main_Doc = Query2.Main_Doc);

For creating list2 I have used 3 queries like below:


Query4:

SELECT Main_list.Main_Doc, Max(Main_list.Rev) AS MaxOfRev, Max(Main_list.Transmit) AS MaxOfTransmit
FROM Main_list
GROUP BY Main_list.Main_Doc;

Query5:

SELECT Main_list.[Doc No], Main_list.Main_Doc, Main_list.Rev, Main_list.Transmit, Main_list.Ct_Cs
FROM Main_list INNER JOIN Query4ON (Main_list.Main_Doc = Query4.Main_Doc) AND (Main_list.Transmit = Query4.MaxOfTransmit) AND (Main_list.Rev = Query4.MaxOfRev);

Query6:

SELECT Query5.[Doc No], Query5.Main_Doc, Query5.Rev, Query5.Transmit, Query5.Ct_Cs
FROM Query5 LEFT JOIN list1 ON Query5.Main_Doc = list1.Main_Doc
WHERE (((list1.Main_Doc) Is Null));

[Bob Fitz]

Cross-posted: https://access-programmers.co.uk/for...d.php?t=296270

Please read: https://www.excelguru.ca/content.php?184