Floated between queries with slow speed

[masoud_sedighy]

Hello



I like to make a query for extracting latest (max) rev of one list like below. But for catching this result I have made 3 queries

that are related
(one by one). So I have floated between queries with slow speed.



LIST:

DocumentNo    title    REV    DATE        TRANS
DW-1013        test    0    12-Dec-09    TT-3799
DW-1013        test    1    13-May-10    TT-5923
DW-1013        test    1    15-Apr-11    TT-5943
DW-1013        test    2    1-Jan-12    TT-6031

Result:

DocumentNo    TITLE    REV    DATE        TRANS
DW-1013        TEST    2    1-Jan-12        TT-6031

For this result if have made 3 queries that are related.

1- First query for finding max rev of the list.(group and aggregate function)

2- Second query for finding max date that has been made with relating first query and list with joining (doc no, rev)

3- Third query for finding max transmittal , that has been made with relating second query and list .

So slow of third query that has my result is slow (some times around 17 second) , please help how I can make better query.

Best regards.

 

[MSAccessRookie]

I can see why the Query would take so long, since you must repeat the process of Grouping at each level, but I am not sure why you need Three Queries. Would the following single Query serve your purposes?

SELECT Table1.DocumetNo, Table1.Title, Max(Table1.REV) AS MaxOfREV, Max(Table1.DocDate) AS MaxOfDocDate, Max(Table1.Trans) AS MaxOfTrans
FROM Table1
GROUP BY Table1.DocumetNo, Table1.Title;

 

[masoud_sedighy]

Excuse me I forget another column like below in my list that can not be grouped like doc,title and in each row has different value,
I need it’s result but when I use max function for this field result is not ok and it returns value of another row in result list.
Actually because of that I made more queries.

LIST:

DocumentNo    title    REV    DATE        TRANSMITTAL    purpose
DW-1013        test    0    12-Dec-09    TT-3799        ifi
DW-1013        test    1    13-May-10    TT-5923        ifr
DW-1013        test    1    15-Apr-11    TT-5943        ifr
DW-1013        test    2    1-Jan-12    TT-6031        afc

result:

DocumentNo    TITLE    REV    DATE        TRANSMITTAL    purpose
DW-1013        TEST    2    1-Jan-12    TT-6031        ifr

 

[MSAccessRookie]

I am not sure I can reproduce your problem. I added the new Field to the Query, and my results matched what you had indicated that you wanted them to match.

SELECT Table1.DocumetNo, Table1.Title, Max(Table1.REV) AS MaxOfREV, Max(Table1.DocDate) AS MaxOfDocDate, Max(Table1.Trans) AS MaxOfTrans, Max(Table1.Purpose) AS MaxOfPurpose
FROM Table1
GROUP BY Table1.DocumetNo, Table1.Title;

 

[masoud_sedighy]

thanks
it does not return what i need, i like to have result like below.

DocumentNo    TITLE    REV    DATE        TRANSMITTAL    purpose
DW-1013        TEST    2    1-Jan-12    TT-6031        afc

it returns max value in purpose"ifr', but i need "afc" value in my row.
best regards.

 

[MSAccessRookie]

thanks
it does not return what i need, i like to have result like below.

DocumentNo    TITLE    REV    DATE        TRANSMITTAL    purpose
DW-1013        TEST    2    1-Jan-12    TT-6031        afc

it returns max value in purpose"ifr', but i need "afc" value in my row.
best regards.

I am a little bit confused by a perceived change in the rules. Post #3 clearly shows ifr as the requested value for the desired result, and I provided a code change that provides that. Now there is a new set of rules that required a different value. Try it with thw following two Queries (Of course, you would need to rename anyything as your needs require).

Query1:
 
SELECT Table1.DocumetNo, Table1.Title, Max(Table1.REV) AS RevNum, Max(Table1.DocDate) AS DocDate, Max(Table1.Trans&"\"&Table1.Purpose) AS TransPurpose
FROM Table1
GROUP BY Table1.DocumetNo, Table1.Title;
 
Query2: (Calls Query1)
 
SELECT Query1.DocumetNo, Query1.Title, Query1.RevNum, Query1.DocDate, Left([TransPurpose],InStr(1,[TransPurpose],"\")-1) AS Trans, Right([TransPurpose],Len([TransPurpose])-InStr(1,[TransPurpose],"\")) AS Purpose
FROM Query1;

It is often difficult to resolve problems while the rules are still evolving. If this is not yet correct, then is there anything else that we need to know?