Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Question Unanswered: Using Excel Spreadsheet to Populate Existing DB

    I have a spreadsheet that was sent to me to update my database. The spreadsheet has a vendor ID number and a corresponding billing number. The database has these two fields in it and in some places the fields are populated but mostly the billing number field is blank but the vendor ID number exists. I need to use the spreadsheet to update the database with the correct billing number.

    What I think I need to do is go through the database and compare vendor ID to spreadsheet and when a match is found in the database, populate the billing number field with the correct data.

    Database has following fields:
    Vendor ID
    Billing Number
    Equipment Serial Number
    Equipment Model Number
    Customer Name
    Customer Address

    Any help to get this done or if you have any questions please let me know. Feel free to email if easier.

    Thanks in advance.

    KC

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is this a one-time thing or do you need to perform this fairly regularly? Regardless, I would recommend the same method, but it's easier if it's one time only.

    Basically I would import the excel sheet into your database. Once it's in, then it's a simple INSERT INTO query.

    If you need to do this on a regular basis and are looking to code a function to do this for you, you may want to take a look at the Microsoft Excel 10.0 object library under references
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    This is more than likely be a one time thing as the information coming in now has it correctly entered but it could need to be run again.

    Will what you suggest overwrite the information currently in the Vendor ID block? I need it to match up with exisiting Vendor ID (currently exists for EVERY record) and add in billing number for that line item. If it overwrites in whatever order it is listed then I'm screwed.

    Thanks for the help.
    KC

  4. #4
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Another thing that I should have mentioned is that there could be many iterations that need updated using the same two fields as the database tracks equipment and usage.

    Appreciate any assistance.

    KC

  5. #5
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Duplicate............. Removed...........
    Last edited by kccpo; 11-16-04 at 12:21.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure I fully understood everything you just asked, but INSERT INTO queries only append information to a table, they will not overwrite information. YOu would need to use an UPDATE statement to do that.

    I would hit the access help files for more information on INSERT INTO and UPDATE queries.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Thanks for the help. I'll take a look at the Insert Into Query and how to do it.

    Does it append multiple records (say ten with the same customer ID?)

    Thanks.

    KC

  8. #8
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Teddy,

    I tried looking in the help and it isn't showing an "INSERT INTO" query anywhere. The update query is there but not Insert into.

    Can you provide me an example.

    What I am trying to do is update an older table that originally didn't requiring the billing number in it, just the customer ID. But now I need both in it and have a listing of them. Many of the customer IDs are duplicated in the table and I need to update them all with the same billing number.

    Thanks for continued help.

    KC

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That sounds more like you should be running an update query...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    But if the Update overwrites then I can't risk losing the data that may be in there....I'll try and read up more about it.

    Thanks for your patience and help.

    KC

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, you just said you need to "UPDATE" your table to add a billing number to an EXISTING record. If I misunderstood, please clarify.

    If you intend to in any way alter pre-existing records, you MUST use an update statement.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Then it sounds like that is what I want to do. Just the idea of overwriting instead of appending just makes me uneasy. Yes, the fields do already exist and they need updated. I need to update one table (the main table) with the table I have with the two elements.

    This is what I'm trying to do
    Old DB
    Cust ID Billing ID Serial Number Model Number
    0001 xyz abc
    0002 xxy ddd
    0003 004 isk dea
    0004 abd xxx
    0002 aaa cce
    0004 dbb aad

    What I want
    Table 1
    Cust ID Billing ID
    0001 343
    0002 444
    0003 004
    0004 233

    Update DB after incorporating above:
    Cust ID Billing ID Serial Number Model Number
    0001 343 xyz abc
    0002 444 xxy ddd
    0003 004 isk dea
    0004 233 abd xxx
    0002 444 aaa cce
    0004 233 dbb aad

    I hope this helps....
    Thanks again.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You would want a basic syntax of:

    UPDATE yourOldTable INNER JOIN yourNewTable ON yourOldTable.[Cust ID] = yourNewTable.[Cust ID]
    SET yourOldTable.[Billing ID] = yourNewTable.[Billing ID]
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Thanks.....I'll take a look...

    Appreciate the help.

    KC

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    np. You might want to make a copy of your table and try out your update skils before applying it to your live data...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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