SQL Question

[Asterix]

Hi all,

In my projekt I use an Access database everything works great but what I don't know how to do is:

In the database there are several records with the same "Projektnummer" what I need is the SQL that takes only the last record from that "Projektnummer".
Tijd is the date and time when the record was added to the database.

Who can help me with this one?

Thanks in advance

Code :

DataEnvironment1.Commands.Item("Command1").CommandText = "SELECT Tijd, Projektnummer, Klantnaam, Kontactpersoon, Datum_Opdracht, Datum_Oplevering, Status, Projektleider, Start_C, Gereed_C, Gereed_PC, Uitv_C, Uren_C, Start_E, Gereed_E, Gereed_PE, Uitv_E, Uren_E, Start_T, Gereed_T, Gereed_PT, Uitv_T, Uren_T, Start_W, Gereed_W, Gereed_PW, Uitv_W, Uren_W, Start_B, Gereed_B, Gereed_PB, Uitv_B, Uren_B, Start_S, Gereed_S, Gereed_PS, Uitv_S, Uren_S, Start_I, Gereed_I, Gereed_PI, Uitv_I, Uren_I " & _
"From Projekten " & _
"WHERE (Projektnummer like '" & Text1.Text & "')" & _
"ORDER BY Tijd;"

[LucGuldentops]

Just replace this line "ORDER BY Tijd;"

with

"ORDER BY Tijd DESC;"

This will work if the field where you store the date is in a dateformat.

If you store it as a string, you should convert it to a date to get a good descending order.

"ORDER BY cdate(Tijd) DESC;"


Now the first record should point to your latest added record.

Greetz, Luc

[Asterix]

Hi Luc,

This isn't what i was looking for when i use "ORDER BY Tijd DESC;" then I get also all the records.

What I need is only the kast record.

[LucGuldentops]

Sorry, I missunderstud you.

You should include a subquery like this:

WHERE Tijd=(SELECT MAX(tijd) FROM Projekten;

Greetz, Luc

[LucGuldentops]

I don't know how to set this smilies thing off, offcourse the smily must be replaced with ; and )