Calculations, Queries and making it all come together in my table

[Carla]

hello,
I am trying to create a very simple database (I'm pretty new to Access). In
my main table it have a field call % Safe which is to show the results a
calculation. I understand I should create a query. I have two issues - one,
the calculation I created doesn't seem to work. I created a simple query
based on the fields in my main table, in the query field called % Safe, under
Criteria I entered = [# Safe]-[# Unsafe]/[# Safe]. now, I assumed it would
look at the data entered in the main table, perform the calculation and BAM
present me with answer in this field. Does not.

Other issue is how to create the relationship between the query and the main
table so that the correct calculation is showing with the correct data?

Hopefully this makes sense and hopefully you can help!

 

[KARL DEWEY]

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post. Post some sample records
also.

 

[John W. Vinson]

hello,
I am trying to create a very simple database (I'm pretty new to Access). In
my main table it have a field call % Safe which is to show the results a
calculation. I understand I should create a query. I have two issues - one,
the calculation I created doesn't seem to work. I created a simple query
based on the fields in my main table, in the query field called % Safe, under
Criteria I entered = [# Safe]-[# Unsafe]/[# Safe]. now, I assumed it would
look at the data entered in the main table, perform the calculation and BAM
present me with answer in this field. Does not.

Other issue is how to create the relationship between the query and the main
table so that the correct calculation is showing with the correct data?

Hopefully this makes sense and hopefully you can help!

The % Safe field should SIMPLY NOT EXIST in your table. Since its value can be
calculated on demand, and since any value you store in a table field will be
wrong the moment a new record is added or the safe/unsafe status of a record
is changed, it's just a Bad Idea.

Secondly, you should not use special characters such as % or # in fieldnames.
They're legal (if you ALWAYS use [brackets]) but can cause real headaches and
hard-to-find bugs.

Thirdly, the Criteria line is used to provide *criteria* on a field to select
records matching the criteria. What you want is to create a new calculated
field in your Query.

Remove the % Safe field from your table (you REALLY don't want or need it!!!)
and create a query based on the table. In the query grid find a vacant Field
cell (the top row of the grid) and type your expression in it:

PctSafe: [# Safe]-[# Unsafe]/[# Safe]

Assuming that neither field will be NULL and that # Safe will never be 0, this
should do the calculation for you. You can then base a Form and/or Report on
this query to display the PctSafe value.

 

[Carla]

Thanks John.

I think I understand what you are telling me. I removed the field %safe
from my table and added the calculation into the query as you indicated.

It is returning the wrong answer though. When I put the data into an excel
spreadsheet and use the formula =(A2-B2)/A2, where A is the # Safe column and
B is the # Unsafe column, I get the result of 93.75% when using 32 Safe and 2
Unsafe. My query in Access gives me a result of 31.9375. Any suggestions?

Also I have to put the query into design view and hit the run button to
update when I add additional data. How do I make this happen automatically?

hello,
I am trying to create a very simple database (I'm pretty new to Access). In
my main table it have a field call % Safe which is to show the results a
calculation. I understand I should create a query. I have two issues - one,
the calculation I created doesn't seem to work. I created a simple query
based on the fields in my main table, in the query field called % Safe, under
Criteria I entered = [# Safe]-[# Unsafe]/[# Safe]. now, I assumed it would
look at the data entered in the main table, perform the calculation and BAM
present me with answer in this field. Does not.

Other issue is how to create the relationship between the query and the main
table so that the correct calculation is showing with the correct data?

Hopefully this makes sense and hopefully you can help!

The % Safe field should SIMPLY NOT EXIST in your table. Since its value can be
calculated on demand, and since any value you store in a table field will be
wrong the moment a new record is added or the safe/unsafe status of a record
is changed, it's just a Bad Idea.

Secondly, you should not use special characters such as % or # in fieldnames.
They're legal (if you ALWAYS use [brackets]) but can cause real headaches and
hard-to-find bugs.

Thirdly, the Criteria line is used to provide *criteria* on a field to select
records matching the criteria. What you want is to create a new calculated
field in your Query.

Remove the % Safe field from your table (you REALLY don't want or need it!!!)
and create a query based on the table. In the query grid find a vacant Field
cell (the top row of the grid) and type your expression in it:

PctSafe: [# Safe]-[# Unsafe]/[# Safe]

Assuming that neither field will be NULL and that # Safe will never be 0, this
should do the calculation for you. You can then base a Form and/or Report on
this query to display the PctSafe value.

 

[TedMi]

You need to wrap the subtraction in parens, just like you do in Excel:
PctSafe: ([# Safe]-[# Unsafe])/[# Safe]
-TedMi

Thanks John.

I think I understand what you are telling me. I removed the field %safe
from my table and added the calculation into the query as you indicated.

It is returning the wrong answer though. When I put the data into an
excel
spreadsheet and use the formula =(A2-B2)/A2, where A is the # Safe column
and
B is the # Unsafe column, I get the result of 93.75% when using 32 Safe
and 2
Unsafe. My query in Access gives me a result of 31.9375. Any
suggestions?

Also I have to put the query into design view and hit the run button to
update when I add additional data. How do I make this happen
automatically?

hello,
I am trying to create a very simple database (I'm pretty new to Access).
In
my main table it have a field call % Safe which is to show the results a
calculation. I understand I should create a query. I have two issues -
one,
the calculation I created doesn't seem to work. I created a simple
query
based on the fields in my main table, in the query field called % Safe,
under
Criteria I entered = [# Safe]-[# Unsafe]/[# Safe]. now, I assumed it
would
look at the data entered in the main table, perform the calculation and
BAM
present me with answer in this field. Does not.

Other issue is how to create the relationship between the query and the
main
table so that the correct calculation is showing with the correct data?

Hopefully this makes sense and hopefully you can help!

The % Safe field should SIMPLY NOT EXIST in your table. Since its value
can be
calculated on demand, and since any value you store in a table field will
be
wrong the moment a new record is added or the safe/unsafe status of a
record
is changed, it's just a Bad Idea.

Secondly, you should not use special characters such as % or # in
fieldnames.
They're legal (if you ALWAYS use [brackets]) but can cause real headaches
and
hard-to-find bugs.

Thirdly, the Criteria line is used to provide *criteria* on a field to
select
records matching the criteria. What you want is to create a new
calculated
field in your Query.

Remove the % Safe field from your table (you REALLY don't want or need
it!!!)
and create a query based on the table. In the query grid find a vacant
Field
cell (the top row of the grid) and type your expression in it:

PctSafe: [# Safe]-[# Unsafe]/[# Safe]

Assuming that neither field will be NULL and that # Safe will never be 0,
this
should do the calculation for you. You can then base a Form and/or Report
on
this query to display the PctSafe value.

 

[KenSheridan via AccessMonster.com]

A query's result set will normally be refreshed when data in the underlying
table is changed, but it will not be requeried, i.e. it won't reflect any
rows deleted from or added to the table, only changes to existing rows (more
on this below).

To reiterate, what you should have is a normal Select query returning the
columns from the table, and another column in query design view with this in
its 'field' row:

RatioSafe: ([# Safe]-[# Unsafe])/[# Safe]

Note that I've called the computed column RatioSafe as it does not in fact
return the percentage, but a fractional value such as 0.9375. Its only by
formatting it as Percent that it shows in percentage format.

The underlying table contains no column for the ratios, the column is a
computed one returned on the fly by the query, so will automatically reflect
the current values of the [# Safe] and [# Unsafe] columns in each row of the
table whenever the query, or better still a report or form based on the query,
is opened. If you base a form on a query then you can requery the form when
rows in the underlying table are inserted or deleted, but you have to include
the functionality to do this, its not automatic. This means that the
additions to or deletions from the table have to be done in another form, not
in the raw datasheet view of the table. But data should never be entered or
edited in that way in any case, only in forms. You'd then need to requery
the second form (the one based on the query with the ratios) in two places in
the first form: firstly in its AfterInsert event procedure; and secondly in
its AfterDelConfirm event procedure. The code in each case is the same:

Forms![YourSecondForm].Requery

substituting the real form name of course.

Ken Sheridan
Stafford, England

Thanks John.

I think I understand what you are telling me. I removed the field %safe
from my table and added the calculation into the query as you indicated.

It is returning the wrong answer though. When I put the data into an excel
spreadsheet and use the formula =(A2-B2)/A2, where A is the # Safe column and
B is the # Unsafe column, I get the result of 93.75% when using 32 Safe and 2
Unsafe. My query in Access gives me a result of 31.9375. Any suggestions?

Also I have to put the query into design view and hit the run button to
update when I add additional data. How do I make this happen automatically?
[quoted text clipped - 33 lines]

should do the calculation for you. You can then base a Form and/or Report on
this query to display the PctSafe value.

 

[KenSheridan via AccessMonster.com]

PS:

Add an extra line to the code:

On Error Resume Next
Forms![YourSecondForm].Requery

That will prevent an error being reported if the second form isn't open at
the time.

Ken Sheridan
Stafford, England