You need to wrap the subtraction in parens, just like you do in Excel:
PctSafe: ([# Safe]-[# Unsafe])/[# Safe]
-TedMi
"Carla" <(E-Mail Removed)> wrote in message
news:7842487A-E376-4EE9-B5B8-(E-Mail Removed)...
> 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" wrote:
>
>> On Wed, 25 Nov 2009 10:24:01 -0800, Carla
>> <(E-Mail Removed)>
>> wrote:
>>
>> >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.
>> --
>>
>> John W. Vinson [MVP]
>> .
>>
|
Similar Threads
