By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

sum() in report footer destroys detail order

P: n/a
I have a report based on:

PARAMETERS Forms!frmProductions!ProdID Long;
SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS
LastName, First(qryMusician.FirstName) AS FirstName
FROM (qryEvent INNER JOIN qryRoleEvent ON qryEvent.EventID =
qryRoleEvent.EventID) INNER JOIN qryMusician ON qryRoleEvent.MusoID =
qryMusician.MusoID
WHERE (((qryEvent.ProdID)=[Forms]![frmProductions]![ProdID]))
GROUP BY qryMusician.MusoID
ORDER BY First(qryMusician.LastName), First(qryMusician.FirstName);

It's the total each musician gets paid for a series of concerts which are
part of the production.

If I have text control in the report footer, =Sum([SumOfFee]), (i.e. total
budget for that production), I lose the sorting in the detail section. The
detail is a list of the musicians and their total fee.

If I move the text control into the page footer no problem.

Anybody got any idea why?

I can correct it with sorting and grouping for the detail. But I'd like to
know why.

Cheers, Mike
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Mike MacSween wrote:
I have a report based on:

PARAMETERS Forms!frmProductions!ProdID Long;
SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS
LastName, First(qryMusician.FirstName) AS FirstName
FROM (qryEvent INNER JOIN qryRoleEvent ON qryEvent.EventID =
qryRoleEvent.EventID) INNER JOIN qryMusician ON qryRoleEvent.MusoID =
qryMusician.MusoID
WHERE (((qryEvent.ProdID)=[Forms]![frmProductions]![ProdID]))
GROUP BY qryMusician.MusoID
ORDER BY First(qryMusician.LastName), First(qryMusician.FirstName);

It's the total each musician gets paid for a series of concerts which are
part of the production.

If I have text control in the report footer, =Sum([SumOfFee]), (i.e. total
budget for that production), I lose the sorting in the detail section. The
detail is a list of the musicians and their total fee.

If I move the text control into the page footer no problem.

Anybody got any idea why?

I can correct it with sorting and grouping for the detail. But I'd like to
know why.

Cheers, Mike

I don't know if this addresses your question. The ORDER BY is not
needed in the query. You set the sort order in a report from the menu
(View/Sorting And Grouping). It's the only way I know to ensure you get
a correctly sorted report.

Nov 12 '05 #2

P: n/a
"Salad" <oi*@vinegar.com> wrote in message
news:SL*******************@newsread1.news.pas.eart hlink.net...
Mike MacSween wrote: I don't know if this addresses your question. The ORDER BY is not
needed in the query. You set the sort order in a report from the menu
(View/Sorting And Grouping). It's the only way I know to ensure you get
a correctly sorted report.


Is that the generally accepted method then? Use sorting in the report and
don't bother trying to sort in the query?

Mike
Nov 12 '05 #3

P: n/a
Mike MacSween wrote:
"Salad" <oi*@vinegar.com> wrote in message
news:SL*******************@newsread1.news.pas.eart hlink.net...
Mike MacSween wrote:


I don't know if this addresses your question. The ORDER BY is not
needed in the query. You set the sort order in a report from the menu
(View/Sorting And Grouping). It's the only way I know to ensure you get
a correctly sorted report.

Is that the generally accepted method then? Use sorting in the report and
don't bother trying to sort in the query?

Mike

Yes. If you have been getting any kind of a sort in past reports without
using the sorting and grouping from the menu...or when you set it up via
the report wizard...consider yourself lucky. I have used a query that
is sorted that displayed in descending order when the query is in
ascending order. Without using the Sorting & Grouping menu option you
really have no control on the order.

Nov 12 '05 #4

P: n/a
It actually does some of those 'sums' (as well as the sorting
and grouping stuff) by rewriting the base query. It's much
more efficient that way of course, but it means that you can't
use the base query order when writing a report.

(david)
"Mike MacSween" <mi***********************@btinternet.com> wrote in message
news:40***********************@news.aaisp.net.uk.. .
I have a report based on:

PARAMETERS Forms!frmProductions!ProdID Long;
SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS
LastName, First(qryMusician.FirstName) AS FirstName
FROM (qryEvent INNER JOIN qryRoleEvent ON qryEvent.EventID =
qryRoleEvent.EventID) INNER JOIN qryMusician ON qryRoleEvent.MusoID =
qryMusician.MusoID
WHERE (((qryEvent.ProdID)=[Forms]![frmProductions]![ProdID]))
GROUP BY qryMusician.MusoID
ORDER BY First(qryMusician.LastName), First(qryMusician.FirstName);

It's the total each musician gets paid for a series of concerts which are
part of the production.

If I have text control in the report footer, =Sum([SumOfFee]), (i.e. total
budget for that production), I lose the sorting in the detail section. The
detail is a list of the musicians and their total fee.

If I move the text control into the page footer no problem.

Anybody got any idea why?

I can correct it with sorting and grouping for the detail. But I'd like to
know why.

Cheers, Mike

Nov 12 '05 #5

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:40***********************@news.syd.swiftdsl.c om.au...
It actually does some of those 'sums' (as well as the sorting
and grouping stuff) by rewriting the base query. It's much
more efficient that way of course, but it means that you can't
use the base query order when writing a report.


Thanks David.

Yes, it's rapidly becoming clear that there's no point setting any sort of
order in the base query for a report, as the report will decide for itself.
And Access reports have got pretty good sorting and grouping anyway.

Mike
Nov 12 '05 #6

P: n/a
Mike MacSween wrote:
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:40***********************@news.syd.swiftdsl.c om.au...
It actually does some of those 'sums' (as well as the sorting
and grouping stuff) by rewriting the base query. It's much
more efficient that way of course, but it means that you can't
use the base query order when writing a report.

Thanks David.

Yes, it's rapidly becoming clear that there's no point setting any sort of
order in the base query for a report, as the report will decide for itself.
And Access reports have got pretty good sorting and grouping anyway.

Mike


Here is another trick I employ if the report footer places the values
far below a value. It is also very handy in setting up multi-column
reports and you want heading above each column.

In the report query, I create a column called Master with the value of
M...or whatever value you want.

My first sort grouping is on Master and I create a footer for the Master
group. I move the report footer fields to the Master footer. This
makes the ending count get closer to the last record printed and is more
presentable.

For multi-column reports, the first sort is on Master. I make it have a
header. I move the column headings into the Master header (not page or
report headers). I set the Master Header band's RepeatSection to Yes.
Then I tell the printer how many columns there are and the column width.
Voila. Easy way to create column headings for each column in the report.
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.