By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Ordering Sums

P: n/a
Hello, I have a query which sums check boxes (so 0 or -1) then Abs the
sum so i can get the number of check box 'checked' per column... based
on start and end date.

SELECT Abs(Sum(CommCon)) AS [Communication Content Error],
Abs(Sum(PerfWor)) AS [Performance Seems Worse], Abs(Sum(AppPCWor)) AS
[Application or PC not Working], Abs(Sum(PrinPeriph)) AS [Printer or
Peripheral Problems], Abs(Sum(VPNWor)) AS [Wireless,VPN not Working],
Abs(Sum([MissApp])) AS [Missing Application], Abs(Sum(SerRes)) AS [Does
not like Service Restriction], Abs(Sum(SWAT)) AS [SWAT Problem],
Abs(Sum(CommRec)) AS [Comm Not Received], Abs(Sum(MissDat)) AS [Missing
Data], Abs(Sum(SupProb)) AS [Support Problem], Abs(Sum(MissSite)) AS
[Missing Site], Abs(Sum(Reboot)) AS [Unhappy about Reboot Intrpts],
Abs(Sum(Font)) AS [Font/Settings], Abs(Sum(PDA)) AS [PDA Problem],
Abs(Sum(Misc)) AS Miscs
FROM [Input] INNER JOIN DSAT ON Input.[Input ID] = DSAT.[Input ID]
WHERE (((Input.[Start Time])>=[Enter Start Date (mm/dd/yyyy)] And
(Input.[Start Time])<=[Enter End Date (mm/dd/yyyy)]));
My question is there a way to order the sums so that the highest number
is on the top (or left) and the lower is underneath ...
Does that make sense? I attempted to use Group By, but doesnt work,
how would i implement order by?

Sep 18 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Save your query, create a new query based on it and ORDER BY that one.

You can safely do -Sum() instead of Abs(Sum), that may give a tiny
performance increase :)

Dr******@gmail.com schreef:
Hello, I have a query which sums check boxes (so 0 or -1) then Abs the
sum so i can get the number of check box 'checked' per column... based
on start and end date.

My question is there a way to order the sums so that the highest number
is on the top (or left) and the lower is underneath ...

Does that make sense? I attempted to use Group By, but doesnt work,
how would i implement order by?
--
Bas Cost Budde
Holland
Sep 18 '06 #2

P: n/a

Bas Cost Budde wrote:
Save your query, create a new query based on it and ORDER BY that one.
I'm still a bit confused on how to do this ... I have the query saved
.... but i can't 'order by' a "*" , so how do i get all the columns in
order???

Sorry if this is an easy fix :-/

Sep 18 '06 #3

P: n/a
Here's another question:

This output of the query gives me each number in a row ... doesn't
'group by' apply to columns?? can i even sory a row of data?

So my output it each Sum() has a column and produces on one row with
all the numbers. Can this be done?

If not, suggest a new way to do the calculations?!

Dr******@gmail.com wrote:
Bas Cost Budde wrote:
Save your query, create a new query based on it and ORDER BY that one.

I'm still a bit confused on how to do this ... I have the query saved
... but i can't 'order by' a "*" , so how do i get all the columns in
order???

Sorry if this is an easy fix :-/
Sep 18 '06 #4

P: n/a
I seem to have overlooked a feature of your question. You have several
sums in one row, and want to sort the *columns*. That cannot be done
with SQL.

You can, however, produce a large UNION of the sums, much like this:

first create a helpquery that puts your FROM and WHERE at bay:
[helpquery]
SELECT * FROM [Input] INNER JOIN DSAT ON Input.[Input ID] = DSAT.[Input ID]
WHERE (((Input.[Start Time])>=[Enter Start Date (mm/dd/yyyy)] And
(Input.[Start Time])<=[Enter End Date (mm/dd/yyyy)]))

then have a large set of UNIONS:

SELECT Abs(Sum(CommCon)), "Communication Content Error" FROM helpquery
UNION SELECT Abs(Sum(PerfWor)), "Performance Seems Worse" FROM helpquery
UNION SELECT Abs(Sum(AppPCWor)), "Application or PC not Working" FROM
helpquery
UNION SELECT Abs(Sum(PrinPeriph)), "Printer or Peripheral Problems" FROM
helpquery

and so on; in the end you can do
ORDER BY 1
meaning the first column. The second column will show your value label.

Dr******@gmail.com schreef:
Here's another question:

This output of the query gives me each number in a row ... doesn't
'group by' apply to columns?? can i even sory a row of data?

So my output it each Sum() has a column and produces on one row with
all the numbers. Can this be done?

If not, suggest a new way to do the calculations?!

Dr******@gmail.com wrote:
>Bas Cost Budde wrote:
>>Save your query, create a new query based on it and ORDER BY that one.
I'm still a bit confused on how to do this ... I have the query saved
... but i can't 'order by' a "*" , so how do i get all the columns in
order???

Sorry if this is an easy fix :-/
--
Bas Cost Budde
Holland
Sep 18 '06 #5

P: n/a
As a matter of fact, I have studied ordering fields by row values
before. See my site, under Code Modules/News Questions, the question
'sort record fields'.

http://www.heuveltop.BasCB/msac_index.html
--
Bas Cost Budde
Holland
Sep 18 '06 #6

P: n/a
I will have to give that a try!!! Thanks soo much!!

Also: *** the link you provided is broken *** Please repost, as i am
very interested in your studies!!

Thanks again!

Bas Cost Budde wrote:
As a matter of fact, I have studied ordering fields by row values
before. See my site, under Code Modules/News Questions, the question
'sort record fields'.

http://www.heuveltop.BasCB/msac_index.html
--
Bas Cost Budde
Holland
Sep 18 '06 #7

P: n/a
Dr******@gmail.com wrote:
Also: *** the link you provided is broken *** Please repost, as i am
very interested in your studies!!
Try http://www.heuveltop.nl/BasCB/msac_index.html

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200609/1

Sep 19 '06 #8

P: n/a
Indeed. <scratchhow did it arrive at lowercase?

Granny Spitz via AccessMonster.com schreef:
Dr******@gmail.com wrote:
>Also: *** the link you provided is broken *** Please repost, as i am
very interested in your studies!!

Try http://www.heuveltop.nl/BasCB/msac_index.html
--
Bas Cost Budde
Holland
Sep 19 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.