Selecting specific records

[Shorin]

Hi,

I am having a blind moment on this problem and hoped someone can help.

I have a table that contains date specific events. I need to cycle through the records in the table and for each record update a field based on a stats calculation of data in the 6 previous records (date wise). So for instance if record 7 has a date of 31st january i need the 6 previous records - eg 28th, 16th, 12th, 10th, 5th, 1st.

I plan to handle the data manipulation in an array and have no problems on that side but for the life of me i can't seem to get my head around correctly selecting those 6 records.

Any pointers folks?

Thanks

Mike

 

[vbaInet]

The previous records from where? The same table?

 

[Shorin]

Sorry - yep same table!

 

[vbaInet]

So after updating the 6 previous records where the date is 31st January, do you move to the 28th and update the 6 previous records too? How should it work?

 

[Shorin]

Hi vbaInet,

Sorry not making myself clear am i. Right let's start again.

Imagine a table with say 100 records in it ordered by a date field. I need to cycle through each record, look at the date and see if there are 6 records prior to it. Obviously the first 6 records wont have 6 prior records.
If there are 6 records i need to put those 6 records into an array - do a bit of number crunching and then update a field in record 7. Then move to the next record and start again. So effectively the routine will move through the table updating each record based on calculations of the previous 6.

Hope that makes sense.

Thanks
Mike

 

[vbaInet]

You don't need to save the records into an array. Just use a separate recordset. Some aircode:

dim rsMain as dao.recordset, rsCheck as dao.recordset, db as dao.database

set db = CurrentDb
set rsMain = db.openrecordset("Select * From TableName Order By [DateField]")

do while not rsMain.eof
    set rsCheck = db.openrecordset("Select Top 6 * From TableName Where  [DateField] < " & rsMain![DateField] & " Order By  [DateField]")

    with rsCheck
        if .recordcount = 6 then
            do while not .eof
                ' Perform number crunching for each record here
                .movenext
            loop
        end if
    end with

    rsMain.edit
         ' Update the record here based on the results from the other recordset
    rsMain.update

    rsMain.movenext
loop

set rsMain = nothing
set rsCheck = nothing

 

[Shorin]

I thought i would use an array because the calculations based on the 6 selected records would use aggregates such as sum and count.

 

[vbaInet]

It's up to you, you've got the aircode to play with.

 

[Shorin]

Thanks vbaInet - i'll give it a try - you're a star!

 

[vbaInet]

I thought i would use an array because the calculations based on the 6 selected records would use aggregates such as sum and count.

Explain this a bit more so I get a better picture.

 

[Shorin]

Hi,

I'm just trying your code but get a Runtime error 3265 (item not found in this collection) on the rsCheck SQl part of it

Set rsMain = db.OpenRecordset("SELECT DataBlock.*, DataBlock.Date FROM DataBlock ORDER BY DataBlock.Date;")

Do While Not rsMain.EOF
Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.*, DataBlock.Date FROM DataBlock" & _
" WHERE (((DataBlock.Date) < " & rsMain!DataBlock.Date & ")) ORDER BY DataBlock.Date;")

 

[vbaInet]

What is error 3265?

 

[Shorin]

item not found in this collection

 

[Shorin]

I think my SQL is correctly syntaxed but not sure

 

[vbaInet]

I've amended it:

Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.* FROM DataBlock " & _
                               "WHERE [Date] < " & rsMain![Date] & " ORDER BY [Date];")

Note that you use just the field name with rsMain, such as rsMain![Field]. Also because you are using an Access reserved keyword, Date (which is not adviceable), you must enclose it in square brackets.

 

[Shorin]

I've changed the table field 'date' to 'eventdate' so as to remove the reserved word issue but still get 'item not found in this collection'

Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.*, [EventDate] FROM DataBlock " & _
" WHERE [EventDate] < " & rsMain![EventDate] & " ORDER BY [EventDate];")

 

[vbaInet]

I've amended your sql again, don't change it or add [EventDate] or put a space before WHERE. DataBlock.* already includes that field and there's a space after FROM DataBlock.

Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.* FROM DataBlock " & _
                               "WHERE [EventDate] < " & rsMain![EventDate] & " ORDER BY [EventDate];")

With regards your problem, are you sure you saved the table after making the change?

 

[Shorin]

Yep table is definately saved

SQL now reads as per your changes

Set rsCheck = db.OpenRecordset("SELECT TOP 6 DataBlock.* FROM DataBlock " & _
"WHERE [EventDate] < " & rsMain![EventDate] & " ORDER BY [EventDate];")


Still getting same error though

 

[vbaInet]

Let me see a stripped down version of your db.

 

[Shorin]

Here's the DB with the datablock table, a form and your code