 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005Grouping/Sorting Report
I know many people ask this but I still am having trouble understanding how I can do what I need to do. Basically, it comes down to the idea of grouping by one field and sorting by another. I have tblClients and tblCommissions. There is a one-to-many relationship between tblClients.intClientID and tblCommissions.intClientID. I have two basic fields that I want to have shown on the report, tblClients.strName and a sum of tblCommissions.curAmount. I need to be able to specify a date range for this report also (tblCommissions.datPeriod). So, I have a query that pulls the desired information. Fields selected are tblClient.strName (Group By), curAmount (Sum), and datPeriod (Group By). The results returned are what I would expect. Here's an example of the results: intClientID strName SumOfcurAmount datPeriod 33 Test Client A $1,478.98 09-01-2005 35 Test Client C $1,250.00 11-01-2005 35 Test Client C $892.15 08-01-2005 34 Test Client B $766.43 08-01-2005 33 Test Client A $476.82 08-01-2005 However, when I put this query as the record source of a report I can not get the results I need. I can either have the report grouped/sorted by strName or grouped/sorted by SumOfcurAmount. What I really need is it to be grouped by strName and then sorted by SumOfcurAmount so that we can see the total commissions for each client (sorted by SumOfcurAmount DESC) based on a specified range (which I will do with a Form). Can anyone explain how I can do this? You showed us maybe what your current results are but you didn't show us how you would like those records sorted in your report and why? You should be able to set your sorting and grouping to: strName Ascending SumOfcurAmount Descending  SignatureDuane Hookom MS Access MVP -- >I know many people ask this but I still am having trouble understanding > how I can do what I need to do. Basically, it comes down to the idea [quoted text clipped - 30 lines] > > Can anyone explain how I can do this? Sorry, I would like the output of the report to list the results of the aforementioned query as follows. This is if I were to not specify a range for datPeriod. Test Client C $2142.15 Test Client A $1955.80 Test Client B $766.43 BTW, I currently have my report constructed as follows. The two fields are placed in the strName Header. They are set to strName and =Sum(curSumOfcurAmount). Am I approaching this wrong or is there some way to get the report to sort by Sum(SumOfcurAmount)? This would all work fine and dandy if I didn't have the datPeriod field in the query. I could do the grouping/summing in the query and sort it in the report. However, I need the datPeriod field in there to be able to specify a date range for the report. You can use Where in your totals query and not have it affect your grouping. Uncheck the Show box in your query. SignatureDuane Hookom MS Access MVP -- > This would all work fine and dandy if I didn't have the datPeriod field > in the query. I could do the grouping/summing in the query and sort it > in the report. However, I need the datPeriod field in there to be able > to specify a date range for the report. Thanks for the help. I'll post back when I get it working. I have set my query to the following SQL: SELECT tblClients.intClientID, tblClients.strName, Sum(tblCommissions.curAmount) AS SumOfcurAmount FROM tblClients LEFT JOIN tblCommissions ON tblClients.intClientID = tblCommissions.intClientID WHERE (((tblCommissions.datPeriod)<>0)) GROUP BY tblClients.intClientID, tblClients.strName ORDER BY Sum(tblCommissions.curAmount) DESC; When I run the report with this command: strWhereCond = "([datPeriod]=#11/01/2005#)" DoCmd.OpenReport stDocName, ViewMode, , strWhereCond I get the "Enter Parameter Value" input box asking me what datPeriod is. I'm assuming this is happening because datPeriod is not in the SELECT portion of the SQL statement. However, if I put it in the SELECT portion of the statement, I have to put it in the GROUP BY portion of the statement, and I don't want to do that. Is there any way around that? You need set the criteria in the query using a reference to a control on a form or a parameter prompt. SELECT tblClients.intClientID, tblClients.strName, Sum(tblCommissions.curAmount) AS SumOfcurAmount FROM tblClients LEFT JOIN tblCommissions ON tblClients.intClientID = tblCommissions.intClientID WHERE tblCommissions.datPeriod = Forms!frmYourForm!cboDatPeriod GROUP BY tblClients.intClientID, tblClients.strName ORDER BY Sum(tblCommissions.curAmount) DESC; SignatureDuane Hookom MS Access MVP -- >I have set my query to the following SQL: > [quoted text clipped - 19 lines] > > Is there any way around that? Ok, I figured I could do that but it would be easier if I could do it with the OpenReport statement. Oh well. Thanks for the help, Duane. Have a great day! Just to let you know, since I wanted to do a date range I had to have a txtPeriodStart and txtPeriodEnd on my form. My final SQL statement was: PARAMETERS [Forms]![frmPrintReports]![txtPeriodStart] DateTime, [Forms]![frmPrintReports]![txtPeriodEnd] DateTime; SELECT tblClients.intClientID, tblClients.strName, Sum(tblCommissions.curAmount) AS SumOfcurAmount FROM tblClients LEFT JOIN tblCommissions ON tblClients.intClientID = tblCommissions.intClientID WHERE (((tblCommissions.datPeriod) Between [Forms]![frmPrintReports]![txtPeriodStart] And [Forms]![frmPrintReports]![txtPeriodEnd])) GROUP BY tblClients.intClientID, tblClients.strName ORDER BY Sum(tblCommissions.curAmount) DESC; Thanks very much for the help! |
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.