Sorting on Calculated Field

[Guest]

My report is run on a table that brings in "Downpay" and "Sales" for each
"AgentCD". I want the report to be sorted based on a calculated field that
is a percentage of Count([Downpay])/Count([Sales]). My field list does not
have the column heading for this calculated field so I don't know how to get
the report to sort in order by my calculated field of "%SalesWithDownPay".
I'm pretty new to Access so any help you can give me is greatly appreciated.
Thank you

 

[Scott]

do your calculation in a query and base the report on the query

 

[Ken Snell \(MVP\)]

In the Sorting & Grouping window, type the name of the textbox control that
holds the value on which you want to sort.

 

[Guest]

You can't sort by an aggregate (sum, count, avg,..) that is calculated in
your report. The solution is to create a totals query that groups by AgentCD
like:
SELECT AgentCD, Count([Downpay])/Count([Sales]) as PctSalesWithDownPay
FROM [table that brings]
GROUP BY AgentCD;

Then add this totals query to your report's record source and join the
AgentCD fields. You can then have all the detail records as well as the
Pct... field for sorting and grouping.

 

[Guest]

Thanks Duane, I do have one question for you, I bring my data in with a
select Query, I've never heard of a totals query? The table my current query
is based on is the "AllRecords" table. That is where I'm grouping by
AgentCD. Is this something that can be accomplished with VB code? Thanks
again for your assistance

You can't sort by an aggregate (sum, count, avg,..) that is calculated in
your report. The solution is to create a totals query that groups by AgentCD
like:
SELECT AgentCD, Count([Downpay])/Count([Sales]) as PctSalesWithDownPay
FROM [table that brings]
GROUP BY AgentCD;

Then add this totals query to your report's record source and join the
AgentCD fields. You can then have all the detail records as well as the
Pct... field for sorting and grouping.

--
Duane Hookom
Microsoft Access MVP

My report is run on a table that brings in "Downpay" and "Sales" for each
"AgentCD". I want the report to be sorted based on a calculated field that
is a percentage of Count([Downpay])/Count([Sales]). My field list does not
have the column heading for this calculated field so I don't know how to get
the report to sort in order by my calculated field of "%SalesWithDownPay".
I'm pretty new to Access so any help you can give me is greatly appreciated.
Thank you

 

[Guest]

Totals queries are very common queries where you group by a field or fields
and aggregate other fields. If you have any questions, press F1 and type in
"totals query".

I don't know why you need to use code to create this query. You should be
able to design it like any other query.

--
Duane Hookom
Microsoft Access MVP

Thanks Duane, I do have one question for you, I bring my data in with a
select Query, I've never heard of a totals query? The table my current query
is based on is the "AllRecords" table. That is where I'm grouping by
AgentCD. Is this something that can be accomplished with VB code? Thanks
again for your assistance

You can't sort by an aggregate (sum, count, avg,..) that is calculated in
your report. The solution is to create a totals query that groups by AgentCD
like:
SELECT AgentCD, Count([Downpay])/Count([Sales]) as PctSalesWithDownPay
FROM [table that brings]
GROUP BY AgentCD;

Then add this totals query to your report's record source and join the
AgentCD fields. You can then have all the detail records as well as the
Pct... field for sorting and grouping.

--
Duane Hookom
Microsoft Access MVP

My report is run on a table that brings in "Downpay" and "Sales" for each
"AgentCD". I want the report to be sorted based on a calculated field that
is a percentage of Count([Downpay])/Count([Sales]). My field list does not
have the column heading for this calculated field so I don't know how to get
the report to sort in order by my calculated field of "%SalesWithDownPay".
I'm pretty new to Access so any help you can give me is greatly appreciated.
Thank you