Expression builder and SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Expression builder and SQL

Author

Topic

GIS_Toronto
Starting Member

2 Posts

Posted - 2004-01-28 : 01:07:20

Hi there. I'm still trying to learn MS Access and so I hope this question isn't too trivial. I use mainly a GIS DBMS at my work. Anyways here's my problem.

I have a table that contains census information for Toronto. The table contains census tracts (areas of land) from 0 to 900+. The table also contains a field (column)that displays the change of population from one census year to another for each census tract. What I need to do is generate a query that will take a look at a range of the census tracts (i.e. 0-99, 100-199, 200-299) while also calculating the average for each range from the change in population (POPCHANGE) field. I don't need to do a query of all the ranges at once.

I got this simple SQL to look at defined ranges:

SELECT [Toronto_ct].[CT_short]
FROM Toronto_ct
WHERE ((([Toronto_ct].[CT_short]) Between "0100.00" And "0199.99"));

but I don't know how to add an AVG function.

I'd appreciate any help I can get. I just started my job here and I'm afraid of asking a question like this 'cuz it seems so simple that I should know it.

Thanks!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-28 : 07:33:29

SELECT "0100.00 - 0199.99" as ThisRange,

avg(POPCHANGE) as [Avg_POPCHANGE_forThisRange]

FROM Toronto_ct

WHERE [Toronto_ct].[CT_short] Between "0100.00" And "0199.99";

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-28 : 07:50:29

or, for all ranges by one select statement:

SELECT min([CT_short]) & " - " & max([CT_short]) as TheRange,

avg(POPCHANGE) as [Avg_POPCHANGE_forTheRange]

FROM Toronto_ct

~~WHERE [Toronto_ct].[CT_short] Between "0100.00" And "0199.99"~~

GROUP BY int(csng([CT_short])/100)

GIS_Toronto
Starting Member

2 Posts

Posted - 2004-01-28 : 12:54:28

Thank you so much STOAD!!

I can't even begin to say how grateful I am.

I'm planning on buying a book on ACCESS to help me for future reference. Any recommendations? I'd like something that has some decent SQL info as well.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-28 : 14:06:42

Very glad to hear it, GISsy.
As to books.. not sure what books I could recommend,
in fact I have nothing to say on this point. Some time
ago I had a book "Access 2000" by Viescas and I liked
it. Hm... in short, Access help (F1) is quite enough for
me + (tons) of experimenting in "self-instructive" mode.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources