Unanswered: Updating data from one column based on another.
I am very new to Access, but would like a detailed direction on this if possible. I have a table that has a column named GlAccount it has account numbers that I would like to use to change the value in another column named AccountGroup. GlAccount will always have a 8 digit number, example 1101.001, 1104.001 or 1106.001 and so on. (4 digit number followed by a decimal then a three digit number = 8 digits). The column named AccountGroup will have names, Asset, Liability, Capital, Income and Expense. This is what I need: If GlAccount number starts with a 1, like 1101.001 it changes the AccountGroup to Asset, if GlAccount number starts with a 2, like 2105.001 it changes the AccountGroup to Liabilty and so on. Heres the legend: If 1 = Asset, if 2 = Liabilty, if 3 = Capital, if 4 = Income, if 5 = Expense and if 6 = Expense. I know 5 and 6 are Expense. :-) If anyone has an idea, please send. It would be great if this could be one sql statement instead of multiple ones, but that would be fine as well.
Assuming the following:
* you have a table named Table1
* It has the two described text fields, GlAccount and AccountGroup
The following SQL statement should work:
Switch([GlAccount] Like '1*','Asset',
[GlAccount] Like '2*','Liability',
[GlAccount] Like '3*','Capital',
[GlAccount] Like '4*','Income',
[GlAccount] Like '5*','Expense',
[GlAccount] Like '6*','Expense');