Sorting

[Yubasus]

Hello,

I am hopping someone will help me with this.

I have three columns in my query LakeID, GR5, AREA. The LakeID is a number
that identifies a lake. Within that lake there are (GR5) 5 levels of chemical
substances that can be found going from 0 to 5. The AREA is identified as the
amount of total area that a certain chemical can be found in.

What I would like to do is query the data so that it gives me the GR5
rating going from the highest number of area that a GR5 covers to the lowest.

Also, I want the query to show what the percentage for each GR5 is of the
total area of the lake.

This is what I have so far.

SELECT adamhdwtrgeo.LAKE_ID, Sum(adamhdwtrgeo.AREA) AS SumOfAREA,
adamhdwtrgeo.GR5
FROM adamhdwtrgeo
GROUP BY adamhdwtrgeo.LAKE_ID, adamhdwtrgeo.GR5
ORDER BY adamhdwtrgeo.LAKE_ID, Sum(adamhdwtrgeo.AREA);


This is what was suggested.

SELECT a.id, a.gr5
SUM(a.x),
SUM(a.x) / (SELECT SUM(b.x)
FROM myTable As b
WHERE b.id=a.id )

FROM myTable As a

GROUP BY a.id, a.gr5
ORDER BY a.id, SUM(a.x)


When I try it gives me this error message:
Syntax error (missing operator) in query expression 'a.gr5 SUM (a.x)'.


Hope someone can help me figure out what is wrong.

 

[Tom Ellison]

Dear Yubasus:

You need commas between columns. There is one missing at the end of
the first line.

I've seen something like this. I suggest you try something simple.
Put an additional set of parentheses around the subquery.

I have suspected there is a problem with the parser in that it may be
discarding the parens around a subquery and not then using it as a
single quantity in an arithmetic expression.

I'm not convinced this is your problem, but it has worked for me on
occasion.

Another hint is to name the calculated columns. So, what I end up
with is something like:

SELECT a.id, a.gr5,
SUM(a.x) AS MySum,
SUM(a.x) / ((SELECT SUM(b.x)
FROM myTable As b
WHERE b.id=a.id)) AS MyRatio
FROM myTable As a
GROUP BY a.id, a.gr5
ORDER BY a.id, SUM(a.x)

I have seen places where one unnamed column is not an error, but two
are.

Some of this is in my "murky area" of so called expertise. It would
be useful to know which suggestion(s) may have been necessary to solve
the problem and which not. Please get back with any information you
glean from this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Marshall Barton]

I have three columns in my query LakeID, GR5, AREA. The LakeID is a number
that identifies a lake. Within that lake there are (GR5) 5 levels of chemical
substances that can be found going from 0 to 5. The AREA is identified as the
amount of total area that a certain chemical can be found in.

What I would like to do is query the data so that it gives me the GR5
rating going from the highest number of area that a GR5 covers to the lowest.

Also, I want the query to show what the percentage for each GR5 is of the
total area of the lake.

This is what I have so far.

SELECT adamhdwtrgeo.LAKE_ID, Sum(adamhdwtrgeo.AREA) AS SumOfAREA,
adamhdwtrgeo.GR5
FROM adamhdwtrgeo
GROUP BY adamhdwtrgeo.LAKE_ID, adamhdwtrgeo.GR5
ORDER BY adamhdwtrgeo.LAKE_ID, Sum(adamhdwtrgeo.AREA);


This is what was suggested.

SELECT a.id, a.gr5
SUM(a.x),
SUM(a.x) / (SELECT SUM(b.x)
FROM myTable As b
WHERE b.id=a.id )

FROM myTable As a

GROUP BY a.id, a.gr5
ORDER BY a.id, SUM(a.x)


When I try it gives me this error message:
Syntax error (missing operator) in query expression 'a.gr5 SUM (a.x)'.


Hope someone can help me figure out what is wrong.

You left out the comma after a.gr5

But somewhere along the line you should probably have a
lakes total area in the table instead of calculating the sum
of areas for each gr.

 

[Yubasus]

Hi Tom,

Thanks for taking the time. After replacing the myTable statement with
(damhdwtrgeo) this is the table were I am getting the data, the query worked.
Now when I run the query it comes up with a parameter input box and it asks
me to of course enter a parameter value. It first asks Enter Parameter b.x
and then a.x. If I for example enter 1 and 1 it comes up with the MySum as
equal to 1 an MyRatio as 1.

I was hoping that I get the sum of the area in the MySum column and the
percentage of the GR_5 rating in the MyRatio column.

Or maybe I am not understanding the parameter input part of the query.

 

[Tom Ellison]

Dear Yubasus:

Is there a column 'x' in this table? It would seem not, since it is
prompting you for that. Please post the SQL of this query as you have
it now.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Yubasus]

SELECT a.ID, a.LAKE_ID, a.GR5, Sum(a.x) AS MySum, Sum(a.x)/((SELECT SUM(b.x)
FROM adamhdwtrgeo As b
WHERE b.id=a.id)) AS MyRatio
FROM adamhdwtrgeo AS a
GROUP BY a.ID, a.LAKE_ID, a.GR5
ORDER BY a.ID, Sum(a.x);