Running sum in form with subform

[bjornas]

I am trying to teach myself Access while keeping the books for a school excursion.

I have to tables, one "Participants" and one "Payment details". They are related by a "Person IDfk" field to a "IDpk" field in the Participant table. I.e., each participant will make several payments.

I have also made a participant report, with a "Payment Detail" subform that reflects the above. I have also added a Sum Field for each participants payments in the sub-report.

Now, I would also like to add a field for summing up payments for each class, as well as a grand total for all participants. I know how to this by using the grouping, sorting and summing function. However, I cant get that function to sum together the sums in the several sub-reports.

Can anyone point me to a solution to this?


Rgds., Bjørn Olav, Oslo, Norway

[ranman256]

reports can do running sum, but not a form.

[Micron]

Your post title says this is about a form but your post suggests this is about a report?
I'm not following - you are saying you know how to do this for a report but not when it's a subreport?

Should be doable in a form, but would require a numeric and unique field sorted ascending (PK autonumber would be good) using Dsum function with a filter that DSum's where the PK_id <= Pk_id. Have not tried that but seems logical to me, but likely would have to be in a continuous form.

[Micron]

Tried it on a form just for the fun of it.



=DSum("[Field with value to be summed here]","[table name here]","Pk name here <=" & [Pk name here])

Notes:
- Pk name here is Pk field name referred to in prior post.
- No quotes around field name because it's numeric
- field name and comparison operators are one string component

[bjornas]

Sorry, my bad. I am talking about A REPORT...

I will try the dsum suggestion.

Rgds., Bjørn Olav

[bjornas]

OK, so the subreport has a field called "beløp" (Norwegian = amount). There is also a =sum([beløp]) at the bottom of the subreport, summing up how much each participant has paid. So far, so good.

In the footer section for "class", there also is a =sum([beløp]) field. This mysteriously returns a *count* of how many participants there are, rather than a sum of the payments.

I didnt get the DSum function to work properly. I am quite sure I am missing something very essential here.

[Micron]

Consider posting a db copy that you have compacted then zipped. You could remove whatever is not required to help with report design - just make sure both reports will open with records and that we know which reports are involved if there are a bunch of them. Also make sure we know what report control should sum what. Not sure if the language will be an issue for all of us or not.

[bjornas]

Pls find attached a zipped version of the database. Although it is in Norwegian language, I belive it is self explanatory.

Thank you for your help in this matter.

[mike60smart]

Hi
I tend with this particular table structure to create the Report without a SubReport.

See the example "rptPameldongsliste" in the attached

[bjornas]

Mike60smart; Precisely what I needed! Thank you!

One thing though: I adapted your suggestion somewhat. I added the fields for "date", "amount" and "sender" in the details section for the PersonID_FK as you indicated. However, when putting the form to use, those field names mysteriously changed to "Expr1001", "Expr1002" and "Expr1003". They do reflect the data in the "date", "amount" and "sender" fields, however, so I dont bother with that. It was just strange.

One other thing: I have made the report "smaller", i.a. by using a column layout. Still, the "report footer" section comes on a separate page. I would have suspected that a <CR> was added before the report footer-section. But the "force page break" property for the fotter cetion is set to "none", so it cant be that.

Any suggestions?


Rgds., Bjørn Olav

[mike60smart]

Can you upload your current version?