Understanding VB6, Access 2000, DAO, SQL Queries

"My question: Can anyone help me code the SQL SELECT statements "
"For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA."

Select top 25 mytable.* from mytable order by NumfieldA desc

"The second recordset is the entire database."

I assume you mean the entire table.

Select * from mytable

Pete

Hi Pete;

I added the following code and ran into a syntax error on the first select statement:

    Set dbHist = OpenDatabase("c:\Documents and Settings\Joe\Data Files\MS-Visual Basic\SMA\HistoricalData.mdb")
    Set rs = dbHist.OpenRecordset("tblHistoricalData ", 2)
    Set rsPctChg = dbHist.OpenRecordset("tblHistoricalData ", 2)
   
    If rs.RecordCount > 0 Then
        SELECT TOP 25 rsPctChg.* FROM rs ORDER BY PercentChange desc
        SELECT * FROM rs ORDER BY DateFld, Symbol
    End If

I'm sorry to ask such basic questions, but I'm really new. Any assistance you and others can give is appreciated.

Joe

I don't really follow what you are doing in your SQL statements, but things don't work like that.
For the top 25 records you probably want:
 Set rs = dbHist.OpenRecordset("SELECT TOP 25 * FROM tblHistoricalData ORDER BY PercentChange desc", 2)

This is what you need for the 'whole table'
Set rsPctChg = dbHist.OpenRecordset(" SELECT * FROM tblHistoricalData ORDER BY DateFld, Symbol ", 2)

It looks like you need to do some reading and look at examples of recordset processing before getting much further.

Pete

Hi Pete;

Thanks for your response. I'll be the first to admit my weakness in understanding vb. And rest assured I'm reading daily to improve my understanding.

Yet, in spite of my unsophisticated explaination of the problem, you still seemed to have captured precisely what I'm trying to do. However, I do need to make one little tweek. I need the top 25 for each date (identified by DateFld), and I'll be cycling through the table building groups of 25 symbols day. I tried modifying your code as follows but it didn't work. Can you give me a hint as to where I'm wrong?

Your original code:    
                           Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                                ORDER BY PercentChange desc", 2)

My change:
                          Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = dteDateVar ORDER BY PercentChange desc", 2)

dteDateVar is a variable I decleared with a starting date and I will increment the date after each group of top 25 symbols. When I run my change I get:

                         "Runtime 3061. Too few parameters. Expected 1."

Thanks again for any assistance you and others can provide.

Joe

Pete;

Thank you for the modified code. It works like a champ. More importantly, thanks for the explaination as to "why" it works. I'll tuck that away in my toolkit. Your points are well deserved.

Joe