429,262 Members | 2,664 Online
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
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 inorder???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. Also: *** the link you provided is broken *** Please repost, as i amvery 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.