Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: import data from excell and compare fields to add data to another field

    Hello!

    I have a db with 4715 rows, the first field in the table is article number, that means I have 4715 article numbers.
    I also have a excell sheet with 328 rows, the first field here is also article number and those 328 article numbers is to find somewhere in those 4715 articlenumbers. what I mean is that 328 of those is "taken" from the big list so they are the same number.
    The second field in the excell sheet is another number I call "nobb". In the db table I have made a new field called nobb.

    What I want to do is an query or macro (or something else that works) that compares the article numbers and where the row with same article number is I want to fill in the nobb nr in the empty nobb field in the db.

    Can somebody tell me how to do this?

    Thanks
    GJ

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    import the excel spreadsheet into a table
    then run update query/queries where the part number matches

    then stop storing data in Excel, as good as spreadsheets are there are not a substitute for storing data inside a db.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    6
    Hello!

    okay, can you please help me writing the query? or at least tell me which argument I need to use.. I am new at access and therefore not relly good at it.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    import the table the excel spreadsheet as a table

    then look at the two tables (your exisitnig access table and the new excel import) and adjust the column and table names accordingly

    update MyAccessTable set MyAccessTable.Nobb = MyExcelTable.Nobb
    where MyAccessTable.ArticleNo = MyExcelTable.ArticleNo


    so say your existing column for article number is called 'Article No' and the existign tabel is called Products then

    where Product.[Article No] = ....

    you can extend this so that you only update the NOBB number if its currently blank in the access table
    where MyAccessTable.ArticleNo = MyExcelTable.ArticleNo AND isnull(MyAccessTable ) = true

    one of the bugbears of Excel (and spreadsheets in general is that they are written to be flexible, meaning that unless there is data in the first few rows there is a risk the timport will use the wrong datatype.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2013
    Posts
    6
    Hello!

    it dosen´t work I just got a question to fill in a parameter value for the fields that is in the excell table, in som way, it dosen´t recognize the other table, I have tried with both dot and underscore, och both with and without () around the field names.

    update JOMA set JOMA.NOBB = Blad1.[NOBB]
    where JOMA.ARTIKELNUMMER = Blad1.[ARTIKELNR];

    I even tried to make relations between those tables, do i have to do that?
    There is som different choises when I import the excell file, it asks me if I want a primary key and if so which field it shall be. btw I am using access 2013 if that matters?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are getting a question about a parameter, then almost certainly you have a typo or spelling mistake. if Access cannot find the name of a column in the square brackets then it presumes its a parameter
    check the datatypes are the same. the risk of importing from a spreadsheet is that it can change the underlying datatype from, say numeric to string

    you only need square brackets if there is a space in the column or table name

    as to "it dosen´t work"
    there's not a lot I can do
    unless you can supply the table names the column names.
    better yet consider copying your db
    remove any surplus forms, reports tables. leave just the two tables
    "compact and repair" the db
    then zip it and post the zipped file as an attachment.
    I will try to look at it today
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2013
    Posts
    6

    Smile

    Hello!

    I have forgot to answer this, but I solved it. What I did was to write

    UPDATE table1, table2

    e.g.
    Update JOMA, Blad1


    instead of only one table..

    Thanks for your help!

Posting Permissions

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