Excel lookup in Microsoft Access

[Sandy25]

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.

 

[kskinne]

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

 

[Sandy25]

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

 

[kskinne]

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

 

[Sandy25]

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??

 

[PaddyD]

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

Paddy

 

[tusharm]

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.

 

[PaddyD]

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