Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Find and Replace - Account Mapping

    We are in the process of converting from one database to another. Until everything is up and running in the new one, we need to manually import GL and other accounting processes. This requires us mapping the old account number to the new one.

    Is there a good way to do this in Access? I have a "cross walk" of the old and new account numbers, I just need to find a good way to do this because there is a ton of them!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Be sure to have a look at MS Access FAQ (Read Me First - NO SERIOUSLY)

    What does the data look like as you "manually import GL and other accounting processes"? What do you want it to look like? What are the tables involved? What are the table relationships? How versed are you in VB and SQL?
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Table: CROSSWALK

    Fields: OldAccountNumber, NewAccountNumber

    Would that help you?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2009
    Posts
    5
    it's one table that has account 12345 as the old one and I want to say if account=12345 replace it with 01012345 but there is literally hundreds of accounts we'll have to do this for.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so either you type "literally hundreds" of lines of SQL or you find a (some) rule(s).

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2009
    Posts
    5

    That's what I'm looking for....

    Quote Originally Posted by izyrider
    so either you type "literally hundreds" of lines of SQL or you find a (some) rule(s).

    izy
    Do you have any rules??

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    We can make up some rules for you sure, but they're going to be completely arbitrary and worthless. Izy is saying that you have define what the rules are since you are the one defining the database. If you provide the rules, we'll try our best to help you flesh those out into code and SQL and such.

    In other words, define what you mean by
    Quote Originally Posted by bgoven
    This requires us mapping the old account number to the new one.
    and anything else that's important for us to know.
    Me.Geek = True

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I have lots of rules!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by nckdryr
    Be sure to read through the link I posted in #2.
    Me.Geek = True

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would of thought a temporary column in either db (but probably best in the new db) that has both the old and the new account codes.
    then as your data arrives you find the old account number and allocate the new account number from your temporary column.

    if you want to keep both systems separate & clean then have a temporary table which the users don't see or even know about.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2009
    Posts
    5

    Don't know rules

    Quote Originally Posted by nckdryr
    We can make up some rules for you sure, but they're going to be completely arbitrary and worthless. Izy is saying that you have define what the rules are since you are the one defining the database. If you provide the rules, we'll try our best to help you flesh those out into code and SQL and such.

    In other words, define what you mean by

    and anything else that's important for us to know.
    I don't know anything about making up rules. All I have is an access table with old and new accounts and I need to write something to convert the old ones to the new ones.

  12. #12
    Join Date
    Mar 2009
    Posts
    5

    how would I use this and where?

    Quote Originally Posted by StarTrekker
    Table: CROSSWALK

    Fields: OldAccountNumber, NewAccountNumber

    Would that help you?
    How would I use it and where?

  13. #13
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by bgoven
    We are in the process of converting from one database to another. Until everything is up and running in the new one, we need to manually import GL and other accounting processes. This requires us mapping the old account number to the new one.

    Is there a good way to do this in Access? I have a "cross walk" of the old and new account numbers, I just need to find a good way to do this because there is a ton of them!
    How about you give us a sample set of "old account numbers" and their corresponding "new account numbers"?
    Me.Geek = True

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by bgoven
    I don't know anything about making up rules. All I have is an access table with old and new accounts and I need to write something to convert the old ones to the new ones.
    Ah, hold on a sec... do you mean you have a table with a column containing old account numbers and a column of new account numbers and you need to change the column of old account numbers to match exactly with the new account numbers?

    Before:

    Code:
    OLD         NEW
    1           25001
    2           25002
    3           25003

    After:

    Code:
    OLD             NEW
    25001           25001
    25002           25002
    25003           25003
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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