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:
Code:
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
Code:
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:
Code:
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:
Code:
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:
Code:
SELECT Query1.Main_Doc, Max(Query1.REV) AS MaxOfREV, Max(Query1.Transmit) AS MaxOfTransmit
FROM Query1
GROUP BY Query1.Main_Doc;
Query3:
Code:
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:
Code:
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:
Code:
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:
Code:
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));