How to add an additional column in query/table?

[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!

 

[jdcallejas]

Hello, each column in access is a field of your table. I think this is what youe doing, you have a field name cost, you want that cost to be added 500 if the field Sex=MAle right?

Ok just do the following, if your Sex field is a combobox, "meaning you pick from a list" just goto events, afterupdate and put this

If Sex=Male than Cost= Price + 500
if sex=Femalw than Cost=Female Price
End if
Me.refresh

I think this should work.... The idea is to set the Sex field to change the amount on the cost field depending what you picked...

Sorry but not to good with VB either...

 

[henrypotter]

Thanks for getting back to me. There is a field called Sex. It equals either Female or Male. There is another field called Cost. I now want to have an additional field called Cost 2 where its condition based on whether the record is male or female.

Would it be possibile to create an additional field?

Where can I input the logic? In properties?

 

[jdcallejas]

Ok,

How are you populating the field Sex? Do users type in the sex or they pick it from a list?

Daniel

 

[The_Doc_Man]

First, it is unwise AT THE TABLE/QUERY level to talk about "extra fields" because that is not what Access does. You either populate or don't populate the field, but in order to EVER populate it at TABLE/QUERY level, it must ALWAYS exist. There is nothing dynamic about the existence of a field.

The same is not true for forms and reports. It is possible within a form to have some VBA code behind the Form_Current event (which fires when a new record becomes current or when the old record is updated in place). Put the extra control on the form that corresponds to your extra field. Always display it. But... in that form's Form_Current event code, you could test what was in your SEX control (NOT FIELD ... CONTROL) and decide whether to make this extra CONTROL visible or not.

If you are letting people see the table or query, then you are already not doing yourself a favor. You should always use a form because you can put complex validation code in the BEFORE_UPDATE event of the form. That way, if there is something wrong, you can force the update to abort and put up a message box or something.

 

[jdcallejas]

Extacly what I wanted to say!!!! :O!!!!

DOC is right use a form, access works with forms for data entry, meaning is better that way. This is what I was trying to explain, I thought you did have a form, didnt read good enough what you posted....


Thanks DOC, I am trying to help but need more experience I guess..

Daniel

 

[henrypotter]

I don't have a form for users to input. All I want to do is to use existing field to calculate some results.

Excel is very easy to handle this situation. Are you saying Access doesnot support that? but I am not very good at Access.

My database comes strictly from Excel Data. Users don't need to input.

Should I write VBA code to add additional fields to the database? Thanks!

 

[jdcallejas]

Send me the excel file and tell me what you want to do.... I will try to get the results you want to have....

Daniel