Can't wrap my head around DSum or calculated controls

[Binx324]

Hi,

I am building a database after a long absence from the database world. I have little understanding of how VBA or SQL coding works, which is a problem because one of the things I’m trying to do in my form requires it.

I have a table called tblParks that’s linked to tblCollections by storing the ParkID from tblParks in a tblCollections field called ParkSite. Each collection record includes a field tracking the volume of material in the collection (CollTotalVol). And each park record currently has an empty field where the total volume of all that park’s collections is supposed to be stored (VolEst).

What I want is for the VolEst field in the Park form (which links to tblParks) to automatically show the total volume of all of a park’s collections. So if I have 9 collections for a park, it adds up all 9 collection’s volumes and gives the total in VolEst. I suspect I need to do some sort of DSum expression, either in a query that groups park collections together or by turning the empty VolEst field into a calculated control on the form. But I really don’t know which option is better or how to do either.

 

[wrmosca]

Binx

DSum() functions can pull a lot of overhead. It would probably be better to create an aggregate (Group By) query with the ParkSite in tblCollections as the Group By and the CollTotalVol as a Sum.

Then you can join tblCollections and the new query on ParkSite and use SumOfCollTotalVol for the new field you want.

Of course, you don't want to store that calculation as it changes with every change to the source table.

 

[Binx324]

DSum() functions can pull a lot of overhead. It would probably be better to create an aggregate (Group By) query with the ParkSite in tblCollections as the Group By and the CollTotalVol as a Sum.

Wow, that was easy. I was attempting to make things way too complicated. The query works great, thanks!

Then you can join tblCollections and the new query on ParkSite and use SumOfCollTotalVol for the new field you want.

Of course, you don't want to store that calculation as it changes with every change to the source table.

I'm a little confused by this. Did you mean that I should join tblCollections to the query or tblParks to the query? And if I'm not storing the total volume of all the collections in tblParks, what do I do tio make the sum from the query show up in the VolEst field on the Parks form?

 

[wrmosca]

Binx

Join the newly created query to tblParks. Then add the Sum field to the grid along with all the fields in tblParks.