How can I get Access to calculate a percentage on two of my fields

[Christinahoff924]

I am a relatively simplistic user and as such I use the Query Design View to
create all my update queries since I do not know SQL or VB.

I need to calcuate a percentage from two fields in my table and update the
percentage to another field in my database. I can't understand why this is
giving me such a fit. I just created a similar query in that I added two
fields together and updated the sum to another field and had no problem. Is
there something different when you want to divide? I've spent 4 hours trying
to do this and no progress.

 

[Duane Hookom]

You don't normally want to store a value that can easily be calculated. If
you have a super good reason for storing a calculated percentage, you can run
an update query like:

UPDATE tblNoNameGiven
SET PctField = FieldA/FieldB
WHERE FieldB > 0;

Since you didn't state what your issue was, you should make sure PctField is
not Long or Integer or other whole number type.

 

[KARL DEWEY]

You should not be storing calculated data because as information changes the
stored results will be out of date.

Open your query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[Christinahoff924]

Thanks. I'll paste the sql in a post tomorrow. In my case, I do want to store
the calculated data as it will never change and I will constantly be using it
for statistical analysis. My data is actually test results and our client
wants us to calculate and make a report for each candidate who tests that
includes the number of attempted items and the percentage of attempted items
they answered correctly. We will then have to use the same data in a mail
merge to inform the candidates of their results. Just wanted everyone to know
my reasons for wanting to store the data. Thanks again.

 

[Christinahoff924]

Okay. Here is the sql text of my query. The result in the my database field
is always a 1 which is obviously incorrect.

UPDATE [QRO New HST Results] SET [QRO New HST Results].Percentage =
[pscore1]/[poverall];

Any help is enormously appreciated.

 

[KARL DEWEY]

The result in the my database field is always a 1 which is obviously
incorrect.
I would bet big bucks that your Percentage field is an Integer. That means
only whole numbers are allowed. Percentages are decimal fractions and
therefore are less than one.

Change your field to Number - Double datatype with format as Percentage.

Okay. Here is the sql text of my query. The result in the my database field
is always a 1 which is obviously incorrect.

UPDATE [QRO New HST Results] SET [QRO New HST Results].Percentage =
[pscore1]/[poverall];

Any help is enormously appreciated.

You should not be storing calculated data because as information changes the
stored results will be out of date.

Open your query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[Christinahoff924]

That was almost embarrasing. Never thought about the set up of the field.

Thank you. You are the Access God.

Christina

incorrect.
I would bet big bucks that your Percentage field is an Integer. That means
only whole numbers are allowed. Percentages are decimal fractions and
therefore are less than one.

Change your field to Number - Double datatype with format as Percentage.

Okay. Here is the sql text of my query. The result in the my database field
is always a 1 which is obviously incorrect.

UPDATE [QRO New HST Results] SET [QRO New HST Results].Percentage =
[pscore1]/[poverall];

Any help is enormously appreciated.

You should not be storing calculated data because as information changes the
stored results will be out of date.

Open your query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

:

I am a relatively simplistic user and as such I use the Query Design View to
create all my update queries since I do not know SQL or VB.

I need to calcuate a percentage from two fields in my table and update the
percentage to another field in my database. I can't understand why this is
giving me such a fit. I just created a similar query in that I added two
fields together and updated the sum to another field and had no problem. Is
there something different when you want to divide? I've spent 4 hours trying
to do this and no progress.

 

[Duane Hookom]

That's why I mentioned in my first reply: "Since you didn't state what your
issue was, you should make sure PctField is not Long or Integer or other
whole number type."

--
Duane Hookom
Microsoft Access MVP

That was almost embarrasing. Never thought about the set up of the field.

Thank you. You are the Access God.

Christina

The result in the my database field is always a 1 which is obviously

incorrect.
I would bet big bucks that your Percentage field is an Integer. That means
only whole numbers are allowed. Percentages are decimal fractions and
therefore are less than one.

Change your field to Number - Double datatype with format as Percentage.

Okay. Here is the sql text of my query. The result in the my database field
is always a 1 which is obviously incorrect.

UPDATE [QRO New HST Results] SET [QRO New HST Results].Percentage =
[pscore1]/[poverall];

Any help is enormously appreciated.

:

You should not be storing calculated data because as information changes the
stored results will be out of date.

Open your query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

:

I am a relatively simplistic user and as such I use the Query Design View to
create all my update queries since I do not know SQL or VB.

I need to calcuate a percentage from two fields in my table and update the
percentage to another field in my database. I can't understand why this is
giving me such a fit. I just created a similar query in that I added two
fields together and updated the sum to another field and had no problem. Is
there something different when you want to divide? I've spent 4 hours trying
to do this and no progress.