- Local time
- Today, 11:11
- Joined
- Feb 28, 2001
- Messages
- 27,309
Part of the problem is your fixation that the total needs to be in the table. No, it doesn't. (Yes, I know it is YOUR design and not mine, but bear with me.)
Everything you do with Access should be centered around recordsets. These can come from either tables or queries. Rather than store totals in a table, you compute them in a query when you need to see them. You can open the query in the same place as you could open the table. BUT... the aggregate field cannot be updated. And it SHOULDN'T be.
If this field is to represent the count or total of something that is in another table, nobody should be able to edit that to make it anything other than the count or total. I.e. if you store it and allow it to be edited later (you said "edit all fields..."), then it has no meaning because Joe Schmuckatelli can come in and bollix up that table totally so that it has no relationship to reality.
Stated another way, the records in the Initiatives table represent real things that can be added or removed from consideration. That, I understand. But fields representing COUNTS or TOTALS of real things are NOT real things. They are meta-data, statistical data, about those real things. As they are descriptive and intended to reflect reality, they should NEVER be editable. They can be changed by changing the table on which they are based but should never be changed by the man on the street. EVER.
Everything you do with Access should be centered around recordsets. These can come from either tables or queries. Rather than store totals in a table, you compute them in a query when you need to see them. You can open the query in the same place as you could open the table. BUT... the aggregate field cannot be updated. And it SHOULDN'T be.
If this field is to represent the count or total of something that is in another table, nobody should be able to edit that to make it anything other than the count or total. I.e. if you store it and allow it to be edited later (you said "edit all fields..."), then it has no meaning because Joe Schmuckatelli can come in and bollix up that table totally so that it has no relationship to reality.
Stated another way, the records in the Initiatives table represent real things that can be added or removed from consideration. That, I understand. But fields representing COUNTS or TOTALS of real things are NOT real things. They are meta-data, statistical data, about those real things. As they are descriptive and intended to reflect reality, they should NEVER be editable. They can be changed by changing the table on which they are based but should never be changed by the man on the street. EVER.