Max and Last

[Len Boorman]

I have a query that does not do what I expect and I do not understand why.

The sql below seems straightforward to me

SELECT Max(qry_Dist_List_Select_1.Rev_Date) AS MaxOfRev_Date
FROM qry_Dist_List_Select_1;


Okay so it is querying a query but that is not unusual.

qry_Dist_List_Select_1 queries two tables that have a one to many link with referential entegrity enforced. Basically the first table is a list of documents and the second table a list of revisions and dates.

So what I want is to select the max rev date for a document so that I get the Max revision. Before you say use the actual revision number I should point out that in this case I need the latest active revision. An active revision has a date. It is possible in this database to assign a revision to a document and not assign a date. This is then a pending revision and being worked on.

So back to question.

I have the following date against a specific document

Rev Date
0 27/11/03
1 27/11/03
2 05/01/04

So why does query select max date as 27/11/03 ?

Now when I use

SELECT Last(qry_Dist_List_Select_1.Rev_Date) AS LastOfRev_Date
FROM qry_Dist_List_Select_1;

I get the correct selection regardless of the sequencing of the first query output.

All comments appreciated.

Len B

 

[jfgambit]

From your code it looks like you are missing the revision number. Add the revision number and leave it as a GROUP BY.

 

[Len Boorman]

Thanks for the reply.

Actually just written long explanation of problem and whey I need to do it the way I have when it struck me

Are we certain that the data is being stored correctly i.e. Sate Format

Well its as formatted text. No doubt this is where problem lies

Thanks anyway

dooh


len B

 

[Len Boorman]

No that's not the probem cos it was set as text because of the inherited data. I was forced to accept existing data which ranged from N/K to --/--98 to full date with everything in between.

Anyway that explains the Max value cos it was selecting pased upon text characters,

The Last select statement still intrigues me however.

Is there some for of automatic date and time stamping that makes it work correctly or is this a quirk of circumstances

Len B

 

[Brianwarnock]

As I understand the way Last works , and as happened to me when I tried to use it to select the last date before I read about it , it returns the data in the field for the last record in the record set that satisfies any other criteria, if its working for you, as it initially did for me, then it is likely to be purely coincidence, and maybe it will always be so, but it dangerous practice to work that way.

Can you edit the field to enable you to use Max?
If you cannot convert to a date field then reversing the order to yy/mm/dd should work, but I haven't got the code to do that.

I would be interested in your solution, merely out of curiosity at the moment.
Best of Luck

Brian

 

[Len Boorman]

Hi Brian
Thanks for the info.

Overnight I considered the situation and decided that using "Last" was not a reliable solution. I tried sorting the recordset that was being queried in different ways to try to figure out exactly what was happening but it seemed to make no difference. So decided that using something that I did not understand how it worked was not the way to go.

I have solved the situation however. Nothing elegant really just use standard queries to initially select all non pending revisions for given document (rev date not null) then simply select Max Rev No from this subset.

Sounds obvious I know (and it is really)Max date was just that bit more direct and it was working fine (apparently). Looking at thinks deeper however it was down to coincidence.
This application is one that started with a simple requirement and then grew to the point where instead of being purely for reference it now manages distribution, sales, revision service, and a whole host of inter-related data.
Just goes to show that it is very difficult to "hold in the brain" all the nuances that are in an application. I have all of these documented but when you look at a recordset it is so easy to "assume" from the way data is presented.

Anyway. Thanks for the interest and sorry that I do not have an elegant,smooth,smart solution that I can pass on.

Regards

Len B

 

[Brianwarnock]

Hi Len

Glad you solved your problem, simple solutions are always the best but its amazing that as projects grow we lose our way and can't see the wood for the trees.

Brian

 

[Len Boorman]

More like stumbling in the forest.

Cheers

len