Ordering Sums

[DrewKull]

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?

[Bas Cost Budde]

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

[DrewKull]

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 :-/

[DrewKull]

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]

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

[Bas Cost Budde]

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

[DrewKull]

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

[Granny Spitz via AccessMonster.com]

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

[Bas Cost Budde]

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