Grouping/Sorting Report

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

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
 
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.
 
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;
 
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!
 
Back
Top