TopValues must be descending, but records must be shown ascending

[AlexMidd]

OK, this one is doing my head in. I am sure there must be an answer out there as it seems so elementary but I cannot get my head round it.

I have a VB app that accesses an Access database. I want to pick the last 10 records according to date/time. This requires me to set up a query using SQL that uses the TOP keyword. For this to work I must sort in DESCENDING order, otherwise it takes the FIRST ten records. The problem is, the ten records that result from this need to be shown in ASCENDING order, i.e. earliest date first up to the last date in the recordset.

If I was doing this within Access it would be no problem, I would just do a query for the first bit then incorporate that into a second query (I think). However, I cannot do this in VB. I have a recordset that picks out the last 10 records but they are in descending order. I cannot work out how to do another recordset that picks the 10 from the first recordset and reorders them in ascending order. I have put the name of the first recordset into the SQL for the second one but it objects, saying "Type Mismatch" or "Recordset not found".

I am sure it can be done quite easily but it escapes me at the moment. Anyone got any ideas? Ta. Have fun! )

Alex Middleton

 

[r937]

your idea of the query of query in access will work

select top 10 datefield, othercolumns
from yourtable
order by datefield desc

save this query in access as top10query, then

select datefield, othercolumns
from top10query
order by datefield

not sure if acccess can do in one step (sql/server can) but you could try

select datefield, othercolumns
from
(select top 10 datefield, othercolumns
from yourtable
order by datefield desc)
order by datefield

rudy

http://rudy.ca/