# Thread: SQL problem

1. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316

## 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. Registered User
Join Date
Jul 2003
Posts
73
Personally - I think you should only show the minimum limits in the tblRules table as follows (using your examples from above):

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

One way could be:

Code:
```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:

Code:
`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.

3. Registered User
Join Date
Jul 2002
Location
Island of Dots
Posts
316
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
•