Results 1 to 3 of 3

Thread: SQL problem

  1. #1
    Join Date
    Jul 2002
    Island of Dots

    Unanswered: SQL problem

    I am performing a count of field RFA in tblMain. I then need that value to ascertain which Code to get from tblRules using the following SQL:

    SELECT Code
    FROM tblRules
    WHERE ((Rule) = " & intCountOfRFA & "));

    The only solution I can think of is to setup tblRules as follows:

    Rule Code
    1 4/4
    2 4/4
    3 4/4
    4 4/4
    5 4/4
    6 4/4
    7 4/5
    8 4/5
    9 4/5
    10 4/6

    The trouble is, there may be no upper limit to intCountOfRFA so its impractical to have a table full of never ending numbers. I could hard-code the rules, but I'd prefer a more elegant solution which uses just the table to lookup. Here is what tblRules would ideally look like:

    Rule Code
    RFA<=6 4/4
    RFA>6 AND <=9 4/5
    RFA>9 4/6

    I somehow need to match-up the expressions in Rule with intCountOfRFA. Any help much appreciated.
    Last edited by bcass; 05-15-04 at 10:26.

  2. #2
    Join Date
    Jul 2003
    Personally - I think you should only show the minimum limits in the tblRules table as follows (using your examples from above):

    MinRule Code
    ------- ----
    0 4/4
    7 4/5
    10 4/6

    One way could be:

    Dim rsMinRule as Recordset
    Dim strCodeToUse as String
      Set rsMinRule = CurrentDb.OpenRecordset("SELECT Code FROM tblRules WHERE MinRule <= " & intCountOfRSA & " ORDER BY MinRule;")
      If Not rsMinRule.EOF Then
        strCountToUse = rsMinRule!Code
      End If
    This gets all of the MinRules which are greater than or equal to the one you're looking for - but orders it by MinRule. Therefore - the first one you retrieve from the recordset will be the category (level) that you want to use.

    Another (pure SQL) way may be:

    strSQL = "SELECT Code WHERE MinRule = (SELECT Max(MinRule) FROM tblRules WHERE MinRule <= " & intCountOfRFA & ")"
    This one does the same as the VB code above - but does it without having to select all of the rows from the tblRules table. It may be a little more efficient - depending on how many rows you'll have in tblRules.
    Last edited by joeldixon66; 05-15-04 at 12:04.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    Jul 2002
    Island of Dots
    Thats exactly what I was looking for. Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts