🔒 Locked

Need to optimize an SQL query

Started by Crispy Mar 18, 2003 at 7:08 AM 6 replies 200+ views

Original Post

Mar 18, 2003 at 7:08 AM

The situation is as follows: I have a table that contains all entrties for a given Type each of which has a YearOfInstallation value and a couple of properties I need to use to classify them by, like this:

Length [other properties] YearOfInstallation
...    ...                ...
1300   ...                1982
815    ...                1991
1900   ...                1993
1600   ...                2002
...    ...                ...

I need to construct a query in MS Access that creates a number of groups from the data in the table:

Group                SumOfLength
...                  ...
1980-1990            1300
1991-2000            2715
2001-2003            1600
...                  ...

The easiest way to do this is by creating a separate query for each of the groups, but it seems awfully inefficient. I can't figure out if it's impossible to create such a query (since adding several where-conditions into the SQL statement in different places is illegal) or I have to use some advanced techniques. Access wizard fails hopelessly in this case. Additionally - it is not possible to create automated parametrized queries for reports (which is the case). I'd appreciate some help (running on a deadline - god I hate clients that make up stuff on the go...). Crispy EDIT: formatting [edited by - crispy on March 18, 2003 8:11:12 AM]

Crispy

Mar 18, 2003 at 9:03 AM

What that does is that it groups the attributes by Year/10, e g, the result is somenthing like:

199,9 1300
198,4 12342

etc.

Besides, the groups aren''t always 10 years in size. I''m actually given periods, such as 0-5, 6-10, 10-20 etc years old. In fact I believe I stated my question wrong in the original post

. Damn.
Thanks for the reply, though - any other ideas?

Crispy

afterburn

Mar 18, 2003 at 9:19 AM

Here is an easy way to do that.

Add another field to have the group inside of it.

Then use an update query.

update tblData set GroupID = GroupID from
tblData cross join tblGroups where
YearOfInstallation between group.startyear and group.endyear
and GroupID is null

You may have to use table names in-front of the fields because of ambiguous names.

Crispy

Mar 18, 2003 at 1:33 PM

afterburn: this won''t do for a very simple reason: the groups are dynamic - the objects move from one group to the next once they became a certain number of years of age. What you''re suggesting would require updating the group numbers every year - that''s something I can''t rely on.

However, I decided it would be easier to run a bunch of Dsum''s in VBA instead and fill in the 25 or so fields one by one instead.

Thanks for the replies, though!

Crispy

Crispy

Mar 18, 2003 at 2:05 PM

Teehee - I remember doing something like this once - Access choked on it real bad. It''s (Access) rather useless when datasets and queries become complex and/or large. I''m not sure, but I''m relatively confident it isn''t the underlying SQL database that poses a 65k character query length limit (although you can fit a world in that) - it has a lot of other limitations and errors to produce...

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.