Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Cool 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.
    TIA

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    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:
    Code:
    UPDATE Table1
    SET [AccountGroup]=
    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');
    See if this works for you.

  3. #3
    Join Date
    Nov 2004
    Posts
    10
    Thanks
    That worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •