Query to display the first value that meets criteria 1

[ba4crm]

I need some help in creating my query correctly. I have the following table:

tblPlans
PlanID PlanLevel MinAge MaxAge Points
A 1 0 18 5
A 2 0 18 11
A 3 0 18 20
: :
X 1 90 99 190
X 5 90 99 200

Based on the data from two fields on a form (txtAge, txtPoints), I have the following query:

Select PlanID, PlanLevel

From tblPlans

Where [txtAge] BETWEEN MinAge AND MaxAge
AND Points >= [txtPoints].

For the example:
txtAge = 15, txtPoints = 9, I would like my query to display PlanID A, PlanLevel 2, but not PlanId A/PlanLevel3 (only display the first PlanLevel within any PlanId that meets the criteria. The first in this instance is the one that is closest to the value of >= [txtPoints]).
One way that I can think of is to break out the points into a MinPoints and MaxPoints and use a query
Where [txtPoints] >= MinPoints and < MaxPoints.

Is there an alternate way of doing this?

Thank you

 

[PHV]

Perhaps something like this ?
SELECT A.PlanID, A.PlanLevel
FROM tblPlans AS A
WHERE [txtAge] BETWEEN A.MinAge And A.MaxAge
AND A.Points = (SELECT Min(Points) FROM tblPlans WHERE PlanID=A.PlanID AND Points>=[txtPoints])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ba4crm]

Thank you PHV.
This may be a really amateur question - what does the "A." in your SELECT statement indicate?
Since I am not as familiar with SQL statements, is it possible to do this in design view (my earlier query was in design view)?

 

[lespaul]

the "A" is an alias for the table and it allows you to reference that table by the alias (makes for easier typing in some cases).

It may be possible to do this in the design grid, but you could just switch to the SQL view and paste the query and have it work.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[GKChesterton]

PHV, this is almost eerie isn't it? I was using "Plan" and "Score" has hypothetical entries. But also some of the issues are the same ... Maybe a FA[.s]Q entry is in order? -- Chesterton

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[ba4crm]

Hi PHV,
Can you review this one more time for me? I used your code
from this thread and the answer that I received was not what I was expecting. It is possible that I was asking the wrong question.

I used Age = 22, Points = 15. For each PlanID, there are 3 levels for each age group. Each level has a different maximum points allowed. If someone reaches the max points, they get the next level.
For the above criteria, my query displays, PlanCode 5,6,11,12,16,17, and 18. What it should display is the first level for each PlanID that meets the criteria. It should display 5,11, and 16.

When I used your criteria, I received only PlanCode 5 (the one with the lowest Point value for all the plans that met the criteria).

If I changed the "Min" to "Max" in the code, it gives me PlanID 6,12, and 18 (the ids for the highest points for each PlanID). It does not work with Min though.
Any suggestions

tblPlans
Plan PlanID Plan Min Max Points
Code Level Age Age
1 A 1 0 18 4
2 A 2 0 18 8
3 A 3 0 18 13
4 A 1 19 40 10
5 A 2 19 40 18
6 A 3 19 40 28
7 B 1 0 18 7
8 B 2 0 18 11
9 B 3 0 18 17
10 B 1 19 40 12
11 B 2 19 40 20
12 B 3 19 40 32
13 C 1 0 18 15
14 C 2 0 18 18
15 C 3 0 18 22
16 C 1 19 40 20
17 C 2 19 40 25
18 C 3 19 40 31

 

[PHV]

What SQL code gives which result ?

You may try this:
SELECT A.PlanCode, A.PlanID, A.PlanLevel
FROM tblPlans AS A
WHERE [txtAge] BETWEEN A.MinAge And A.MaxAge
AND A.Points = (SELECT Min(Points) FROM tblPlans WHERE PlanID=A.PlanID
AND Points>=[txtPoints] AND MinAge=A.MinAge AND MaxAge=A.MaxAge)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ba4crm]

I get PlanCode 5,6,11,12,16,17,and 18 for SQL code:
SELECT A.PlanCode, A.PlanID, A.PlanLevel
FROM tblPlans AS A
WHERE ((([Forms]![frmPlans]![txtAge]) Between [A].[MinAge] And [A].[MaxAge]) AND ((A.Points)=(SELECT Min(Points) FROM tblPlans WHERE PlanCode=A.PlanCode
AND Points >= [Forms]![frmPlans]![txtPoints])));

I get PlanCode 5 for SQL code:
SELECT A.PlanCode, A.PlanID, A.PlanLevel
FROM tblPlans AS A
WHERE ((([Forms]![frmPlans]![txtAge]) Between [A].[MinAge] And [A].[MaxAge]) AND ((A.Points)=(SELECT Min(Points) FROM tblPlans WHERE PlanID=A.PlanID
AND Points >= [Forms]![frmPlans]![txtPoints])));

I still get PlanCode 5 only when I add the new line from your last thread. I am still unable to get it to display 5, 11, and 16 only.
Should I try having a MinPoints and MaxPoints field instead of just one Points field?

 

[PHV]

With your posted data and my previous suggestion, I get this (txtPoints=15, txtAge=22) :
[tt]
PlanCode PlanID PlanLevel
5 A 2
11 B 2
16 C 1[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ba4crm]

It works now - thank you for your help and patience PHV.
A star is on the way!!!