Results 1 to 8 of 8

Excel lookup in Microsoft Access

This is a discussion on Excel lookup in Microsoft Access within the Excel Questions forums, part of the Question Forums category; I want to do a lookup of values in a table and the table is in access. Normally I can ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    35

    Default

    I want to do a lookup of values in a table and the table is in access. Normally I can import or copy the table into excel but the table is 170,000 rows and growing and it won't fit in Excel. Can I lookup values in an Access table using excel and if so, how do I do it? Thanks.

  2. #2
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default

    what you could do is run a query on the database from excel.
    1. go to the data menu and select 'get external data'
    2. select 'new database query'
    3. from the list of database types, select 'ms access database'
    4. browse for and select your database file

    FROM THIS POINT ON, THE PROCESS IS WIZARD-DRIVEN

    5. select the database table columns you want to include in your query and select 'next'
    6. select filters so that only the data you want to see appears in your spreadsheet and select 'next'
    7. if you want to, sort your data, then select 'next'
    8. click the 'save query' button and save your query
    9. select 'return the data to microsoft excel' and click the 'finish' button
    10. choose where you want to place the query data and press 'ok'.

    hope this helps you, good luck

    kevin

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    35

    Default

    Won't I still run into the same problem of being too big (170,000 rows)?

  4. #4
    Board Regular kskinne's Avatar
    Join Date
    Feb 2002
    Location
    USA
    Posts
    1,263

    Default

    depends on how much data you want to pull in the query - if you run a query that returns over 65,536 rows of data, then you are right - it's going to be too big. but i was assuming that when you said you needed to "lookup" data from your data table, you meant that you wanted to look for certain criteria and only return the data that met that criteria. if this is what you want to do, i still think query would work for you, unless, as i stated before, your query results are larger then the excel spreadsheet can hold. keep in mind that if you use query to lookup data from your table, you don't need to have the table in excel.

    btw, are you familiar with using query in excel, because you can really do much more with it than what i have briefly described to you in these posts.

    good luck,
    kevin

  5. #5
    New Member
    Join Date
    Jul 2002
    Posts
    35

    Default

    Yes I have done queries in excel. The problem with the query is that I am doing a lot of lookups and my criteria change all the time. For this particular need, I would have to be changing the query criteria all the time. Any other ideas??

  6. #6
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,946

    Default

    Why not just do the query in access & then pick up the results in excel?

    Paddy

  7. #7
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,747

    Default

    On 2002-08-07 13:36, Sandy25 wrote:
    Yes I have done queries in excel. The problem with the query is that I am doing a lot of lookups and my criteria change all the time. For this particular need, I would have to be changing the query criteria all the time. Any other ideas??
    I'm not sure what Paddy meant, but it is possible to parameterize a query in MS Query, or even in Access. In this case, Query/XL will coordinate and ask you if you want to use a fixed value, provide a value each time you ask for a refresh, or if you want to put the value in a particular cell. If you pick the last (and if I remember correctly), you can specify an automatic lookup whenever the cell value changes.


  8. #8
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,946

    Default

    Re-read post & see ambiguity! What I meant was that if you are:

    a) familiar with writing queries
    b) dealing with relatively large access tables
    c) wanting to run parameterised queries on those tables

    you may as well write the queries in access & then bring the results into excel once the queries are done. You could still set up the 'automatice updates etc' as per Tushar's post, but would save youself at least one call from excel to access...

    Paddy

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com