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

  • Thread starter Thread starter Carla
  • Start date Start date
C

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!
 
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.
 
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!
Click to expand...

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.
 
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?

John W. Vinson said:
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!
Click to expand...

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.
Click to expand...
 
You need to wrap the subtraction in parens, just like you do in Excel:
PctSafe: ([# Safe]-[# Unsafe])/[# Safe]
-TedMi

Carla said:
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?

John W. Vinson said:
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!
Click to expand...

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.
Click to expand...
Click to expand...
 
Back
Top