Sorting Percentages

G

Guest

Hello!

I have this query and I would like to make it only give me the first and the
second highest percentage. I also I need to be able to view what LakeID and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last step in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea], qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
 
J

John Viescas

Well, for starters you don't need a Totals query. First, save a simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
G

Guest

Thanks John.

Your help is appreciated! However, what if I want to see all the top two
percentages in my table and not just two?



John Viescas said:
Well, for starters you don't need a Totals query. First, save a simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Hello!

I have this query and I would like to make it only give me the first and
the
second highest percentage. I also I need to be able to view what LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last step in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea], qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
 
J

John Viescas

The SQL I gave you should do the top two for every LakeID. If that's not
what you want, then I don't understand your question, so maybe an example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John.

Your help is appreciated! However, what if I want to see all the top two
percentages in my table and not just two?



John Viescas said:
Well, for starters you don't need a Totals query. First, save a simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Hello!

I have this query and I would like to make it only give me the first
and
the
second highest percentage. I also I need to be able to view what LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,

What I mean is that now I am able to pull out the top two but, I am only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID and its
top two percentages.

Thank you.



John Viescas said:
The SQL I gave you should do the top two for every LakeID. If that's not
what you want, then I don't understand your question, so maybe an example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John.

Your help is appreciated! However, what if I want to see all the top two
percentages in my table and not just two?



John Viescas said:
Well, for starters you don't need a Totals query. First, save a simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the first
and
the
second highest percentage. I also I need to be able to view what LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
J

John Viescas

That SQL should give you them all. JET should rerun the subquery for each
new LakeID it encounters. Something else is going on to limit the LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query that is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
John,

What I mean is that now I am able to pull out the top two but, I am only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID and
its
top two percentages.

Thank you.



John Viescas said:
The SQL I gave you should do the top two for every LakeID. If that's not
what you want, then I don't understand your question, so maybe an example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John.

Your help is appreciated! However, what if I want to see all the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the first
and
the
second highest percentage. I also I need to be able to view what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks John!

I tried the query again and it worked! I want to paste the results to excel,
I get the top two for all but they are not going from the biggest to the
smallest. For example if I have 50% and 40% for LakeID1 the query shows 40%
first and then 50%. I tried sorting but it won't work itried max and it would
not work. Hopefully you see this message and you gaian help me. Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



John Viescas said:
That SQL should give you them all. JET should rerun the subquery for each
new LakeID it encounters. Something else is going on to limit the LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query that is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
John,

What I mean is that now I am able to pull out the top two but, I am only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID and
its
top two percentages.

Thank you.



John Viescas said:
The SQL I gave you should do the top two for every LakeID. If that's not
what you want, then I don't understand your question, so maybe an example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the first
and
the
second highest percentage. I also I need to be able to view what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
J

John Viescas

An Order By should work:

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY LakeID, Percentage Desc;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John!

I tried the query again and it worked! I want to paste the results to
excel,
I get the top two for all but they are not going from the biggest to the
smallest. For example if I have 50% and 40% for LakeID1 the query shows
40%
first and then 50%. I tried sorting but it won't work itried max and it
would
not work. Hopefully you see this message and you gaian help me. Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



John Viescas said:
That SQL should give you them all. JET should rerun the subquery for
each
new LakeID it encounters. Something else is going on to limit the
LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query that
is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
John,

What I mean is that now I am able to pull out the top two but, I am
only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when
the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID
and
its
top two percentages.

Thank you.



:

The SQL I gave you should do the top two for every LakeID. If that's
not
what you want, then I don't understand your question, so maybe an
example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the
first
and
the
second highest percentage. I also I need to be able to view what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,

Thank you! The Query is now fully functional.

I really appreciate you taking the time to help.

John Viescas said:
An Order By should work:

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY LakeID, Percentage Desc;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John!

