How to calculate a field within a table??

[Novaember]

Hello
I have data stored in a table. For each record there are multiple fields that the data is Yes, NO, NA, FYI.
I want another field on each record to count how many of these items are not NA or FYI, but I don't know how to set that field up to automatically count without some kind of user input.

The users are selecting the NA, Yes, No etc but I don't want them to have to do something additional to count and store that number.

I'm guessing it is some kind of macro or query that is bound to the field type but I'm completely lost.

The record would look like this:
NA| YES| NA| NO| YES| [countOpportunities]
countOpportunities would = 3 for this record.

Any help is greatly appreciated!
Thanks

 

[boblarson]

The answer is YOU DON'T DO THIS. At least not until Access 2010 comes out and you buy it and then have DATA MACROS and/or Calculated Fields to maintain this. There is no way currently to have a table make a change to data within the table based on data in another column. Access doesn't have "triggers" like SQL Server. And so the normalization answer says you should not be storing data that is dependent upon other columns of data.

You would need to be using a form to do this but then it is dangerous because someone could go change data in the table directly and BOOM! Your data is not correct.

So, currently, until Access 2010 comes out - you do this with a QUERY to DISPLAY it only and do not store that information in the table.

 

[HiTechCoach]

It goes against proper data normalization rules to store a value that is calculated from other fields in the record. even is Access 2010 provides a way to do it, my opinion is that in your case you still should not do it.

I would urge you not to store the calculated value. I would calculate the valee as need for forms and reports. It can also be done in a query.

My guess is that you have repeating fields. I looks liek the fields should each be on a separate record for a child table. To get the value you want, would be a query that sums the records.

IMHO, the reason you are not finding it easy to do what you want is because your database has design issues.

 

[boblarson]

IMHO, the reason you are not finding it easy to do what you want is because your database has design issues.

And I would agree with that assessment as well.

 

[boblarson]

even is Access 2010 provides a way to do it, my opinion is that in your case you still should not do it.

And THAT is something we will have to "relearn" Boyd. It is going to be a fact of life, if you want to use the web enabled features of Access Web Services, that you will NEED to use calculated fields and/or data macros in order to make things work. So, I know it is going to go against every fibre of your being to accept that, but I have and so have many of the other MVP's because we've seen the future and it does entail making compromises of some sort. And this is one of them. And frankly, with the new calculated fields, while it does violate the normalization rules, it can be very handy for speed and accuracy for things even not web enabled.

 

[HiTechCoach]

And THAT is something we will have to "relearn" Boyd. It is going to be a fact of life, if you want to use the web enabled features of Access Web Services, that you will NEED to use calculated fields and/or data macros in order to make things work. So, I know it is going to go against every fibre of your being to accept that, but I have and so have many of the other MVP's because we've seen the future and it does entail making compromises of some sort. And this is one of them. And frankly, with the new calculated fields, while it does violate the normalization rules, it can be very handy for speed and accuracy for things even not web enabled.

Bob, I have had to store calculated value. Only on on rare occasions. Mainly when I was passing data to other applications. I hope this new feature in 2010 does not get abused/misused outside of using the web enabled features of Access Web Services.

 

[Novaember]

Hi everyone
Thanks for the feedback. I knew I was doing it wrong. I finally figured out how to do it within my query. Funny how one day something seems so hard and the next day it is so simple. Sometimes you just need to walk away for a while lol Thanks again!

 

[boblarson]

I hope this new feature in 2010 does not get abused/misused outside of using the web enabled features of Access Web Services.

Well, I would consider this, based on what it does, to be a benign tumor (so-to-speak) because even if it is frequently used outside of the norm, at least it will not HURT anything or cause data integrity issues. In fact, it can be helpful because it will maintain the values EVEN if someone gets in and messes with the underlying data in the tables AND the calculated fields can NOT be manually adjusted.

 

[Galaxiom]

Well, I would consider this, based on what it does, to be a benign tumor (so-to-speak) because even if it is frequently used outside of the norm, at least it will not HURT anything or cause data integrity issues. In fact, it can be helpful because it will maintain the values EVEN if someone gets in and messes with the underlying data in the tables AND the calculated fields can NOT be manually adjusted.

I agree. There is no breach of normalisation if the field is automatically maintained. It is also going to save work in form design and processing time. I think they sound interesting.

Do these derived fields work only within an individual record or can they derive values referring to, say, totals? For example could the derived field calculate the percentage that a value in a record represents of the total of a field for all records in the table?

Can they refer to values in other tables?

 

[boblarson]

I have not dug in too deep yet on these, but as far as I know, a calculated field can only refer to fields in the current table and it only maintains the calculation amongst the individual rows.

I will have to look at it again to know about how aggregates would be but if I remember correctly there should be some way of doing it, because you can't use domain aggregate functions within the web app.