Running Sum Over Group - Sporadic Error

[jessicastar9373]

Using Access 2010. I have a report (Invoice) with one subreport (Invoicesubrepo rt). For each client, a new page is generated. We bill by the event, so the subreport groups by EventDate and the subreport is called from the Invoice report for each EventDate. I have a total calculated per Event in the EventDate footer of the subreport. In that same footer, I have an invisible field [RunningClientNe t] set to =Sum([Amount]) (from the subreport detail)... "Over All".

In the Client Footer on the parent Invoice report, it prints the Client Net ([Reports]![Invoice]![InvoiceSubrepor t].[Report]![RunningClientNe t]). Most of the time, this field is correct (whether the client only has one event or multiple, it sums correctly). But sporadically, within one report, the ClientNet will add itself to the ClientNet on the previous page (for the previous client). None of our clients' invoices print to a second page, so it's just one page per client. On pages 6, 11, 13, 18, 20 (so far, I stopped checking), the ClientNets include the current cleint's event nets plus the ClientNet from the previous page (for a different client). Ideas? Thank you in advance for your time and help.

[twinnyfo]

jessicastar9373 ,

My only though, initially, is that the report is summing by date (you mention you are grouping by EventDate). This may cause Access to think you are trying to sum over the records matching that particular date. SInce I don't know all the data, this is just conjecture. Since common dates are often associated with different records, it may be wiser to try to exclude that data field from the grouping options (grouping by ClientID, for example).

Just check to see if the dates for these errors are the same. it might be a place to start.....

[jessicastar9373]

Thanks for the idea, twinnyfo. I had wondered the same thing but there are no common dates when the current client record incorporates the previous client's total into it's total. We are billing for the renting of our facility's auditorium and we only ever book one event per date and the Sum for each client is Summing the total from each outstanding event date for that client. But there's never an event for another client on the same event date. (But I still double-checked the report right now to verify this just in case.)

[twinnyfo]

Would it be possible for you to post a partial of your DB and I'd be glad to look at the report and see what I can see???

[jessicastar9373]

Twinnyfo: Unfortunately, since we're a state university, we've got so many regulations about confidentiality , etc. I think it would be frowned upon if posted any part of it, without spending a lot of time making up some dummy records. Your offer is very kind, though, and I wish I could take you up on it. I'll have to keep looking into it when I can. We're approaching end of fiscal year (8/31) and are swamped right now. Thank you.

[twinnyfo]

Jessica,

As I am re-reading your initial question, I am wondering if it might be possible to include a Report Footer on your SubReport with the data for RunningClientNe t. This would guarantee the RunningClientNe t data would only be associated with one Client, and you wouldn't have to refer to your SubReport that way. As I "visualize" how I think your report "looks", it would put that value in roughly the same location on your report.

Just an idea. But, it just might work. Hope you don't get too swamped. Try to keep your head up! :-)

[zmbd]

jessicastar9373 :
Twinnyfo may have your answer with the overlapping or adjacent dates because of the way access stores the date/time information.
You may only see "5/15/99;" however, MSA sees "36295.0" which shows as "05/15/1999 00:00" internally... so, depending on how your query, or how the formula your fields, is written you can very well be getting some overlap.
(http://support.microsoft.com/kb/210276)

Would you please post the formula that your form is using in the field to calculate the values and the SQL the reports are based on.

Please remember to use the [CODE][/CODE] tags :)
-z
(sorry if this seems short.. using my Mom's PC and the keyboard is funky... love vacation... prefer my own PC.. where's the spell check... Rabbit... what Rabbit... ahhahahah.... stupid tab key :))