Very Computer

I am writing software for a Water Utilities company (a small one) and
being new to using the Jet engine to do
databases in VB, I have run into problems.  Conceptually I think I
understand relational databases and SQL, but I
can't get the syntax working for me.  Below are the two tables I am
using tabCustomers and tabReadings.
They should be joined by CLASS.  One customer has a unique Class and
the reading table will be full of readings
with this class number identified by Class and Date as to which is
most recent.

CUSTOMER TABLE   tabCustomers
  has the following fields
  Class     'a unique identifier of which customer
  FirstName
  MidInit
  LastName
  and other customer fields

READING TABLE    tabReadings
  has the following fields
  Class
  Date
  Reading

I wanted to have all of the meter readings for every customer in the
reading database.  I need to be able
to go to a particular customer found by Class and get the entry with
the most recent date.  That will be the previous reading,
and the user will add the current month's reading and add a new record
to the READING TABLE with that reading and date.
I have been unsuccessful at getting the most current reading of each
customer placed into the grid control
below is the code I am using and many ways I have tried to accomplish
this.  I can't get the SQL syntax right, or
I just don't know what I am doing one.

I thought I should JOIN the two tables based on
tabCustomer.CLASS=tabReading.CLASS and order it by tabReading.Date
DESC to get the most recent one for each customer.
What am I doing wrong and do you have a better suggestion?

Thanks,

Shane

Sub InitGrid
   Dim First As Boolean

   First = True
   grdReadings.Row = 1

'loop through ever customer, get corresponding--most recent--reading
from the READING TABLE (tabReadings)

   Do Until rstCust.EOF
     'I tried this-----
      ' rstRead.Sort = "Date desc"
      ' rstRead.Requery
      ' rstRead.FindFirst ("cstr(class)='" & CStr(rstCust!Class) &
"'")
     '----------------
  'I also tried each of the lines below.
'     rstRead.FindFirst ("cstr(class)='" & CStr(rstCust!Class) & "'
ORDER BY [" & CStr(Date) & "] DESC")
'     rstRead.FindFirst ("tabCustomer INNER JOIN tabReading ON
tabCustomer.Class='" & tabReading.Class & "' ORDER BY Date Desc")
'     rstRead.FindFirst ("cstr(class)='" & CStr(rstCust!Class) & "'
ORDER BY '" & CStr(rstRead!Date) & "'")
'     rstRead.FindFirst ("cstr(class)='" & CStr(rstCust!Class) & "'
ORDER BY '" & CStr(rstRead!Date) & "' DESC")

'this stuff below isn't part of my question
      With grdReadings
         If Not First Then
            .Rows = .Rows + 1
            .Row = .Row + 1
         End If
         First = False
         .Col = 0: .Text = rstCust!Class 'display class#
         .Col = 1: .Text = MakeName(rstCust!firstname,
NullToString(rstCust!MidInit), rstCust!lastname) 'Display name
         .Col = 2: .Text = rstRead!reading 'display most recent
reading for this customer in this PREV READING col and in
         .Col = 3: .Text = rstRead!reading 'this CURRENT READING col
(as a default current reading)
         .Col = 4: .Text = 0               '0 difference to start with
      End With

      rstCust.MoveNext
   Loop

End Sub

Dim RS as Recordset

Set RS = db.OpenRecordset("SELECT * FROM READING WHERE CLASS = " &
MyClass & " ORDER BY DATE", dbOpenSnapshot)

RS.MoveLast
If Not RS.EOF and Not RS.BOF Then
        Debug.Print RS!Reading
else
        Debug.Pring "No Records found for this class id: " & MyClass
endif

That would work, but tweeking an SQL statement just right will do it
too, if you find that to be a problem just do some research into SQL,
I can't think of the SQLstring you need right off the top of my head
but it can be done, I can tell you that.

        Good Luck
                Mirko Crevatin