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;