I tried the query again and it worked! I want to paste the results to
excel,
I get the top two for all but they are not going from the biggest to the
smallest. For example if I have 50% and 40% for LakeID1 the query shows
40%
first and then 50%. I tried sorting but it won't work itried max and it
would
not work. Hopefully you see this message and you gaian help me. Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



John Viescas said:
That SQL should give you them all. JET should rerun the subquery for
each
new LakeID it encounters. Something else is going on to limit the
LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query that
is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,

What I mean is that now I am able to pull out the top two but, I am
only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when
the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID
and
its
top two percentages.

Thank you.



:

The SQL I gave you should do the top two for every LakeID. If that's
not
what you want, then I don't understand your question, so maybe an
example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the
first
and
the
second highest percentage. I also I need to be able to view what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,
Sorry, but one last thing. I can also get the top percentage by changing the
top value to 1. If I want the only the second value what do you suggest.

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 1 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY qryPercent.LakeID, qryPercent.Percentage DESC;


Yubasus said:
John,

Thank you! The Query is now fully functional.

I really appreciate you taking the time to help.

John Viescas said:
An Order By should work:

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY LakeID, Percentage Desc;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
Thanks John!

I tried the query again and it worked! I want to paste the results to
excel,
I get the top two for all but they are not going from the biggest to the
smallest. For example if I have 50% and 40% for LakeID1 the query shows
40%
first and then 50%. I tried sorting but it won't work itried max and it
would
not work. Hopefully you see this message and you gaian help me. Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



:

That SQL should give you them all. JET should rerun the subquery for
each
new LakeID it encounters. Something else is going on to limit the
LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query that
is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,

What I mean is that now I am able to pull out the top two but, I am
only
allowed to see the top two for one lakeID. However, I have hundreds of
LakeIDs and I would like to see all the top two for every LakeID when
the
query runs.

Hopefully that makes sense. Your SQL works but I only get one LakeID
and
its
top two percentages.

Thank you.



:

The SQL I gave you should do the top two for every LakeID. If that's
not
what you want, then I don't understand your question, so maybe an
example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me the
first
and
the
second highest percentage. I also I need to be able to view what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] DESC;
Click to expand...
Click to expand...
Click to expand...
 
J

John Viescas

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE Percentage =
(SELECT Max(Percentage) FROM qryPercent As Q2
WHERE Q2.Percentage <
(SELECT Max(Percentage) FROM qryPercent As Q3
WHERE Q3.LakeID = qryPercentage.LakeID)
AND Q2.LakeID = qryPercent.LakeID)

... but I'm not sure that the JET query engine is sophisticated enough to
parse that. You could use the above in SQL Server. Basically, the query is
looking for the largest percentage that is less than the largest percentage
(the 2nd largest).

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
John,
Sorry, but one last thing. I can also get the top percentage by changing
the
top value to 1. If I want the only the second value what do you suggest.

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 1 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY qryPercent.LakeID, qryPercent.Percentage DESC;


Yubasus said:
John,

Thank you! The Query is now fully functional.

I really appreciate you taking the time to help.

John Viescas said:
An Order By should work:

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY LakeID, Percentage Desc;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John!

I tried the query again and it worked! I want to paste the results to
excel,
I get the top two for all but they are not going from the biggest to
the
smallest. For example if I have 50% and 40% for LakeID1 the query
shows
40%
first and then 50%. I tried sorting but it won't work itried max and
it
would
not work. Hopefully you see this message and you gaian help me.
Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



:

That SQL should give you them all. JET should rerun the subquery
for
each
new LakeID it encounters. Something else is going on to limit the
LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query
that
is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,

What I mean is that now I am able to pull out the top two but, I
am
only
allowed to see the top two for one lakeID. However, I have
hundreds of
LakeIDs and I would like to see all the top two for every LakeID
when
the
query runs.

Hopefully that makes sense. Your SQL works but I only get one
LakeID
and
its
top two percentages.

Thank you.



:

