Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    May 2009
    Posts
    19

    Red face Unanswered: Excel to Access Questions.. please help

    ok,.. I've got all these trip report forms that I need to import into Access to better keep track of them and search them.

    Would love a point in the right direction on What I have to do to make this happen.
    And possibly to make all fields searchable
    And hopfully with out having to change up the original excel sheet since I have a ton of them filled out already.

    Any help would be amazing



    PS,.. Ive added a pic of what the form looks like to give you guys an
    idea of what I'm working with
    Attached Thumbnails Attached Thumbnails trip report pic.bmp  

  2. #2
    Join Date
    May 2009
    Posts
    19
    I embeded the pic.. Might make it easier for you you guys to help :P

    http://i39.tinypic.com/epqccj.jpg

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You aren't going to be able to import that easily at all. I'd build an access db to support the data in the XL "forms", but trying to import the data would be horrendous. How many of these is in a ton? It might be quicker to re-key them?
    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
    May 2009
    Posts
    19
    hmmm,.. well I have one for every client.. not sure how many... but i think we havent been using these forms long.. so maybe 100 .. or less i hope

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would create an excel sheet to transform the data from Form layout to Table (one row per form) using formulae. Import this data into access, clean it and transfer to normalised tables. This is called ETL if you want to get an idea of the essentials.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2009
    Posts
    19
    ok,.. think I can do that.
    Is there a formula to grab info from sheet 1 and display it on sheet 2....
    Im sure there is..lol
    I'll go google it :P

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    =MySheetName!A1
    Code:
    =MySheetName!B2
    etc etc
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If the existing spreadsheets are consistant in their layout, I would write a module to automate the process described by Pootle Flump (TM).

    If you could maybe get copies of all these spreadsheets into one folder (not essential but a little easier). I can knock you up a little access module to open each one, set the formula in place and then extract the data into a local access table.

    If possible could you post a .zip of one of the excel files (just put 'dummy' data in it so as not to expose any data)?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    May 2009
    Posts
    19
    Ok, got that... that was easy... So am I just going to use row 1 to identify what the data is in the column and then continue on each row with the data from each client

  10. #10
    Join Date
    May 2009
    Posts
    19
    Here's a zip file of an example
    And yes all the forms are consistant
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Thanks - can write you a little module to automate this - hang fire 12hrs or so!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  12. #12
    Join Date
    May 2009
    Posts
    19
    that would be amazing
    Much apprieciated
    And I would offer u help in anything that I know how to do.. but by looking at your sig.. you got it all covered..lol :P
    oh ya... i fogot.. I know some cisco voip.. call manager and unity that i would offer my help if ya ever needed it..lol

  13. #13
    Join Date
    May 2009
    Posts
    19
    Also.. I have a sheet2 made that link the the first sheet if that helps you any.. let me know if u need it

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Don't need that mate.

    What would help though (but not essential). Would be the details of the table and the fields which this data will ultimately be imported to.

    If you could state:

    Table name
    xlCellReference Field Name in table Datatype in table

    I can make sure the local table is in the correct format - I can also extract the data in such a way their is less 'cleaning up' to do.

    As I say, not essential if you don't understand (I'll just use my 'noddle') but it would be of assistance and maybe avoid issues later on in the process.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  15. #15
    Join Date
    May 2009
    Posts
    19
    eh,.. table isnt create let.. guess I can just name it what ever to match what you need.. if I under stand right

Posting Permissions

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