Totals in linked subforms

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C Tate - 22 Apr 2008 15:00 GMT

I have a main form with 2 linked subforms, much the same as the customer
order form in the Northwind database.

The main form contains details of a site. The subforms relate to houses on
the site and the funding of them.

I can easily create a subtotal for the first subform (which is total cost
for all the houses). So far I have only been able to create a subtotal (total
funding) for the individual block of houses on the second subform. This is
because when you click housingID on the first subform, the second subform
only shows the relevant funding records (you will see what I mean if you look
at the customer order form in Northwind).

Somewhere on my main form, I'd like to display the total funding for all the
houses on the site, not just the various filtered lines of the second
subform. Perhaps this is impossible or perhaps it is not the right way to do
things, but I thought I would ask anyway!

If it is impossible, any advice on alternative stategies would be much
appreciated. Thank you.

Reply to this Message

Klatuu - 22 Apr 2008 19:24 GMT

You could use a DSum function on the main form to return the sum for all
houses on the site. Since I don't know your data, I can only give an example
you can work from. Use a text box with the DSum as the control source:

=DSum("[FieldToSum]","TableOrQuery","[SomeField] = " & Me.txtSomeControl)

If you have any difficulty, let me know what controls on the form and or
subform you need to filter on, the names of the fields in the table or query,
and the name of the table or query.

Signature

Dave Hargis, Microsoft Access MVP

> I have a main form with 2 linked subforms, much the same as the customer
> order form in the Northwind database.
[quoted text clipped - 16 lines]
> If it is impossible, any advice on alternative stategies would be much
> appreciated. Thank you.

Reply to this Message

C Tate - 22 Apr 2008 22:20 GMT

Thanks. In the main form I have siteID. There are other fields in it but not
important here I don't think. The first subform (housing subform) is about
housing. I have housingID and then information about the houses, eg, number
of units (ie, how many houses), tenure, the date they were acquired and some
others. There are many lines in the first subform as different blocks of
houses are built in different years (all on the same site). 10 might be in
2008, 20 in 2009 etc.

The second subform (funding subform) is about funding those houses. The two
subforms are linked (just like the customer order form in Northwind). There
is a fundingID (though I don't show this on the form), funding (the actual
amount) and funding souce (which budget the money is coming from).

On the main form I want to show the running total of the funding for all the
houses on this particular site. In other words I am looking at all the lines
on the first subform and adding up all the funding. At the moment all I have
succeeded in doing is calculating the total for a particular line. I want to
use the dsum thing to calculate the running total of the funding for all the
houses. It is the funding column I am adding up.

So the field to sum would be [funding] and the table would be [funding
subform] but after that I'm stuck!

> You could use a DSum function on the main form to return the sum for all
> houses on the site. Since I don't know your data, I can only give an example
[quoted text clipped - 26 lines]
> > If it is impossible, any advice on alternative stategies would be much
> > appreciated. Thank you.

Reply to this Message