Finding the next min

[carlton123]

Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?

 

[jdraw]

You could try

SELECT TOP 4 FieldInvolved FROM YOUR Tables order BY FieldInvolved DESC

see http://www.techonthenet.com/access/queries/top_results.php

 

[carlton123]

thanks for that but doesnt quite do what im looking for

 

[JHB]

In which way does it not fulfills your requirement?

 

[nanscombe]

Using "SELECT TOP 4 FieldInvolved FROM YOUR Tables order BY FieldInvolved DESC" might not bring back the correct values if you have duplicate values.

You might have to do in two parts.

Part 1) query01: "SELECT DISTINCT FieldInvolved FROM YOUR Tables" - Get unique values
Part 2) query02: "SELECT TOP 4 FieldInvolved FROM query01 order BY FieldInvolved DESC" - Get lowest 4 unique values

 

[jdraw]

Interesting catch nanscombe.

With this data

student	grade	id	Amount
bob	B-	1	8000
jim	A	2	251000
Sam	F	3	280000
Joe	B+	4	450000
Kent	C	5	650000
dave	C	6	280000

and this query

SELECT top 4 amount from grades order by amount desc

I get 4 highest values

amount
650000
450000
[COLOR="Sienna"]280000
280000[/COLOR]

and if I change the query to

SELECT top 4 amount from grades order by amount asc

I get 4 lowest values

amount
8000
251000
[COLOR="Sienna"]280000
280000[/COLOR]

However, if I don't include an Order By

SELECT top 4 amount from grades ;

I get 4 lowest distinct values

amount
8000
251000
280000
450000

Hmmm? Seems Access returns dups if you use an Order By.

Please note in earlier posts and responses, to get the Minimums you DO NOT do Order By DESC (that's for Maximums).

 

[nanscombe]

...

However, if I don't include an Order By

SELECT top 4 amount from grades ;

I get 4 lowest distinct values

amount
8000
251000
280000
450000

Hmmm? Seems Access returns dups if you use an Order By.

Please note in earlier posts and responses, to get the Minimums you DO NOT do Order By DESC (that's for Maximums).

I think you may just picking the first four rows, in order of data entry, regardless of their values.

amount
[COLOR="Red"]8000[/COLOR]
[COLOR="Lime"]251000[/COLOR]
[COLOR="Magenta"]280000[/COLOR]
[COLOR="Sienna"]450000[/COLOR]

student	grade	id	Amount
bob	B-	1	[COLOR="Red"]8000[/COLOR]
jim	A	2	[COLOR="Lime"]251000[/COLOR]
Sam	F	3	[COLOR="Magenta"]280000[/COLOR]
Joe	B+	4	[COLOR="Sienna"]450000[/COLOR]
Kent	C	5	650000
dave	C	6	280000

ETA:
I've just recreated your table and changed bob's grade to 8000000 and I just get the first four rows.

amount
[COLOR="Red"]8000000[/COLOR]
[COLOR="Lime"]251000[/COLOR]
[COLOR="Magenta"]280000[/COLOR]
[COLOR="Sienna"]450000[/COLOR]

 

[nanscombe]

We still don't know what carlton123 is actually looking for.

 

[Cronk]

I think we've got off track here.

Sorting in DESC order will place the highest at the top and the first 4 highest values will be selected.

Leave out the DESC to get the four lowest values.

 

[nanscombe]

I don't see any track to get off yet.

If you have values of:
A, 1
B, 2
C, 2
D, 3
E, 4
F, 4
G, 5
H, 6

Which does charlton123 want?
1 2 2 3 (lowest distinct records)
or
1 2 3 4 (lowest distinct values)

 

[jdraw]

I think we've got off track here.

Sorting in DESC order will place the highest at the top and the first 4 highest values will be selected.

Leave out the DESC to get the four lowest values.

See the "purple note in my previous post.

I also agree with nanscome -- we don't know what the original poster really wants/needs.

 

[carlton123]

Sorry its been a while before post but on the Min one in a query says the lowest one but im wanting to say min(2) which is 2nd lowest or even equal to first if there is two of them and min(3) min(4) I always find it hard to describe exactly what i mean lol

 

[jdraw]

Do you want

a) the lowest/smallest 4 values, or
b) the highest/largest 4 values?

Do you want unique/distinct values or, if there are duplicate, are the duplicates acceptable?

 

[carlton123]

smallest but in the summary if you put Min now then it shows the Min so I was wondering if you can stipulate 2nd lowest 3rd lowest etc

 

[jdraw]

smallest but in the summary if you put Min now then it shows the Min so I was wondering if you can stipulate 2nd lowest 3rd lowest etc

Please see nanscombe's post #10

 

[carlton123]

1 2 2 3 but if i say min(4) or whatever function it would say 3

 

[jdraw]

Do you have a function called Min?
We are telling you that if you want the 4 lowest values, you do as nanscombe showed previously.

Use a query to get distinct values; then use a Top 4 with the appropriate Order By to get the required values.

Why are you using Min?

 

[carlton123]

well i dont know how to do it it and i dont have a function called min but in a query when you summarise you can have the min value im wondering if it possible to say Min(3) or Min offset(3) to show the 3rd Min i need it to be in a summary otherwise its not what im looking for

 

[jdraw]

Please review my sample in post#6 -specifically the 4 lowest values part.
I supplied some sample data and a query.

You could try a query using your own data based on the sample.
Good luck.

 

[carlton123]

I dont think im explainging my self very well yes that gives me the values i want but not the way i want to display them think i will just leave thanks for everyones help