How to add an additional column in query?

[henrypotter]

Hi All,

Could anyone tell me how to create an additional column in Access based on certain condition such as if sex = male, than cost + 500?

How could I write that in my VBA? Thanks!

 

[KenHigg]

What are the exact field names you will be using and what will the logic be?

 

[henrypotter]

Hi Ken,

The field name is Sex which contains Male and Female. Another field name is Cost. How do I add an additional field called cost 2 with the condition that if sex = male, cost2 = cost + 500.

should I do this in VBA? or should I do this in query or table?

Thanks!

 

[KenHigg]

In the last blank column do something like:

Cost 2: iif([sex] = 'Male', [cost] + 500, 0)

If you want it be be cost if not male then:

Cost 2: iif([sex] = 'Male', [cost] + 500, [cost])

 

[henrypotter]

Thanks Ken.

Where should I put these logics? in VBA or in query? or else where?

 

[KenHigg]

In your query on the top row of the first blank column

 

[henrypotter]

Thanks! I tried to edit that statement in SQL view but it says "You tried to execute a query that does not include the specified expression 'iif (....) as part of an aggregate function.

What does that mean?

 

[KenHigg]

Why did you not use the query builder grid tool?

 

[henrypotter]

I did and the same thing happened.

 

[KenHigg]

Let me see if I can whipp you up a sample db... Give me a minute...

 

[henrypotter]

Ken,

It's now working, however, I have some records that does not input MALE or FEMALE, I want to add 500 Cost to it too. What's the word for nothing in the query code?

Thanks,

 

[KenHigg]

See if this helps

 

[KenHigg]

You should be able to change:

NewCost: IIf([sex]="male",[cost]+500,[cost])

To:

NewCost: IIf([sex] ="female",[cost],[cost]+500)

???

 

[henrypotter]

Unfortunately, I cannot open your file since I am still using Access 2000.

If [sex] is empty, I want to change the cost to 0. So there are 3 conditionns. Should I write?

NewCost: IIf([sex] ="Male",[cost],iif([sex]="",0,[cost]+500))

would access be able to recognize "" as empty?

 

[KenHigg]

Try this one

 

[henrypotter]

If [sex] is empty, I want to change the cost to 0. So there are 3 conditionns. Should I write?

NewCost: IIf([sex] ="Male",[cost],iif([sex]="",0,[cost]+500))

would access be able to recognize "" as empty?

 

[KenHigg]

The way I read this there is one condition, if female then it should equal cost, otherwise it should equal cost + 500.

 

[henrypotter]

Sorry this is an extra question. if I want to put an extra conditional to eliminate emply Sex entry. What should I do?