Need direction on best approach

[mrcarter]

I'm having a hard time determining the best function to use and or query. I have a column of transaction codes that I need to reference and based on that code will determine what I need my new column to update to. (assuming update query is the best approach) there is a possibility of 7 different codes with a possibility of 4 different outcomes. not sure if I should use the iif or switch function or a combination of the two, if thats even possible.



the query/update/etc would be something to the effect of:

iif code equals 1 then subtract the dollars in column B and subtract total payment months in column D AND (these amounts need to be removed from my total sum of payments and number of payment months)
iif code equals 2 then add the dollars in column B and add the months in column D (there is another field in the table that I can reference to say use this number for payment months)
iif code equals 3 then add the dollars in column B and convert the months in column D to 0 (do not want them included in total payment months sum but need to still include the dollars in total payments)
iif code equals 4 then subtract the dollars in column B and payment months should be converted to 0 (same as above scenario)

there are 7 different codes due to different reasons assigned to the codes but how we calculate the dollars and payment months remains the same.

any guidance would be appreciated.....

[mrcarter]

UPDATE: I don't need to worry about subtracting the dollars for those codes. that has been taken care of. now I just need to know for those records how to change the number of payment months from a positive number to a negative so that my sum of payment months does not reflect these records in the sum of total payment months

[ssanfu]

I am totally lost on what you are doing.

now I just need to know for those records how to change the number of payment months from a positive number to a negative

To change a number from a positive to negative, multiply by -1.

[June7]

First all, saving calculated data (data dependent on other data) is usually a bad idea.

If you are trying to determine whether or not a value should be a positive or negative according to a code in another field, maybe:

[dollars] * IIf([code]=1 Or [code]=4,-1,1)

After that, I am also lost on what you are doing.

[mrcarter]

I did my best to explain it, my apologies....I've attached an example of what I'm trying to do. At this point I'm trying to use an update query to create a new field by looking at the Adj Reason Code (column B) and if it's blank, like row 1 below, use the value in # of Payment Adj Months (Column A) otherwise return the value 0. I have 55k records and 6 different codes sporadic to certain records. So the value in my new column will either be 0, the value in # of payment adj months or the negative of the value in # of payment adj months. Thank you for the multiply by -1 to change it to a negative value, I have some Adj reason codes that require me to have the # of payment adj months decreased. Hope this makes my question easier to understand

[orange]

Sometimes it's a whole lot easier for the reader, if the poster tells us about the issue/opportunity in plain, simple English.
We need to know WHAT you are trying to do, before anyone can give a focused response/suggestion.

I noticed that Steve and June both said they were confused on what you are doing.

A recommendation is that you should not use a naming convention that allows embedded spaces, or uses special characters in field and/or object names.

Good luck with your project.

[June7]

An UPDATE query cannot create field, can only edit data in existing field.

Any design that requires routine creation of new fields is bad. Among other issues, eventually will hit 255 fields limit.

[mrcarter]

I added the new field to my table and I'm updating the blank records in the new field by referencing the field with the existing data. I used a switch function but it worked for 3 out of the possible 7 codes. Here is where I'm at: The codes that aren't updating is the 50 code is not updating to 0 and the codes 1, 3, and 12 are not multiplying by -1. I get the absolute value. is this a data format issue possibly?

Switch ([Adj Reas Cd]=41,"0",[Number Of Pmt/Adj Months Prt D], [Adj Reas Cd]=50,"0",[Number Of Pmt/Adj Months Prt D], [Adj Reas Cd]=1 Or 3 Or 12,[Number Of Pmt/Adj Months Prt D]*[-1],[Number Of Pmt/Adj Months Prt D], [Adj Reas Cd]=2 Or 31,[Number Of Pmt/Adj Months Prt D])

[June7]

Don't put 0 within quote marks.

The Switch syntax is not correct. Refer to Access/VBA Help. http://www.experts-exchange.com/Micr...ft-Access.html

This does not make sense:

([Adj Reas Cd]=41,"0",[Number Of Pmt/Adj Months Prt D],

Also correct to:
[Adj Reas Cd]=1 Or [Adj Reas Cd]=3 Or [Adj Reas Cd]=12