Solved

SQL Statement: CountOfSummaryItem

Posted on 2003-12-05
10
377 Views
Last Modified: 2010-05-01
Hello All,

Is there anyway to ASC, DESC the CountOfSummaryItem in this sql?:

s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor;"

Thanks guys. ADawn
0
Comment
Question by:ADawn
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 5

Expert Comment

by:rajaamirapu
Comment Utility
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY CountOfSummaryItem;"
0
 

Author Comment

by:ADawn
Comment Utility
rajaamirapu

I've tried this. It doesn't work! I receive the error message:

"No value given for one or maore required parameters."
0
 
LVL 1

Expert Comment

by:carpe-diem
Comment Utility
Or
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor, CountOfSummaryItem DESC;"
0
 
LVL 1

Expert Comment

by:carpe-diem
Comment Utility
How are you using this sql statement? Is it part of a data environment for example?
0
 

Author Comment

by:ADawn
Comment Utility
carpe-diem

Same error message:

"No value given for one or maore required parameters."

ADawn
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:rajaamirapu
Comment Utility
Can you give the error code
0
 
LVL 9

Expert Comment

by:p_sie
Comment Utility
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(((Select count(*) FROM tbl_Records_DR)))) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor, CountOfSummaryItem DESC;"

Try adding extra (( )) around the subquery, I know I always use them with subqueries, otherwise errors can occur!
Don't know if this will resolve your problem.
0
 

Accepted Solution

by:
ROTRTechTeam earned 50 total points
Comment Utility
It is really easy, but you need to think about what you are wanting to order by.  You say you want to order by CountOfSummaryItem, but to SQL there is no such column.  That is why the other answers you've gotten won't work.  Remember that those aliases are just for the column title.  In your case you can accomplish what you want as follows:

s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor ORDER BY Count(DAuthor);"

You can add ASC or DESC after the "ORDER BY Count(DAuthor)" to order ascending or descending.


0
 

Author Comment

by:ADawn
Comment Utility
ROTRTechTeam

Thanks for the great information. However, please note that you have two Order BY clauses which caused an error. I removed Order BY DAuthor (keeping: ORDER BY Count(DAuthor)) and the SQL works great.

This comment is for others that may want to use your GREAT works!

Thanks,

ADawn
0
 

Expert Comment

by:ROTRTechTeam
Comment Utility
Whoops!  I copied your SQL and added my order by at the end.  Sorry about that, but thanks for the compliments!  Glad it worked for you!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now