471,850 Members | 1,004 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,850 software developers and data experts.

Sorting multiple fields in queries

I am so aggravated and need any help I can get. I am using Access 2000
and am trying to sort multiple fields in a select query.

My query is set up as follows:
FIELD 1 FIELD 2 FIELD 3 FIELD4 FIELD 5 FIELD 6
MSA03 MSA 02 % change ICU 03 ICU 02 % change
calc. calc.

This format continues and in some queries I have up to 13 percentages
to sort. I need to sort each percentage to show over 20% increase and
have only those records with an increase show.

I've tried using >=.2 as the criteria in each percentage field, but
that doesn't give me what I need.

(I know this looks like it should have been done in Excel, but for
reporting/printing purposes it had to be done in Access.)

Any help is greatly appreciated.
Thank you

Nov 13 '05 #1
3 3297
Tesc,
Behind the scenes the QBE grid is writing an SQL statement for you. To
define the sort order of a query it uses (and you can use) an ORDER BY
clause in the SELECT statement. If you want to limit the rows returned in a
result to only those that meet a set of criteria, then you would include a
WHERE clause.
So, for example, "SELECT [FIELD 1], [FIELD 2], [FIELD 3], [FIELD4], [FIELD
5], [FIELD 6] FROM TABLE1 WHERE [FIELD 1] >=0.2 OR [FIELD 2] >= 0.2 OR
[FIELD 3] >=0.2 OR [FIELD 4] >=0.2 OR [FIELD 5] >=0.2 OR [FIELD 6] >=0.2
ORDER BY [FIELD 1].

And a gripe--Naming your fields [FIELD 1], [FIELD 2], etc. is lazy & rude.
Hopefully no one else has to use the table set up this way. Hopefully the
table won't be in service long enough for you to forget what each field
contains. But bitter experience by many database administrators has taught
that failing to make the effort to name fields something sensible begets
hours of hair pulling and teeth gnashing by the users and administrators of
such a database. Make the effort to name your fields.

<te**@optonline.net> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I am so aggravated and need any help I can get. I am using Access 2000
and am trying to sort multiple fields in a select query.

My query is set up as follows:
FIELD 1 FIELD 2 FIELD 3 FIELD4 FIELD 5 FIELD 6
MSA03 MSA 02 % change ICU 03 ICU 02 % change
calc. calc.

This format continues and in some queries I have up to 13 percentages
to sort. I need to sort each percentage to show over 20% increase and
have only those records with an increase show.

I've tried using >=.2 as the criteria in each percentage field, but
that doesn't give me what I need.

(I know this looks like it should have been done in Excel, but for
reporting/printing purposes it had to be done in Access.)

Any help is greatly appreciated.
Thank you

Nov 13 '05 #2
Thank you for your response and the spanking. My fields are named
properly. I listed field names and numbers for illustrative purposes
only.

Nov 13 '05 #3
Thank you for your response. My fields are named properly, MSA 03, MSA
02, MSAPCT and so on. I included the field numbers for illustrative
purposes only.

Thanks again.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Paul Kirby | last post: by
reply views Thread by MHenry | last post: by
2 posts views Thread by Tarvos{k} | last post: by
11 posts views Thread by dskillingstad | last post: by
8 posts views Thread by Chris A via AccessMonster.com | last post: by
reply views Thread by YellowAndGreen | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.