Sort report by calculated value

  • Thread starter Thread starter Chris Moore
  • Start date Start date
C

Chris Moore

Access 2002 on WinXPPro.

The main section of a report I have created is grouped by customer and
contains a list of services provided per customer, the date the service was
provided and the code for the type of service. In the customer header I have
created a text box which contains a count of the number of times a specific
service code was provided for that customer:

=Sum(IIf([Service CD]="12345",1,0))

I would like to sort the customers based on the values in this text box. Is
there anyway to do that? Thanks!

-Chris
 
You cannot sort the records in a report by a value that is not calculated
until the records are already on the report. You will need to get this
calculation into the report's source query if you want to sort on it.

Create another query using the same table.
Depress the Total button (on the toolbar in query design.)
This adds a Total row to the design grid.
In the Total row under CustomerID, choose Group By.
Type this expression into the Field row:
([Service CD]="12345")
In the Total row under that expression, choose Sum
That will give you a field with the negative count.
Save the query.

Now add that query to your existing one as another source 'table.'
Join on CustomerID.
This allows you to output the count for each row.
Since the field is now in the report's source, you can sort on it.
 
Chris,
Yes... you can do the calculation in the report query, and therby create
a "bound" field that can be Sorted and Grouped.
In your query...
YourCalcName : IIF([Service CD] = "12345", 1, 0)
YourCalcName can now be placed on the report... and used for sorting.
And...
=Sum(YourCalcName)
will give you your footer totals.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Great! I understand where you are going with this. Not sure I understand why
the results are negative though. I suppose if I want to sort descending I
will actually have to specify ascending since the numbers are negative values?

Allen Browne said:
You cannot sort the records in a report by a value that is not calculated
until the records are already on the report. You will need to get this
calculation into the report's source query if you want to sort on it.

Create another query using the same table.
Depress the Total button (on the toolbar in query design.)
This adds a Total row to the design grid.
In the Total row under CustomerID, choose Group By.
Type this expression into the Field row:
([Service CD]="12345")
In the Total row under that expression, choose Sum
That will give you a field with the negative count.
Save the query.

Now add that query to your existing one as another source 'table.'
Join on CustomerID.
This allows you to output the count for each row.
Since the field is now in the report's source, you can sort on it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Chris Moore said:
Access 2002 on WinXPPro.

The main section of a report I have created is grouped by customer and
contains a list of services provided per customer, the date the service
was
provided and the code for the type of service. In the customer header I
have
created a text box which contains a count of the number of times a
specific
service code was provided for that customer:

=Sum(IIf([Service CD]="12345",1,0))

I would like to sort the customers based on the values in this text box.
Is
there anyway to do that? Thanks!

-Chris
Click to expand...
Click to expand...
 
Creating the query this way works perfectly. The remaining step seems to be
how to link the two queries together as the record source for the report.
Could you provide a further explaination on that? Thanks!

Al Campagna said:
Chris,
Yes... you can do the calculation in the report query, and therby create
a "bound" field that can be Sorted and Grouped.
In your query...
YourCalcName : IIF([Service CD] = "12345", 1, 0)
YourCalcName can now be placed on the report... and used for sorting.
And...
=Sum(YourCalcName)
will give you your footer totals.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Chris Moore said:
Access 2002 on WinXPPro.

The main section of a report I have created is grouped by customer and
contains a list of services provided per customer, the date the service
was
provided and the code for the type of service. In the customer header I
have
created a text box which contains a count of the number of times a
specific
service code was provided for that customer:

=Sum(IIf([Service CD]="12345",1,0))

I would like to sort the customers based on the values in this text box.
Is
there anyway to do that? Thanks!

-Chris
Click to expand...
Click to expand...
 
The expression is True or False (or null.)
In Access, True is -1, and False is 0.
Therefore summing the expression gives the negative count.
If you want to sort it out, change the SQL to:
-Sum([Service CD]="12345") AS SomeCount

Ultimately it doesn't matter whether it's positive or negative: you just
swap the sorting (ascending verses descending.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Chris Moore said:
Great! I understand where you are going with this. Not sure I understand
why
the results are negative though. I suppose if I want to sort descending I
will actually have to specify ascending since the numbers are negative
values?

Allen Browne said:
You cannot sort the records in a report by a value that is not calculated
until the records are already on the report. You will need to get this
calculation into the report's source query if you want to sort on it.

Create another query using the same table.
Depress the Total button (on the toolbar in query design.)
This adds a Total row to the design grid.
In the Total row under CustomerID, choose Group By.
Type this expression into the Field row:
([Service CD]="12345")
In the Total row under that expression, choose Sum
That will give you a field with the negative count.
Save the query.

Now add that query to your existing one as another source 'table.'
Join on CustomerID.
This allows you to output the count for each row.
Since the field is now in the report's source, you can sort on it.

Chris Moore said:
Access 2002 on WinXPPro.

The main section of a report I have created is grouped by customer and
contains a list of services provided per customer, the date the service
was
provided and the code for the type of service. In the customer header I
have
created a text box which contains a count of the number of times a
specific
service code was provided for that customer:

=Sum(IIf([Service CD]="12345",1,0))

I would like to sort the customers based on the values in this text
box.
Is
there anyway to do that? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the input, I now understand your posts and have all the
functionality working.

-Chris
 
Back
Top