sum() in report footer destroys detail order

[Mike MacSween]

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

[Salad]

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.

[Mike MacSween]

"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

[Salad]

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.

[david epsom dot com dot 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.

(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

[Mike MacSween]

"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

[Salad]

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.