Sort report by calculated value

[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

 

[Allen Browne]

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.

 

[Al Campagna]

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]

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?

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.

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

 

[Chris Moore]

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!

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."

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

 

[Allen Browne]

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.

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?

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.

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 Moore]

Thanks for the input, I now understand your posts and have all the
functionality working.

-Chris