Full Version: Query To Choose The Smallest Of The Three...
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
williams9969
Hi all:

I am trying to build something into my query but need some help.

I need to display the smallest number of the three available parameters.

For instance

AST: = 20%
ASN: = 30%
ASQ: = 40%

Then the result (ASP) would be the below
ASP= 20%

I need to display them all in the same query.

Can anyone please assist?

Thanks in advance

Dan
theDBguy
Hi Dan,

What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question.

Are those fields in your table or data values? Can you illustrate exactly what you want the query to look like?

Not sure if you could just use the Min() function or need a different one.

Just my 2 cents... 2cents.gif
williams9969
Sorry about that...ACC 2007

The percentages I am trying to find the minimum of are based on calculations in the query.

I just need to display which one is the smallest while showing all three at the same time.

Thanks for your help!!!!

Dan
theDBguy
Hi Dan,

Can you post a sample query result? I think I can understand the question better when I can see how it looks like.

Thank you.

PS. Don't forget to use the <code> tags to preserve any spacing/formatting.
John Spencer
The logic using the IIF operator is

IIF(A<=B and A<=C,A,IIF(B<=C,B,C))

If you are doing this with calculated fields in the query, then you have to replace A, B, and C with the calculations and not with the field alias assigned to the calculation.
williams9969
Thank you both for taking the time to respond and assist me.

I got it working using John's response.

Again, thank you both and Happy Holiday's!

Respectfully

Dan
theDBguy
Hi Dan,

Glad to hear you found a solution that works for you.

yw.gif

John and I are happy to help. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.