Need a query to return a value if out of range

[new2access123 via AccessMonster.com]

I have the table below. I need to create a query that will return a score
based on a passed Age and a passed LimitValue.

For example:
If Male= True, Age = 33 and LimitValue = 70 I need to return a score of 4.
Because Age <= AgeHighLimit and Male = True when the LimitValue >= HighLlimit
(60) in the table the highest score should be returned.

Likewise, if Male= True, Age = 33 but LimitValue = 24 I need to return a
score of 1. Because for that age and gender when the LimitValue <= HighLlimit
(25) in the table the lowest score should be returned.

I can write a query that will return the correct score if the Age and
LimitValue are with in the range of the values in the table. But if one or
both of the parameters is out of the range of the table values I am at a loss.

Help please

MECurlUpTable
MECurlUpID AgeLowLimit AgeHighLimit Male LowLimit HighLimit ITAScore
1 0 34 Yes 48 60 4 <= When passed LimitValue = 70 return this record.
2 0 34 Yes 37 47 3
3 0 34 Yes 26 36 2
4 0 34 Yes 0 25 1 <= When passed LimitValue = 24 return this record.
5 35 40 Yes 40 50 4
6 35 40 Yes 30 39 3
7 35 40 Yes 20 29 2
8 35 40 Yes 0 19 1
9 45 100 Yes 32 40 4
10 45 100 Yes 23 31 3
11 45 100 Yes 14 22 2
12 45 100 Yes 0 13 1
13 0 34 No 28 35 4
14 0 34 No 22 27 3
15 0 34 No 16 21 2
16 0 34 No 0 15 1
17 35 40 No 33 40 4
18 35 40 No 24 32 3
19 35 40 No 15 23 2
20 35 40 No 0 14 1
21 45 100 No 24 30 0
22 45 100 No 17 23 0
23 45 100 No 11 16 0
24 45 100 No 0 10 0

 

[Daryl S]

New2access123 -

It sounds like a data issue, not a query or coding issue.

Why is the highest HighLimit 60 for males aged 0 to 34, if you could pass in
a 70? I would expect the HighLimit of 60 to be changed to 70 or more, or a
new record added to the data that could accomodate a higher limit. The same
logic would apply to all male/female age ranges. What does the HighLimit
mean, and should the highest of the HighLimit values be changed or new
records added to accomodate higher ranges?

 

[new2access123 via AccessMonster.com]

I understand your points. But I do not have control over the brake down of
the look up tables. I have to use what is provided to me.

New2access123 -

It sounds like a data issue, not a query or coding issue.

Why is the highest HighLimit 60 for males aged 0 to 34, if you could pass in
a 70? I would expect the HighLimit of 60 to be changed to 70 or more, or a
new record added to the data that could accomodate a higher limit. The same
logic would apply to all male/female age ranges. What does the HighLimit
mean, and should the highest of the HighLimit values be changed or new
records added to accomodate higher ranges?

I have the table below. I need to create a query that will return a score
based on a passed Age and a passed LimitValue.

[quoted text clipped - 40 lines]

23 45 100 No 11 16 0
24 45 100 No 0 10 0

 

[Daryl S]

New2access123 -

If you can't fix the problem, then you can run a query to get around it.
The trick is to use the query you had working for where the limit is within
the range, and UNION ALL it to a query that only returns records if the limit
is over the range. I am giving you the basic (but untested) structure here.
You can create the two queries separately (you have the first one already),
and then do a UNION ALL for them.

For the query after the UNION ALL, if you build it in query design, the
entire subquery goes in the criteria. You will have to make sure the field
names (and any form names and control names are correct).

SELECT ITAScore from MECurlUpTable
Where Male = forms!FormName!Male
AND Forms!FormName!Age between AgeLowLimit and AgeHighLimit
AND Forms!FormName!LimitValue between LowLimit and HighLimit
UNION ALL
SELECT ITAScore from MECurlUpTable
Where Male = forms!FormName!Male
AND Forms!FormName!Age between AgeLowLimit and AgeHighLimit
AND HighLimit = (Select max(HighLimit) from MECurlUpTable AS ME2
Where ME2.Male = MECurlUpTable.Male
AND ME2.AgeLowLimit = MECurlUpTable.AgeLowLimit
AND Forms!FormName!LimitValue > ME2.HighLimit);

--
Daryl S

I understand your points. But I do not have control over the brake down of
the look up tables. I have to use what is provided to me.

New2access123 -

It sounds like a data issue, not a query or coding issue.

Why is the highest HighLimit 60 for males aged 0 to 34, if you could pass in
a 70? I would expect the HighLimit of 60 to be changed to 70 or more, or a
new record added to the data that could accomodate a higher limit. The same
logic would apply to all male/female age ranges. What does the HighLimit
mean, and should the highest of the HighLimit values be changed or new
records added to accomodate higher ranges?

I have the table below. I need to create a query that will return a score
based on a passed Age and a passed LimitValue.

[quoted text clipped - 40 lines]

23 45 100 No 11 16 0
24 45 100 No 0 10 0

--



.

 

[new2access123 via AccessMonster.com]

Thanks. I can use that approach.

New2access123 -

If you can't fix the problem, then you can run a query to get around it.
The trick is to use the query you had working for where the limit is within
the range, and UNION ALL it to a query that only returns records if the limit
is over the range. I am giving you the basic (but untested) structure here.
You can create the two queries separately (you have the first one already),
and then do a UNION ALL for them.

For the query after the UNION ALL, if you build it in query design, the
entire subquery goes in the criteria. You will have to make sure the field
names (and any form names and control names are correct).

SELECT ITAScore from MECurlUpTable
Where Male = forms!FormName!Male
AND Forms!FormName!Age between AgeLowLimit and AgeHighLimit
AND Forms!FormName!LimitValue between LowLimit and HighLimit
UNION ALL
SELECT ITAScore from MECurlUpTable
Where Male = forms!FormName!Male
AND Forms!FormName!Age between AgeLowLimit and AgeHighLimit
AND HighLimit = (Select max(HighLimit) from MECurlUpTable AS ME2
Where ME2.Male = MECurlUpTable.Male
AND ME2.AgeLowLimit = MECurlUpTable.AgeLowLimit
AND Forms!FormName!LimitValue > ME2.HighLimit);

I understand your points. But I do not have control over the brake down of
the look up tables. I have to use what is provided to me.

[quoted text clipped - 15 lines]