Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Access Database help

  1. #1
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate

    Access Database help

    Ok yall, figured you would be my best place for help.

    I have somehow become the keeper of some databases with absolutely no formal training.

    One of my databases has the main table where two fields formated as currency and need to be just regular numbers. As they are some 1.8 million rows, the simple format change options aren't working. I know that there is supposed to be some way to change this with an update query, but it is escaping me with massive google fu failure. There is also supposed to be a way with VBA, but I have absolutely no experience in that and what I find is confusing me

    Treating me like a 3rd grader, can someone help me with fixing this?

  2. #2
    Junior Member
    Join Date
    Jan 2014
    Posts
    2,103
    vCash
    0
    Points
    235,838
    Bank
    0
    Total Points
    235,838
    Donate
    Literally 1.8 million rows?
    "But I got it because I'm an iSheep who needs to have all my stuff have an Apple logo on it."

  3. #3
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,645
    vCash
    1792
    Points
    82,676
    Bank
    0
    Total Points
    82,676
    Donate
    Easiest I can think of:

    ****Backup db****
    Update the table, add new column [tmp] of type numeric double, default value NULL (you will be using this as a "failed to update" indicator)
    Run query to update
    UPDATE Table1 SET Table1.tmp = [your_money_field];
    Validate all data was updated
    SELECT * FROM Table1 WHERE tmp IS NULL
    Drop old column, rename tmp to that name

  4. #4
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate
    Quote Originally Posted by Larommi View Post
    Literally 1.8 million rows?
    Nah, only 1,964,488 rows

  5. #5
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate
    Quote Originally Posted by CeeBee View Post
    Easiest I can think of:

    ****Backup db****
    Update the table, add new column [tmp] of type numeric double, default value NULL (you will be using this as a "failed to update" indicator)
    Run query to update
    UPDATE Table1 SET Table1.tmp = [your_money_field];
    Validate all data was updated
    SELECT * FROM Table1 WHERE tmp IS NULL
    Drop old column, rename tmp to that name
    Ok, the table is called Distribution Shipment Detail. Old Field is Gross Weight, new field is TMP.

    Is it
    Field: Gross Weight
    Table: Distribution Shipment Detail

    UPDATE: Distribution Shipment Detail SET Distribution Shipment Detail.TMP = [Gross Weight]

  6. #6
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,645
    vCash
    1792
    Points
    82,676
    Bank
    0
    Total Points
    82,676
    Donate
    Put names with spaces within square brackets
    UPDATE [Distribution Shipment Detail] SET [Distribution Shipment Detail].TMP = [Gross Weight]

  7. #7
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate
    Quote Originally Posted by CeeBee View Post
    Put names with spaces within square brackets
    UPDATE [Distribution Shipment Detail] SET [Distribution Shipment Detail].TMP = [Gross Weight]
    Damn, you expect a lot out of a 3rd grader, guess I should have said I wasn't better than a pre-schooler
    Didn't realize you were giving me SQL, worked like a charm.

    If I can be greedy/needy/wanting/bothersome. What would we change to make it a number rather than text?

  8. #8
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate
    Oh, and thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  9. #9
    Senior Member CeeBee's Avatar
    Join Date
    Jan 2014
    Posts
    1,645
    vCash
    1792
    Points
    82,676
    Bank
    0
    Total Points
    82,676
    Donate
    Quote Originally Posted by Cleetus View Post
    What would we change to make it a number rather than text?
    If the destination field is numeric then it will be a number. If there are values that cannot be converted I'd probably do something like
    UPDATE [mytable] SET [mytable].[mynumcolumn] = Cdbl(mytxtcolumn) WHERE IsNumeric(mytextcolumn)=true
    That will explicitly convert to a double type where the text field can be interpreted as number. See which values don't convert
    SELECT * FROM [mytable] WHERE IsNumeric(mytextcolumn)=false
    As before check for failed conversions looking for null just in case...

    I hate Access. It uses a proprietary SQL wannabe implementation. Everything is much easier in plain SQL.

  10. #10
    Senior Member
    Join Date
    Jan 2014
    Posts
    361
    vCash
    500
    Points
    44,378
    Bank
    0
    Total Points
    44,378
    Donate
    Was driving home and realized where I went wrong with making the field a numeric one. Derp. Thanks again. Now off to get my Excel/PowerPivot updated to 64bit now that this table is over 2.1 million rows as of this morning.

    Yeah, our whole team has screamed ourselves horse telling them that Access is not the solution that they are looking for. Maybe if oil prices go back up we will get some money to do this correctly.

Posting Permissions

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