Please help, I am new to Jet Engine and SQL 
Author Message
 Please help, I am new to Jet Engine and SQL

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



Wed, 12 May 1999 03:00:00 GMT
 Please help, I am new to Jet Engine and SQL

Quote:

>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.

Well, you could be totally inefficient and try this (if it's a small
program this shouldn't cause problems.

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



Fri, 14 May 1999 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Please help, I am new here

2. How can I create a new database using OLEDB provider for Microsof t Jet Engine 4.0

3. New JET Engine PERFORMANCE MONITOR and TRACE utility!!!

4. Jet engine 2.0 DLLs to create new records

5. New Jet Engine Performance Tuning and Analysis Tool!!!

6. New line break - ( I am new to the world of sql server)

7. Help I am new to SQL server

8. Am in SQL Hell please help

9. jet engine engine.idle

10. jet engine engine.idle

11. ODBC engine vs. Jet engine

12. T-SQL vs Microsoft Jet Database Engine SQL


 
Powered by phpBB® Forum Software