Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Access Data via Excel (Excel 2003 (others))

    Hi all,
    I have an Access database with 205,343 records that I'd like to set up for querying through Excel. I know Excel pretty well but haven't used the external data query feature so need some assistance. My goal is to fetch data in the Access database based on a a lookup on two fields in several Excel worksheets. Example:

    <pre>Excel fields:
    City State Code1 Code2 LCode
    A B c1 c2 ? (find match in Access)</pre>

    <pre>Access fields
    Code1 Code2 LCode
    c1 c2 L111
    c1 c4 L123</pre>

    I want to fetch the LCode from Access based on the Code1+Code2 matched from the Excel record. I have about 70k of these I need to do (they're spread thru several worksheets). In this example I'd return "L111" since Code1/Code2 match and store that value in the corresponding cell in Excel.

    If the database could fit in Excel, this would be a trivial lookup, but what are my choices for doing it with Access as the lookup table? Is there someway to write a formula to do the lookup? I can write a query for each Code1/Code2 but that's not practical at all, of course so there's gotta be some other easy way I don't know about.

    Thnx,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Access Data via Excel (Excel 2003 (others))

    This is a very awkward way to combine Access and Excel. As it is now, you'd probably have to query for each value separately, but with 70K Excel records that will be a nightmare to design, and performance will be atrocious. It would be easier if you could import or link the Excel data in Access, and build a query there.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Access Data via Excel (Excel 2003 (others))

    Ok, I'm working on your suggestion. After a few hours with Access I'm not making much progress (being that this is the first time I've spent more than 5 min with Access I'm sure it's operator error). I tried linking my Excel tables (which have named ranges assigned) as a table in Access. This works fine but when I create a query between these and the master table it fails because of type mismatch. All the Excel fields are numbers and formatted as such but when I create a linked table in Access they all come over as Text (and Access won't let me change their type). The query then fails because the master table is comparing Integer fields with text fields.

    Should I redefine the master table fields to be text instead (the numbers I'm looking up are always three digits)? It's frustrating because all I need is a simple SQL such as:

    SELECT table1.code1, table1.code2, table2.codeL
    FROM table1, table2
    WHERE table1.code1=table2.code1 and table1.code2=table2.code2

    Thnx,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Access Data via Excel (Excel 2003 (others))

    Access determines the data type of a field on the basis of the first record(s). If a particular field is blank in the first records, it may be interpreted as text, even if all non-blank values are numeric. Workarounds:
    - Insert a dummy top row in Excel and populate it with the correct data types, or
    - Create a blank table in Access with the correct structure, and import the data from Excel instead of linking them.

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Access Data via Excel (Excel 2003 (others))

    I ended up re-defining the fields for the Access database and then relinking. My Excel table didn't have blank rows and the fields were defined appropriately so I'm not sure what happened - but it's working now.

    My NEW problem is more Access related so I should probably close this question and open a new one in the Access forum. When I build the query to fetch the LCode, I get ~6000 more records than the source table has. When I look at the SQL it has some INNER JOIN stuff going on I don't know why. Even when I edit the SQL manually it still picks up more records. Anyway I'll repost this new question to the Access forum.

    Due to my time constrainst I may just manually edit the original 195K line .csv file and break it into 60K chunks for Excel to use. I'd be done long, long ago if I had done that earlier but I wanted a solution that others could use if the source database (the 195K records) ever got updated (it was one we purchased from a marketing research company not built on our own).

    Thnx, Deb

Posting Permissions

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