Forms and Tables

[Tiffy]

Hello,



I created a form out of a table and added expressions for it to do a certain calculation for me, but any results from the calculations won't show up in the same field on the table, only manual inputs. Is there a way to make this possible? Please help

P.S. I need it to show up on the tables because I have a union query on a different database for several similar tables.

Thank you,
Tiff

[boblarson]

The question has been asked many times before and it will be asked many times in the future. “I have calculations on my form but they aren’t saving to the table and I need them to be saved, why aren’t they saving?” The answer is straightforward but there is a higher principle at stake here. The answer is that calculations in controls (formulas set in the control sources of the controls) means that they are not bound to the fields where you would be storing the data. But before you go try changing that, you should be aware of a few things.

• It is actually RARE that you would store calculated data in the tables.
• If you store calculated data in the tables, your data is at a much higher risk of being INCORRECT and you lose the integrity of your data.

So let’s address each one of those. First off, it is rare that you would store calculated data in the tables. There are exceptions to every rule, of course, but the key thing to know is when you should and shouldn’t store the numbers. If you have the data stored in the table which would be used to calculate the number you want, you should not store the calculation (read up on the rules of Normalization and redundant data). If you have numbers which are date specific, in other words, if you must calculate the data as of today and there is no other way to recreate today at a later time, then you may need to store the calculated data. However, if you can store the parts which make up the final calculation, this is a better thing to do so that you can also see what went into the calculation. Sometimes, for auditing or legal/regulatory purposes, you may have to store the calculation even if you could calculate it at run time.

The second statement I made was regarding data integrity. So, what if someone can get to the table directly and change one, or more, of the pieces of data which makes up the calculation? The inputs and the final output will not be in synch, would it? No, it would not. And so what do you believe? The calculated field or the data that makes up the calculation? What if someone changed the calculated field and not the parts that make it up? Again, we don’t know what is right? The integrity of our data is shot, not reliable. So if you only store the parts which make up the calculation and then calculate the result in a query at run time, you will have the correct calculation, even if the parts have been changed.

So, it is best if at all possible, to just calculate the value in the underlying query, of the form or report, when you need it and not store the value. Also, some people think that storing the calculation is necessary if they are wanting to use a table for something. But the reality is that you can use a QUERY in 99% of the places you would use a table. So it normally does not hold that you need this data in a table.

[Tiffy]

My apology if I didn't make my question clear. When I said calculations, I didn't mean it mathematically, more of a conditional function (IIf).
It's designed for quality assurance, tracking status of inquiries. So say, each rep has their own database they're assigned for. The supervisor has a combined worksheet of all the reps for monitoring and maintaining purposes (where I had my union query). Because of the amount of inquiries we get, I'm trying to make it easier for the reps to identify which inquiries are overdue for follow up and as they change the status of an inquiry (complete, incomplete, etc) it will also reflect on the supervior's worksheet.
If you have any suggestions for a new design that would work out better, it would be much appreciated.

[orange]

You may have created a table, but your approach with separate databases and the boss has a combined worksheet just reeks of impending problems.
Do some research on database, normalization, ER diagrams and get a good handle on the underlying concepts. Your terminology suggests equivalency with Excel(spreadsheet/worksheet) and Access (tables). These products are different.
Spend some time understanding the difference and design your intended application.

Build a data model, create some test data (good and bad), test the model, adjust as necessary. When your model "works" with the test data. start the design of your tables and database.

Good luck with your project.

[Tiffy]

Any way to make this work with just access?