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

    Cool Unanswered: Modify data from 7 digit number to 9 digit number

    I need help with this project. I have a table of General Ledger accounting code numbers and there descriptions. There are currently 425 account numbers in this table. This is a two part change to this table. First change the account numbers from this format 1101.001, I need the decimal in position 5 to go away so it reads 1101001 and then the last 1 in position 7 to change to 0 so it reads 1101000 and finally add two more 0's so the new number is 110100000 or 9 digits long. This is the new format to this table GLMaster and data GLAccount. Now the tricky part I need this data to copy itself and append to the table GLMaster with exactly the same records again this time modifing the GLAccount record, position 5 and 6 should change from 00 to 10 so it reads 110110000. I then need it to do it again changing the position 5 and 6 to 20 so it reads 110120000. I need this table to have every account number, have a record or new account number from 00 to 10, 20, 30, 40, 50, 60, 70, 80, 90. This should make the the table size change from 425 records to 4250 giving every account number a new record 9 times. An example is account number 1101.001 Cash On Hand. I need it to change to 110100000 Cash On Hand as well as add to this table by coping 110100000;
    110110000 Cash On Hand
    110120000 Cash On Hand
    110130000 Cash On Hand
    110140000 Cash On Hand
    110150000 Cash On Hand
    110160000 Cash On Hand
    110170000 Cash On Hand
    110180000 Cash On Hand
    110190000 Cash On Hand.
    Attached Files Attached Files
    Last edited by jamesmp3; 11-27-04 at 10:59.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    UNTESTED

    are these guys really numbers? (too lazy/scared to download your zip).

    if yes: on a copy of your DB try:

    add a new field newGLAccount, Long

    add a run-once query:
    UPDATE GLMaster SET newGLAccount = Fix(GLAccount * 1000) -1;

    DONT USE THIS CODE - IT IS HORRIBLY WRONG

    add a use-once form with a button "butGO" and some code (DAO, but ADO equivalent exists)

    private sub butGO_Click()
    dim dabs as dao.database
    dim recs as dao.recordset
    dim myAccount as long
    dim loupe as integer
    dim strSQL as string
    set dabs = currentdb
    set recs = dabs.openrecordset("GLMaster")
    with recs
    do while not .eof
    myAccount = !newGLAccount
    for loupe = 10000 to 90000 step 10000
    strSQL = "INSERT INTO GLMaster (GLAccount) VALUES (myAccount + loupe);" 'WRONG WRONG WRONG!
    strSQL = "INSERT INTO GLMaster (newGLAccount) VALUES (myAccount + loupe);"
    dabs.execute strSQL
    next
    .movenext
    loop
    end with
    end sub

    delete the run-once query and the use-once form
    delete the field GLAccount
    rename the field newGLAccount as GLAccount
    (...reinstate any relationships)

    ...something like this should take you to:
    your current balance in NNNN00000
    and nine new accounts for each NNNN with empty balances

    if you are native ADO and you can't be bothered to rewrite the above, switch on the DAO 3.6 reference for the duration of the "conversion" and try the code as-is (ALT-F11 to get the code window, then menu: Tools¦References and check the box on Microsoft DAO3.6 blah blah blah)

    izy
    Last edited by izyrider; 11-29-04 at 09:58.
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Posts
    10

    Question

    Yes these are just numbers. My database is clean. I am using an Access version 2002 on an XP machine. I don't know what "DAO" or "ADO" is. Can you explain in detail how an absolute new to Access user can do this?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    easier to do than explain

    attached uses a different strategy (the code I posted doesn't work, and when you fix the obvious errors it runs for ever adding billions of new records).

    open the form in design view
    ALT-F11
    in the code window that opens with Alt-F11
    menu: Tools ¦ references

    either you already have Microsoft DAO 3.6 library "ticked" or you need to scroll on down to M-for-Microsoft and tick it.

    then: open the form in form view and click the button.

    don't click the button a second time without first runing the delete query to remove the added records fro the previous run.

    i included the query that made the 9 digit refs but i already ran it - it does no harm to run it again if you like, but at least you can see the design of it.

    i modified your table design (killed index-no dupes and required-field).

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    jamesmp3,

    I've got a solution for you, but there will be an issue with 4 of your accounts based upon what you have described. Sequence numbers 119-126 contain 8 account numbers which will create 4 duplicate account numbers (and eventually 40 account numbers) based on your logic of changing the last character to 0.

    Additonally, what do you want to happen to the "Sequence" field in your table? Do you want the new records to just autonumber on the append or is this field used for something else? Also, what is the purpose of the 'Sequence" 1 which is only "."? What do you want to happen with this record on the new numbering action?

    TD

  6. #6
    Join Date
    Nov 2004
    Posts
    10

    Cool

    I am not using the autonumber function, the "," sequence 1 account can be deleted and all duplicates can be deleted. I did forget that the AccountDesc and the AccountGroup must copy to the new account code numbers.
    Last edited by jamesmp3; 11-29-04 at 19:04.

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    First, make a copy of your GlMaster table and call it GlMater-old. Then delete all records from the GlMaster table. Create a new Table (I wasn't very creative and called mine Table1) with one field called Position5 which is a Number Data Type and a Field Size Integer. Next enter 10 records with values from 0-9. Then create a query with the following SQL code and run it to create new records in your GlMaster table.

    INSERT INTO GlMaster ( GlAccount, AccountDesc, AccountGroup )
    SELECT Left([GlMaster-old]![GlAccount],4) & [Table1].[Position5] & "0000" AS GLAccount, First([GlMaster-old].AccountDesc) AS AccountDesc, First([GlMaster-old].AccountGroup) AS AccountGroup
    FROM Table1, [GlMaster-old]
    GROUP BY Left([GlMaster-old]![GlAccount],4) & [Table1].[Position5] & "0000", Mid([GlAccount],5,1)
    HAVING (((Mid([GlAccount],5,1))="."))
    ORDER BY Left([GlMaster-old]![GlAccount],4) & [Table1].[Position5] & "0000";

    The 'First' statements are to accomodate the accounts that duplicate when you replace the Position 7 character with a 0.

    TD

Posting Permissions

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