subtotals/totals from SQL statement

[Pendragon]

Access 03/WinXP

I am using a function to generate an SQL statement based on several
user-defined filters from a form. This SQL statement is used as the record
source in the OnOpen statement of the report property. I have no problems
with the SQL statement, filters, etc. - the report displays exactly the
records requested.

Where I am having an issue in the the report's group subtotals and the
report totals; the amounts calculated are the totals for the *entire*
underlying table of the initial SQL statement from the function and *not* the
filtered set of records.

I tried using the running totals options on the text boxes but the result
was the same.

Suggestions are appreciated.

 

[Duane Hookom]

I am confused by this. I have never seen a report with totals that include
more records than the report's record source.

How are you calculating totals? Are you using something like:
=Sum([FieldFromRecordSource])

 

[Pendragon]

Sorry, should have included that. Yes, I am. The fields in the detail
section are InvoiceAmount and PaidAmount; a third text box calculates the
difference for a Balance Due.

The group footer and report footer simply utilizes Sum([InvoiceAmount]) and
Sum([PaidAmount]).

I am confused by this. I have never seen a report with totals that include
more records than the report's record source.

How are you calculating totals? Are you using something like:
=Sum([FieldFromRecordSource])

--
Duane Hookom
Microsoft Access MVP

Access 03/WinXP

I am using a function to generate an SQL statement based on several
user-defined filters from a form. This SQL statement is used as the record
source in the OnOpen statement of the report property. I have no problems
with the SQL statement, filters, etc. - the report displays exactly the
records requested.

Where I am having an issue in the the report's group subtotals and the
report totals; the amounts calculated are the totals for the *entire*
underlying table of the initial SQL statement from the function and *not* the
filtered set of records.

I tried using the running totals options on the text boxes but the result
was the same.

Suggestions are appreciated.

 

[Duane Hookom]

The total/sum will not include values that are not in the report's record
source query.

Could you describe your method used to filter the report?

--
Duane Hookom
Microsoft Access MVP

Sorry, should have included that. Yes, I am. The fields in the detail
section are InvoiceAmount and PaidAmount; a third text box calculates the
difference for a Balance Due.

The group footer and report footer simply utilizes Sum([InvoiceAmount]) and
Sum([PaidAmount]).

I am confused by this. I have never seen a report with totals that include
more records than the report's record source.

How are you calculating totals? Are you using something like:
=Sum([FieldFromRecordSource])

--
Duane Hookom
Microsoft Access MVP

Access 03/WinXP

I am using a function to generate an SQL statement based on several
user-defined filters from a form. This SQL statement is used as the record
source in the OnOpen statement of the report property. I have no problems
with the SQL statement, filters, etc. - the report displays exactly the
records requested.

Where I am having an issue in the the report's group subtotals and the
report totals; the amounts calculated are the totals for the *entire*
underlying table of the initial SQL statement from the function and *not* the
filtered set of records.

I tried using the running totals options on the text boxes but the result
was the same.

Suggestions are appreciated.

 

[Pendragon]

Sorry to have taken your time - I resolved the issue. There was a table in
the initial select statement (from a previous version of the report) which
was creating a one-to-many situation below the level of the filters; thus the
large sums. When I removed the culprit, the report calculated correctly as
you said it should.

ugh.

The total/sum will not include values that are not in the report's record
source query.

Could you describe your method used to filter the report?

--
Duane Hookom
Microsoft Access MVP

Sorry, should have included that. Yes, I am. The fields in the detail
section are InvoiceAmount and PaidAmount; a third text box calculates the
difference for a Balance Due.

The group footer and report footer simply utilizes Sum([InvoiceAmount]) and
Sum([PaidAmount]).

I am confused by this. I have never seen a report with totals that include
more records than the report's record source.

How are you calculating totals? Are you using something like:
=Sum([FieldFromRecordSource])

--
Duane Hookom
Microsoft Access MVP


:

Access 03/WinXP

I am using a function to generate an SQL statement based on several
user-defined filters from a form. This SQL statement is used as the record
source in the OnOpen statement of the report property. I have no problems
with the SQL statement, filters, etc. - the report displays exactly the
records requested.

Where I am having an issue in the the report's group subtotals and the
report totals; the amounts calculated are the totals for the *entire*
underlying table of the initial SQL statement from the function and *not* the
filtered set of records.

I tried using the running totals options on the text boxes but the result
was the same.

Suggestions are appreciated.