How to make a calculated field with this data

  • Thread starter Aries via AccessMonster.com
  • Start date
A

Aries via AccessMonster.com

Hi everyone,

I'm new to access and I would like to ask for some assistance with my problem.
 
F

fredg

Hi everyone,

I'm new to access and I would like to ask for some assistance with my problem.
Click to expand...

And your problem is?
I would strongly suggest you rad some of the posts by others to this
newsgroup to learn how to successfully ask a question.
Remember, we cannot see your database so it's up to you to make your
problem clear. A few examples is always helpful.

Perhaps take a look here first:
See Netiquette at http://www.mvps.org/access

It's a great site to visit anyway.
 
A

Aries via AccessMonster.com

oops, my table got messed up.. here's a better view..

Amount TransactionTypeID TransactionDate "CalcField-1" "CalcField-2"
15.00 3 01/01/09 13.50

15.00 3 01/01/09 13.50

15.00 3 01/01/09 13.50

30.00 3 01/01/09
26.75

30.00 3 01/02/09
26.75

30.00 3 01/02/09
26.75
 
A

Aries via AccessMonster.com

My apologies if I haven't made my problem clear.. I'll try to explain it the
best I can..

I have a Transactions table that contains a field Amount with values

Amount
15
15
30
50
and so on..

What I need is to have a "new"field showing..

Amount
15 should be reduced by 1.50 leaving 13.5
30 should be reduced by 3.25 leaving 26.75
and so on..

what I tried doing is to make a calculated field like this

calculatedfield1: [Amount] - 1.50
calculatedfield2: [Amount] - 3.25

but the problem is that it applies to all amounts in the field, I don't know
if its possible that my calculatedfield1 will only apply to the Amounts with
15.00 and likewise the calculated field 2 would only apply to amounts with
value of 30.

I hope this helps.

Thanks again.
Aries said:
Hi everyone,

I'm new to access and I would like to ask for some assistance with my problem.
.

Is it possible to make multiple calculated fields based on the values in the
field? I mean, I wanted to apply the calculated field only to specific value
or range

I was hoping to having something like this.

Transactions table (Query)
-------------------------------
Amount TransactionTypeID TransactionDate "CalculatedField-1"
"CalculatedField-2"
15 3 01/01/09
13.5
15 3 01/01/09
13.5
15 3 01/01/09
13.5
30 3 01/01/09
26.75
30 3 01/02/09
26.75
30 3 01/02/09
26.75

So basically, I wanted to create a calculated field based on the [Amount]
field having a certain value and apply it to that record alone.

here's what I've done,

- filtered the records in the transactions table to show only the pertinent
records by transactiontype
- created a calculated field CalculatedField-1: [Amount] - 1.50
- created a calculated field CalculatedField-2: [Amount] - 3.25

the problem is I don't know how to apply those calculated fields only to
those specific values on the [Amount] field.

I'm not sure if I'm doing this right, perhaps my approach is entirely wrong,
so please enlighten me.

Thanks in advance!
Click to expand...
 
P

Philip Herlihy

Aries said:
My apologies if I haven't made my problem clear.. I'll try to explain it the
best I can..

I have a Transactions table that contains a field Amount with values

Amount
15
15
30
50
and so on..

What I need is to have a "new"field showing..

Amount
15 should be reduced by 1.50 leaving 13.5
30 should be reduced by 3.25 leaving 26.75
and so on..

what I tried doing is to make a calculated field like this

calculatedfield1: [Amount] - 1.50
calculatedfield2: [Amount] - 3.25

but the problem is that it applies to all amounts in the field, I don't know
if its possible that my calculatedfield1 will only apply to the Amounts with
15.00 and likewise the calculated field 2 would only apply to amounts with
value of 30.

I hope this helps.

Thanks again.
Aries said:
Hi everyone,

I'm new to access and I would like to ask for some assistance with my problem.
.

Is it possible to make multiple calculated fields based on the values in the
field? I mean, I wanted to apply the calculated field only to specific value
or range

I was hoping to having something like this.

Transactions table (Query)
-------------------------------
Amount TransactionTypeID TransactionDate "CalculatedField-1"
"CalculatedField-2"
15 3 01/01/09
13.5
15 3 01/01/09
13.5
15 3 01/01/09
13.5
30 3 01/01/09
26.75
30 3 01/02/09
26.75
30 3 01/02/09
26.75

So basically, I wanted to create a calculated field based on the [Amount]
field having a certain value and apply it to that record alone.

here's what I've done,

- filtered the records in the transactions table to show only the pertinent
records by transactiontype
- created a calculated field CalculatedField-1: [Amount] - 1.50
- created a calculated field CalculatedField-2: [Amount] - 3.25

the problem is I don't know how to apply those calculated fields only to
those specific values on the [Amount] field.

I'm not sure if I'm doing this right, perhaps my approach is entirely wrong,
so please enlighten me.

Thanks in advance!
Click to expand...
Click to expand...

I wouldn't be able to comment until I understood more about this
requirement; it's easy enough to provide an expression for these two
values but would it be re-usable? What "discount" would you apply to
16? 29? 31.5? Is there a rule, or are the "discounts" abitrary?

If you're wanting to apply a function to only some records, then my
guess is your table design is wrong....

Phil, London
 
F

Fred

Here's the short translation from my dummy level.

For 99% of applications (which almost certainly includes yours) you
shouldn't store the result of a calculation (= don't make a field [in your
table] for the result. ). Just program the calculation into the query(s),
form(s) or report(s) where you need to see the result. Then the result
will be instantly calculated and shown whenever you need it.

If you want help with programming the calculation, you are going to have to
describe it better.
 
Top