Choose the highest and second highest from three field

  • Thread starter Thread starter pakkoda
  • Start date Start date
P

pakkoda

Can anyone solve my problem ....
In query there are 3 field - Mark1, Mark2, Mark3. I like to do an expression
that we can choose the highest mark and second highest mark only from 3 mark
above.

Mark1 Mark2 Mark3
80 40 55

So expression automatic choose 80 and 55

Thank U , for somebody who try to assist me
 
The basic problem is that your table structure is wrong. You are going across
with the columns, as in a spreadsheet, instead of down like in a database. It
would look something like this:

Mark Score
1 80
2 40
3 55

Then a simple select query with a Top clause would find it when you sorted
by the Score field in ascending order.

Select Top 2 *
From YourTable
Order By Score ASC ;
 
Assumption for both calculations: A, B, and C all have a value
Max of three values
IIF(A>=B and A>=C,A,IIF(B>=C,B,C))

Additional Assumption: All three values are different
Second value
IIF(A Between B and C,A, IIF(B Between A and C,B,C))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I do believe that should be --
Select Top 2 [YourTable].*
From [YourTable]
Order By Score DESC;
 
I do believe that you are correct. Good thing that I use to fix aircraft and
not fly them. Getting up and down confused in that profession would be a bad
thing!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


KARL DEWEY said:
I do believe that should be --
Select Top 2 [YourTable].*
From [YourTable]
Order By Score DESC;

--
Build a little, test a little.


Jerry Whittle said:
The basic problem is that your table structure is wrong. You are going across
with the columns, as in a spreadsheet, instead of down like in a database. It
would look something like this:

Mark Score
1 80
2 40
3 55

Then a simple select query with a Top clause would find it when you sorted
by the Score field in ascending order.

Select Top 2 *
From YourTable
Order By Score ASC ;
Click to expand...
Click to expand...
 
Sorry... It's okey if we have field and all the marks in there, so we can
sort such as
Mark Score
1 80
2 40
3 55

I have three field like this

Englishmark 80
GeoMark 40
MathMark 55

So how do the query/expression automaticaly choose top two of marks... 80
and 55
--
GoodLuckAllOfYou


Jerry Whittle said:
I do believe that you are correct. Good thing that I use to fix aircraft and
not fly them. Getting up and down confused in that profession would be a bad
thing!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


KARL DEWEY said:
I do believe that should be --
Select Top 2 [YourTable].*
From [YourTable]
Order By Score DESC;

--
Build a little, test a little.


Jerry Whittle said:
The basic problem is that your table structure is wrong. You are going across
with the columns, as in a spreadsheet, instead of down like in a database. It
would look something like this:

Mark Score
1 80
2 40
3 55

Then a simple select query with a Top clause would find it when you sorted
by the Score field in ascending order.

Select Top 2 *
From YourTable
Order By Score ASC ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Can anyone solve my problem ....
In query there are 3 field - Mark1, Mark2, Mark3. I like to do an expression
that we can choose the highest mark and second highest mark only from 3 mark
above.

Mark1 Mark2 Mark3
80 40 55

So expression automatic choose 80 and 55

Thank U , for somebody who try to assist me
Click to expand...
Click to expand...
Click to expand...
 
Use the query that was posted substituting your table and field names.
--
Build a little, test a little.


pakkoda said:
Sorry... It's okey if we have field and all the marks in there, so we can
sort such as
Mark Score
1 80
2 40
3 55

I have three field like this

Englishmark 80
GeoMark 40
MathMark 55

So how do the query/expression automaticaly choose top two of marks... 80
and 55
--
GoodLuckAllOfYou


Jerry Whittle said:
I do believe that you are correct. Good thing that I use to fix aircraft and
not fly them. Getting up and down confused in that profession would be a bad
thing!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


KARL DEWEY said:
I do believe that should be --
Select Top 2 [YourTable].*
From [YourTable]
Order By Score DESC;

--
Build a little, test a little.


:

The basic problem is that your table structure is wrong. You are going across
with the columns, as in a spreadsheet, instead of down like in a database. It
would look something like this:

Mark Score
1 80
2 40
3 55

Then a simple select query with a Top clause would find it when you sorted
by the Score field in ascending order.

Select Top 2 *
From YourTable
Order By Score ASC ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Can anyone solve my problem ....
In query there are 3 field - Mark1, Mark2, Mark3. I like to do an expression
that we can choose the highest mark and second highest mark only from 3 mark
above.

Mark1 Mark2 Mark3
80 40 55

So expression automatic choose 80 and 55

Thank U , for somebody who try to assist me
Click to expand...
Click to expand...
Click to expand...
 
Back
Top