Understanding VB6, Access 2000, DAO, SQL Queries

Hello Experts;

I'm a new VB programmer and I'm writing an application to manipulate stock market data. I have an Access 2000 database that I process using VB6, referencing DAO. I want to declare two record sets and using one recordset as an input stream, cycle through the second recordset and process the data.

For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA.

The second recordset is the entire database.

Now, I need to find the symbol for each row in recordset 1 in recordset 2 and perform certain processing.

My question: Can anyone help me code the SQL SELECT statements to do the above? I can provide additional information if needed. Thanks for your help!

Joe
JoeWaynickAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = #" & dteDateVar  & "# ORDER BY PercentChange desc", 2)
Dates must be enclosed in #..#  and you need the variable to be evaluated so it has to be 'outside' the sql string.

Pete
0
 
peter57rCommented:
"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
0
 
JoeWaynickAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
peter57rCommented:
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
0
 
JoeWaynickAuthor Commented:
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

0
 
JoeWaynickAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.