The SQL I gave you should do the top two for every LakeID. If
that's
not
what you want, then I don't understand your question, so maybe an
example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all
the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save
a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me
the
first
and
the
second highest percentage. I also I need to be able to view
what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my
last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID
=
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea]
DESC;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you! It worked!

John Viescas said:
SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE Percentage =
(SELECT Max(Percentage) FROM qryPercent As Q2
WHERE Q2.Percentage <
(SELECT Max(Percentage) FROM qryPercent As Q3
WHERE Q3.LakeID = qryPercentage.LakeID)
AND Q2.LakeID = qryPercent.LakeID)

... but I'm not sure that the JET query engine is sophisticated enough to
parse that. You could use the above in SQL Server. Basically, the query is
looking for the largest percentage that is less than the largest percentage
(the 2nd largest).

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Yubasus said:
John,
Sorry, but one last thing. I can also get the top percentage by changing
the
top value to 1. If I want the only the second value what do you suggest.

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 1 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY qryPercent.LakeID, qryPercent.Percentage DESC;


Yubasus said:
John,

Thank you! The Query is now fully functional.

I really appreciate you taking the time to help.

:

An Order By should work:

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)))
ORDER BY LakeID, Percentage Desc;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John!

I tried the query again and it worked! I want to paste the results to
excel,
I get the top two for all but they are not going from the biggest to
the
smallest. For example if I have 50% and 40% for LakeID1 the query
shows
40%
first and then 50%. I tried sorting but it won't work itried max and
it
would
not work. Hopefully you see this message and you gaian help me.
Thank you
very much!

SELECT qryPercent.LakeID, qryPercent.Percentage, qryPercent.GR5
FROM qryPercent
WHERE (((qryPercent.Percentage) In (SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc)));



:

That SQL should give you them all. JET should rerun the subquery
for
each
new LakeID it encounters. Something else is going on to limit the
LakeID.

Please post the SQL from qrySumArea, qryPercent, and the final query
that
is
returning only one lake.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,

What I mean is that now I am able to pull out the top two but, I
am
only
allowed to see the top two for one lakeID. However, I have
hundreds of
LakeIDs and I would like to see all the top two for every LakeID
when
the
query runs.

Hopefully that makes sense. Your SQL works but I only get one
LakeID
and
its
top two percentages.

Thank you.



:

The SQL I gave you should do the top two for every LakeID. If
that's
not
what you want, then I don't understand your question, so maybe an
example
would be in order.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks John.

Your help is appreciated! However, what if I want to see all
the top
two
percentages in my table and not just two?



:

Well, for starters you don't need a Totals query. First, save
a
simple
query to calculate the percentage:

qryPercent:
SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Percentage,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID =
qrySumArea.LakeID

Now build a query to get the top 2:

SELECT LakeID, Percentage, GR5
FROM qryPercent
WHERE Percentage IN
(SELECT Top 2 Percentage
FROM qryPercent As Q2
WHERE Q2.LakeID = qryPercent.LakeID
ORDER BY Percentage Desc);

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello!

I have this query and I would like to make it only give me
the
first
and
the
second highest percentage. I also I need to be able to view
what
LakeID
and
GR5 the percentages are coming from.

I will appreciate it if someone can give a hand. This is my
last
step
in
finalizing my query.

Thank you.

SELECT qrySumArea.LakeID, [sumofarea]/[sumofsumofarea] AS
Expr1,
qrySumArea.GR5
FROM qrySumTotal INNER JOIN qrySumArea ON qrySumTotal.LakeID
=
qrySumArea.LakeID
GROUP BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea],
qrySumArea.GR5
ORDER BY qrySumArea.LakeID, [sumofarea]/[sumofsumofarea]
DESC;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get percentage and sort 2
Sorting 5
percentages problem: what am I doing wrong? 1
Sum means empty query? 5
Percentages, eg. Male v Female 1
Ranking Query For Multiple Groups using percentages 1
Query behaving differently in VBA than when running as regular Que 1
Cannot modify record based on multiple table query 3

Top