 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Classified by segment
There’s a table, with the format: Table1:price,with only one column. I want to use query to Make it output as follows: 1. Price in (0,50k) Price in (50k,1m) Price in (1m,5m) Price >5M count Or 2. count Price in (0,50k) Price in (50k,1m) Price in (1m,5m) Price >5M How to arrange query?pls give it in sql. Many thanks. I fail to see how your initial data is in the table. You probably over simplified your problem when you say that table1 has ONLY one column. Vanderghast, Access MVP > There's a table, with the format: > Table1:price,with only one column. [quoted text clipped - 11 lines] > How to arrange query?pls give it in sql. > Many thanks. SELECT Abs(Sum(Price >0 and <=50000)) as Count0_50 , Abs(Sum(Price >50000 and <=10000000)) as Count50_1M , Abs(Sum(Price >1000000 and <=5000000)) as Count1_5M , Abs(Sum(Price >5000000)) as CountOver5M FROM Table1 '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== > There’s a table, with the format: > Table1:price,with only one column. [quoted text clipped - 11 lines] > How to arrange query?pls give it in sql. > Many thanks. Dear John, As trying your way,I write the following: SELECT ABS(SUM(QUERY4.[End of Day Bal LCY]>0 AND QUERY4.[End of Day Bal LCY]<=100000) )AS SUM010K, ABS(SUM(QUERY4.[End of Day Bal LCY]>100000 AND QUERY4.[End of Day Bal LCY]<=300000) )AS SUM10K30K, ABS(SUM(QUERY4.[End of Day Bal LCY]>300000) ) AS SUMOVER30K, FROM QUERY4; But while try to run it return with error, within the sentence ,where is wrong? Thanks Dawn > SELECT Abs(Sum(Price >0 and <=50000)) as Count0_50 > , Abs(Sum(Price >50000 and <=10000000)) as Count50_1M [quoted text clipped - 24 lines] > > How to arrange query?pls give it in sql. > > Many thanks. Looks as if you have an extra comma at the end of the SELECT clause. SELECT ABS(SUM([End of Day Bal LCY]>0 AND [End of Day Bal LCY]<=100000) )AS SUM010K, ABS(SUM([End of Day Bal LCY]>100000 AND [End of Day Bal LCY]<=300000) )AS SUM10K30K, ABS(SUM([End of Day Bal LCY]>300000) ) AS SUMOVER30K FROM QUERY4; John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Dear John, > As trying your way,I write the following: [quoted text clipped - 36 lines] >>> How to arrange query?pls give it in sql. >>> Many thanks. Dear John, It works , thanks. And what if I want both “the sum of balance “ ,”the count “ two fields under the classification ,for example [0,30k],(30k,+∞),how to write the sql? Many thanks. SELECT ABS(SUM([End of Day Bal LCY]>0 AND [End of Day Bal LCY]<=100000) )AS Count010K . SUM(IIF([End of Day Bal LCY]>0 AND [End of Day Bal LCY]<=100000,[End of Day Bal LCY],0)) as Total010K , ABS(SUM([End of Day Bal LCY]>100000 AND [End of Day Bal LCY]<=300000) )AS Count10K30K , SUM(IIF([End of Day Bal LCY]>100000 AND [End of Day Bal LCY]<=300000,[End of Day Bal LCY],0) as Total10K30K , ABS(SUM([End of Day Bal LCY]>300000) ) AS CountOVER30K , SUM([End of Day Bal LCY]>300000,[End of Day Bal LCY],0) as TotalOVER30K FROM QUERY4; John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > Dear John, > It works , thanks. > And what if I want both “the sum of balance “ ,”the count “ two fields under > the classification ,for example [0,30k],(30k,+∞),how to write the sql? > Many thanks. Whoops, there is a period that should be a comma and at least one missing closing parentheses. Hopefully this one is syntactically correct. SELECT ABS(SUM([End of Day Bal LCY]>0 AND [End of Day Bal LCY]<=100000) )AS Count010K , SUM(IIF([End of Day Bal LCY]>0 AND [End of Day Bal LCY]<=100000,[End of Day Bal LCY],0)) as Total010K , ABS(SUM([End of Day Bal LCY]>100000 AND [End of Day Bal LCY]<=300000) )AS Count10K30K , SUM(IIF([End of Day Bal LCY]>100000 AND [End of Day Bal LCY]<=300000,[End of Day Bal LCY],0)) as Total10K30K , ABS(SUM([End of Day Bal LCY]>300000) ) AS CountOVER30K , SUM([End of Day Bal LCY]>300000,[End of Day Bal LCY],0) as TotalOVER30K FROM QUERY4; John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County SNIP out erroneous SQL statement >> Dear John, >> It works , thanks. >> And what if I want both “the sum of balance “ ,”the count “ two fields >> under the classification ,for example [0,30k],(30k,+∞),how to write >> the sql? >> Many thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.