A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Totals in linked subforms



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2008, 03:00 PM posted to microsoft.public.access.forms
C Tate
external usenet poster
 
Posts: 45
Default Totals in linked subforms

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.


  #2  
Old April 22nd, 2008, 07:24 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Totals in linked subforms

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.
--
Dave Hargis, Microsoft Access MVP


"C Tate" wrote:

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.


  #3  
Old April 22nd, 2008, 10:20 PM posted to microsoft.public.access.forms
C Tate
external usenet poster
 
Posts: 45
Default Totals in linked subforms

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!



"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"C Tate" wrote:

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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.