eliminate zeros and negatives on a group footer. Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a report that generates several pages of data. looks like this;

Date amount code name type
1/1/05 $100 ESC John D
1/2/05 $100 ESC D
1/3/05 ($250) ESR R
Totals ($50)
1/1/05 $100 ESC Pam D
1/2/05 $100 ESC D
1/3/05 $100 ESC D
Totals $300

and it goes on for a hundred or so pages. The Total line is a group footer
sorted from Name. Anyway, what i need to be able to do is if the Group Footer
is less than 0, omit the records from the report. So, if the Total line was
($50) like it is for 'John', all of John's data is not shown at all.
 
You need to create a totals query that Sum()s the amount by Name (similar to
your report's record source). Then add this query to your report's record
source and join the Name (hope your field name isn't name) fields. You can
then filter on SumOfAmount.
 
can you explain how to Sum() a query? I only know how to create a summary
that requires a date.
 
I don't know how your report's record source is defined or filtered but you
would create a similar totals query:
SELECT [NameField], Sum([AmountField]) As SumOfAmount
FROM tblNoNameGiven
WHERE [DateField] Between ......
GROUP BY [NameField];

Save and add this query to your report's record source query and join the
[NameField]s. Add the SumOfAmount to the report's record source so you can
use it to set a criteria.
 
Back
Top