Something difficult

[handsomed]

I have this query:

SELECT DISTINCT Q.[creation_date], Round(Now()-Q.[creation_date],0) AS Diferenc,
Q.[Nº Siniest], Q.Asunt, Q.Usu, Q.Obs
FROM [HISTORIC] AS Q
WHERE (((Q.[creation_date])=(SELECT MAX([creation_date])
FROM [HISTORIC] As T
WHERE T.[Nº Siniest] = Q.[Nº Siniest])) AND ((Q.Asunt) Not Like
"Autofact*") AND ((Q.Asunt) Not Like "Final*"))
ORDER BY Q.[creation_date];


[HISTORIC] has this format:

Nº Siniest creation_date Asunt

10408002464 18/11/2008 11:08:00 Repar cur
10408002464 18/11/2008 13:45:00 Pendiente Tramitador
10408002464 19/11/2008 10:15:00 Pendiente Tramitador
10408002464 25/11/2008 17:59:00 Reparaci¢n en curso
10408002464 02/12/2008 14:38:00 Pendiente Tramitador
10408002464 03/12/2008 12:17:00 Pendiente Tramitador
10408002464 05/12/2008 11:55:00 Valoración aceptada
10408002464 05/12/2008 11:55:00 Autofactur

I have a problem in the above case, where the last two rows have the same creation_date and should take the last row, however it takes the previous row (10408002464 05/12/2008 11:55:00 Valoración aceptada)

Any ideas?

 

[PHV]

How do you know that '05/12/2008 11:55:00 Valoración aceptada' is prior than '05/12/2008 11:55:00 Autofactur' ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[handsomed]

This is the order in the table. I created an autonumber field called IdNumber, so for example '05/12/2008 11:55:00 Valoración aceptada' would be IdNumber=7 and '05/12/2008 11:55:00 Autofactur' would be IdNumber=8, I think

 

[PHV]

I've reread your SQL and found that:

...AND ((Q.Asunt) Not Like "[!]Autofact*[/!]") ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[handsomed]

Yes that's the problem. My query must show the row which its [creation_date] is the last of one [Asunt] and the [Asunt] can't be in the state Autofactur. I mean, I'm trying to do a list of pending task, and when a [Nº Siniest] is Autofactur it supposed to be done and it shouldn't be listed.


So, in the example it shouldn't show any rows becouse the [Nº Siniest] is Autofactur :

10408002464 05/12/2008 11:55:00 Valoración aceptada
10408002464 05/12/2008 11:55:00 Autofactur


But it shows:
10408002464 05/12/2008 11:55:00 Valoración aceptada