Totals in parent tables

[TerryEA]

Hi

I'm having another go at this in the hope that someone can suggest a better method than the one I'm using.

All I want to do is calculate the totals of a field in a transaction table ("child" or "many") and store the result in a field in another linked table ("parent" or "one"). Having read a bit, I understand that Access update queries can only update the "many" side but of course I want to update the "one" side. Is using an update query the right way to go about this, and if so, could someone suggest how?

Thanks

Terry

 

[fneily]

One of the purposes of a Relational database such as Access is that you DO NOT store totals. Since they're calculations, you can create them on your forms or reports when you need to. This is against the protocols of Access and a thing called Normalization. Some people think they have a good reason to store totals, but it usually isn't the case. See:

The Fundamentals of Relational Database Design

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

From the article:
"An obvious example of a dependency is a calculated column. For example, if a table contains the columns Quantity and PerItemCost, you could opt to calculate and store in that same table a TotalCost column (which would be equal to Quantity*PerItemCost), but this table wouldn't be 3NF"

Now, someone out here may show you how to store your totals, but it is not a good or recommended procedure.

 

[TerryEA]

Hi fneily

Thanks for the reply. I do understand what you're saying I tend to generally agree with you. I'm writing an accounting package which has to cope with the complexities of the Nominal Ledger and, in particular, the production of the Trial Balance, Profit/Loss Account and Balance sheet reports, all of which are much simplified by using stored totals. The application, and the layout of the reports is such that it is extremely difficult to design if one has to calculate the complex totals and print them in the right format on the reports. In fact, given the current nature of the transaction file, it may be impossible, without a significant change to the thinking behind the package. I've done many relatively simple applications, where reports just need to show simple totals, or totals of simple calculated fields but accounts applications can be very taxing.

The application is an update version of my own 12 year old accounting software, originally designed using a DOS database. Successfully producing the complex management reports was evry easy because the totals needed were calculated and stored first using a sort of macro, which in itself was very easy to do. THe difference it made to the size of the database etc was negligible.

Although it's not the recommended way, surely it must be a fairly easy thing to do, ie calculate totals of child records and store the totals in parent records. There are good uses for the technique, I believe.

Terry

 

[SkipVought]

Notice what you just stated. This is some sort of a CLONE of an OLD DOS database (not relational, I'd guess).

Reminds me of a story I heard where the wife, cuts the end off the ham butt before puting in the pan to bake. When asked WHY, she stated, "That's the way Mom always did it!"

Well, it turns out that Mom only had a SMALL PAN.

"Successfully producing the complex management reports was evry easy because the totals needed were calculated and stored first using a sort of macro,..."

Well these "complex management reports" with all kinds of aggregations, etc. don't need "some sort of macro", they can be performed in a QUERY.

So you just demonstrated whey you ought to adhere to the rules of normalization.

Skip,

_[red][/red]

 

[TerryEA]

Skip

I sort of know that you're right, well you are right, and you have a very entertaining way of telling me so. It doesn't detract from the fact, however, that it was a lot easier in the DOS database which was, incidentally, relational and before its time. I guess at the end of the day, being an expert of many years with the DOS database and not being an expert in Access, I was bound to try and "do it like Mother did". I think I'm generally quite good at understanding Access reports but I admit I'm not brilliant at SQL. Hey, that's why I'm asking fellas like you!

If I were younger I guess I would train to be a "programmer" like yourself, rather than a "technical user". The accounting application is almost done and it's much better than many on the market, so I'm quite proud of it. The old DOS version was actually very powerful and made me a good living for many years. This one will keep me going until I retire.

I've solved the problem now, laterally, but I still think there is merit in occasionally storing totals of child records in parent records. The difficulty I had was caused by the way the transactions table was structured. Perhaps had I been a more experienced Access user I would have done it differently. I won't bore you with the details but I solved the problem by using an append query to transfer transactions to a new temporary table, in a slightly different format, which was easier to work with, so that I could produce the reports. The temporary table is deleted and remade when it is time to produce the monthly management reports again. It works well and, as I said, has solved the problem, but I'd still like to know how to succeed in what I was trying to do, just out of interest.

It's been interesting. Many thanks for your help.

Terry