In my original post # 1, I said it was for a bit of FUN. But most people read what they wanted to read and not the real intent. (FUN)
I was expecting someone to say, “I never store calculated Values”
I would have then said, “Hey what about AutoNumber or DMax Plus 1”
I don't think that's the same thing. Autonumber and DMax are techniques to generate a new unique number typically for use in a primary key (and hence good relational design). If you want to stretch the point further you might argue that DATE() is a formula and therefore calculated data!
To address the Storing Calculated Values.
This practice is to be frowned on in general circumstances. There is an argument that if an underlying value was to change then the calculated value would then be incorrect. The inverse argument is that if an underlying value was changed then the stored Calculated Value should alert you to this fact, and that you should investigate the cause.
Which would expose a serious problem with your database. Imagine if an auditor found an invoice where the values didn't tie up. What would your response to the auditor be? "Must be a glitch in my database"? The auditors response will be "it's going to be a long night then..."
There lies the fundamental problem. If you are going to to store derivable data then you must be 100% (and I do mean 100%) sure that your database is able to enforce that. In Access this is, in principle, achievable e.g. using events etc. But Access comes with a lot of "what-ifs". What if the PC crashes before the update? What if someone decides to update directly to the table but doesn't realise the Total also has to be updated? e.g. the VAT was wrong but it's ok we can correct with an Update query because we haven't published the invoices yet. What if a new designer comes along and designs another form that updates tables but doesn't realise the original designer had been storing calculated data?
I appreciate all these can be overcome by professionals and experienced users but most of the questioners to these forums are not at that level. To allow novices go down this road in preference to the "good practice" of not storing calculated data is, imho, dangerous. By the same token you could say Normalisation is not necessary (as Dave "the husky" pointed out, many 3rd party systems don't implement hard normalisation), but is this a good grounding for a novice to build database? Far better to encourage practices which makes the process of designing consistent database easier to undertake.
So when I see someone answer an OP’s question by critising them for their table strustructure without asking for a proper understanding, then I feel that the person answering the post is most often quoting rhetoric that they have read before without taking the time to understand the OP’s situation.
I assume you mean questioning their table structure rather than actually criticising them?(I don't think I've ever seen OP's criticise people personally simply because their design was flawed).
I think this is off the topic the that you are keen to have discussed (a worthy topic by the way), but this is how I see it. These are public forums where OP's contribute for free. In doing that they inevitably want to limit their time spent responding. These often means that assumtions have to be made in order to at least start to address the question. It would be unreasonable to expect an OP to do a full analysis of the users requirements before feeling they could give the correct answer.
Furthermore, when I read a post, the first thing I ask myself is "is the table design right?". If there are any clues to suggest they are not following good design practice I will point these out regardless of whether it's answering the question or whether they may be in the 5% of cases where they are justified. It encourages the user to reflect on their design.
Contrast this to the approach where I were to simply answer the question and don't challenge the potential design flaws. This certainly solves their current problem quickly and neatly in the short term but spells potential disaster for the user in the long term.
Chris