Sorting and Grouping issue

[Guest]

Hey group. I'm sure this is an easy one but for some reason it's killing me.

I have a report that shows individual records of employees at various
companies along with various field data on those employees. The report then
groups by each "FirmID" and then sums where appropriate.

My problem is that my internal client has requested that I sort on one of
the grouped values - let's call it "Total Sales" for demonstration purposes.
Right now I have set up the Sorting and Grouping to be:

FirmID - header/footer=yes

However, when I add in the "Total Sales" field to the Sorting and Grouping
dialog box it sorts by that field, but it also breaks apart my "FirmID" group
so now there are multiple records for each firm. It's driving me nuts.

Is there a way to group on one value - FirmID in this case - while sorting
by an aggregate which is calculated on the same report? In essence, I want to
sort on the "Total Sales" value which is only determined by a field on the
report.

I've tried referencing the field name as "[Text30]" (w/o quotes) or
"=[Text30]" or even "Text30" but it jsut asks me for the value when I run the
report.

Any thoughts?

txs!!!
- BD

 

[Duane Hookom]

Create a totals query that calculates your value you want to sort on. Add
this query to your report's record source query and join the appropriate
fields. You can then use SumOfSales and FirmID to sort and group your
report.

 

[Guest]

Awesome. That totally did the trick and I can't believe I didn't think of
that myself! hahah

I actually already had a firm-level value being stored at the individual
level and was displaying it on the report at the firm level by simply not
summing it in the FirmID header. I was about one step away from your solution
but just couldn't get it.

Thanks again. Saved me from another few hours of trial/error.

txs!
- S

Create a totals query that calculates your value you want to sort on. Add
this query to your report's record source query and join the appropriate
fields. You can then use SumOfSales and FirmID to sort and group your
report.

--
Duane Hookom
MS Access MVP

Hey group. I'm sure this is an easy one but for some reason it's killing
me.

I have a report that shows individual records of employees at various
companies along with various field data on those employees. The report
then
groups by each "FirmID" and then sums where appropriate.

My problem is that my internal client has requested that I sort on one of
the grouped values - let's call it "Total Sales" for demonstration
purposes.
Right now I have set up the Sorting and Grouping to be:

FirmID - header/footer=yes

However, when I add in the "Total Sales" field to the Sorting and Grouping
dialog box it sorts by that field, but it also breaks apart my "FirmID"
group
so now there are multiple records for each firm. It's driving me nuts.

Is there a way to group on one value - FirmID in this case - while sorting
by an aggregate which is calculated on the same report? In essence, I want
to
sort on the "Total Sales" value which is only determined by a field on the
report.

I've tried referencing the field name as "[Text30]" (w/o quotes) or
"=[Text30]" or even "Text30" but it jsut asks me for the value when I run
the
report.

Any thoughts?

txs!!!
- BD