Optimized 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.

<code>

Doc No
Rev
Transmit
Ct_Cs

</code>

data of my table1 is like below:

<code>

Doc No Rev Transmi 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

</code>

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

</code>

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;


</code>

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));


</code>


Query2:

<code>

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


</code>

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);


</code>


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;


</code>


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);


</code>

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));


</code>

 

[Ranman256]

its not
<code>
</code>

use brackets:
[
[/

 

[bob fitz]

Cross-posted: http://www.accessforums.net/showthread.php?t=68480

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

 

[plog]

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


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));

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

Those 2 queries do not provide what the statement says. This could easily be done in one query. First though, explain why you have all those additional fields that your statement doesn't reference. [Doc No], Ct_Cs and Transmit aren't mentioned in your statement. Why are they in the query?