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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|