Finding the last record for each day

[resumes123]

I am using VB 6. I am fetching values from an MS Access table called SALES. from Sales.mdb It has daily Sales values of the company. It has Sales value for each hour for every day. Hence for each day there might be several records.

The fields in the table are DATE, TIME, SALE_COST

I need to find the last row for each day and print the values on an Excel sheet.
The Date, Time, Sale_Cost at the last value of each day have to be displayed
All dates need not end at exactly the same date. Different dates have different number of rows.

I am currently working on a Code already written by another person. The entire table contents are taken in a single recordset.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\data\SALES.mdb")
Set rs = db.OpenRecordset("Select * from SALES", dbOpenDynaset)

The record set "rs" is already open and I need to work on this recordset only. I would not like to write another recordset grouping on dates.

Could some one please suggest a Visual Basic code how I can manipulate this recordset RS to get the last record for each day and print on to another Excel sheet

Thanks

 

[PatrickIRL]

In your SQL statement, instead of ...

Select * from SALES

... try this ...

SELECT <datefield>, max(<datefield>) AS maxDate 
FROM Sales GROUP BY <datefield>

Obviously replace <datefield> with whatever date field you are using.

Patrick

 

[strongm]

>I would not like to write another recordset grouping on dates

Can I ask why not? As Patrick has shown, creating a new recordset is about the easiest way of doing this

 

[Jasen]

Yeah, it's really not a big deal to open a new rs object with an appropriate query.

The other option I'd probably take is to cycle through the entire recordset looking for the latest time field for each date and recording the time and sale_cost on your Excel sheet.

Not hard, but then not nearly as efficient as what has already been suggested.

I.E. something like...

Dim dt, tm as String

rs.Sort = "Date DESC, Time ASC"

dt = CStr(rs!Date)

Do Until rs.EOF

    If CStr(rs!Date) = dt Then
        ' same date, next higher time
        tm = CStr(rs!Time)
    Else
        ' new date, our tm variable holds the max time for the previous date

        ' <put your dt, tm, and rs!sale_cost somewhere now>
        ' imaginary function to do this:
        AddExcelRow(dt, tm, rs!Sale_Cost)

        ' next date
        dt = CStr(rs!Date)
    End If

    rs.MoveNext
Loop

Pseudo-code off the top of my head.
That code makes the assumption that date holds a short date, not a full DateTime--in which case you'll have to adjust by perhaps using a DatePart function to grab and compare day-of-year or something.
This is still more clumsy than doing it in the query.

 

[strongm]

Did any of this help?