select min

[tomk01]

what am i doing wrong?

SELECT bcode, minimumService, accrualRate
FROM caccrchart
WHERE bcode = "VA1" AND minimumService >= 0
and accrualrate = min(accrualrate)

 

[PHV]

It would help if you explain what you are trying to do...
My guess:
SELECT bcode, minimumService, accrualRate
FROM caccrchart
WHERE bcode = "VA1" AND minimumService >= 0
and accrualrate = (SELECT min(accrualrate) FROM caccrchart)

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

 

[tomk01]

sorry...

I have a table that looks like this:

bcode MinmumService AccrualRate

Vac1 0 0.54
Vac1 24 3.08
vac1 60 4.62

i want to select 0.54 if the MinmumServices is > 0 and < 24. The problem is the MinmumService will change. the only thing that will remain constant is a numberVar that I have as "0" in my statment

 

[PHV]

You may try this (typed, untested):
SELECT A.AccrualRate
FROM caccrchart A
WHERE A.bcode = "Vac1" AND [searched service] Between A.MinmumService And (SELECT Min(B.MinmumService) FROM caccrchart B WHERE B.bcode = "Vac1" AND B.MinmumService>A.MinmumService)

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

 

[tomk01]

in this statment I dont understand how computer would know what the max and min are. In Access it is asking me for a min and a max. I need it to just run with out asking me.

 

[PHV]

In Access it is asking me for a min and a max
Can you please post the sql code and the exact access asking stuff, and what is the real issue ?

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

 

[tomk01]

SELECT A.AccrualRate
FROM caccrchart A
WHERE A.bcode = "Vac1" AND [searched service] Between A.MinmumService And (SELECT Min(B.MinmumService) FROM caccrchart B WHERE B.bcode = "Vac1" AND B.MinmumService>A.MinmumService)

 

[PHV]

[searched service] is the place holder for the value you want to test against Min[highlight]i[/highlight]mumService.
If access prompts for either x.MinimumService after the spelling corrections, you may try this:
SELECT A.AccrualRate
,(SELECT Min(B.MinimumService) FROM caccrchart B WHERE B.bcode = A.bcode AND B.MinimumService>A.MinimumService) AS MaximumService
FROM caccrchart A
WHERE A.bcode = "Vac1" AND [searched service] Between A.MinmumService And MaximumService

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