How to simulate VLOOKUP() in Access?

[EagleOne]

2003

In Excel, (In table1 Access) Excel w/s I have concatenated 6 columns of data into another cell in
that record.

I wish to match or lookup that value in sheet2 (table2 Access). The problem is that I may have as
many as 2,000,000 records. Of course Excel could not handle that.

What am I missing, if I feel that I do not want to run a query 2,000,000 times looking for matches.

Therefore, this can not be as difficult as I am assuming.

Thoughts please!

TIA EagleOne

 

[Jeff Boyce]

In Excel, you are probably using VLOOKUP to find a matching value and a
corresponding "looked-up value".

In Access, you use a query to join two tables, one with the "matching value"
as part of the record and a second table that is your "lookup table". Use
the query, not the tables directly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clif McIrvin]

2003

Version is good to know!

You really haven't given us much information to work with here, so I'll
ask a few questions .... read all the way down to get all of them.


Worksheet data is not normalized, and unless pains are taken when
importing or linking that data into Access the resultant table is still
not normalized. Attempting to force Access to work with non-normalized
data cripples it's power and capabilities.

Perhaps the most important question here is: Have you considered moving
this data into a properly normalized database?


Are these linked tables? That is, does the data actually "live" in
Excel, not Access?

Is the data dynamic (more or less constantly being revised) or static?

In Excel, (In table1 Access) Excel w/s I have concatenated 6 columns
of data into another cell in
that record.

This sounds like this w/s serves a user interface "parameter entry"
purpose. If so, an Access Form is a good candidate to get your lookup
criteria from the user.

I wish to match or lookup that value in sheet2 (table2 Access).

Queries, Filters, SQL WHERE clauses or Relationships all do matching.
Which one you choose depends on your data structure and what you are
attempting to do.

 

[Clif McIrvin]

A couple thoughts to add to my previous post (below.)

:::::
From a post by Pete D.:
It sounds like your trying to use Access as a multi page spreadsheet.
You
might read up on relational database before starting. Here is some
reading
material.

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
http://www.allenbrowne.com/casu-06.html

and:

:::::::::::::::::::::::::::::::::::::::
(Thanks to John W. Vinson [MVP] for this info

Access has a steeper learning curve than (say) Word or
Excel; not least, in order to make productive use of the program you
have to
understand the theoretical basis of database design - a concept called
"Normalization". It is very logical and not at all difficult once you
get the
concepts down.

Here are some tutorials and other resources that I hope you will find
helpful:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[EagleOne]

Thanks all for your responses.

Logically, I know that Access must have an answer for large-record matching analysis.

The challenge I see is that I for sure can do a query 2,000,000 times to find matches in Tables1 vs
Table2.

Even saying that to myself, forces me to think that I am missing something obvious.

At this point all is in Excel. The data comes from a 2 main frames and is queried monthly and the
results compared in the PC world. Sounds insane, but the main framers do not seem to have the
ability to do the compare using the MF.

So my choice seems to be 2,000,000 RAM calculations (VLOOKUP()) which will kill the PC or 2,000,000
queries which will kill the PC and the User.

Really, I am not meaning to be cryptic or flip. That said, what am I missing or not considering? Is
this really possible in Access without having to run a query 2,000,000 times? Can the query be run
to loop through the records leaving an indicator as to a successful find?

TIA EagleOne

 

[EagleOne]

Thanks for the references!

A couple thoughts to add to my previous post (below.)

:::::
From a post by Pete D.:
It sounds like your trying to use Access as a multi page spreadsheet.
You
might read up on relational database before starting. Here is some
reading
material.

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
http://www.allenbrowne.com/casu-06.html

and:

:::::::::::::::::::::::::::::::::::::::
(Thanks to John W. Vinson [MVP] for this info

Access has a steeper learning curve than (say) Word or
Excel; not least, in order to make productive use of the program you
have to
understand the theoretical basis of database design - a concept called
"Normalization". It is very logical and not at all difficult once you
get the
concepts down.

Here are some tutorials and other resources that I hope you will find
helpful:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[John W. Vinson/MVP]

The challenge I see is that I for sure can do a query 2,000,000 times to find matches in Tables1 vs
Table2.

Even saying that to myself, forces me to think that I am missing something obvious.

At this point all is in Excel. The data comes from a 2 main frames and is queried monthly and the
results compared in the PC world. Sounds insane, but the main framers do not seem to have the
ability to do the compare using the MF.

So my choice seems to be 2,000,000 RAM calculations (VLOOKUP()) which will kill the PC or 2,000,000
queries which will kill the PC and the User.

Really, I am not meaning to be cryptic or flip. That said, what am I missing or not considering? Is
this really possible in Access without having to run a query 2,000,000 times? Can the query be run
to loop through the records leaving an indicator as to a successful find?

Yes.

Access is a relational database programming environment. This is what
relational databases DO! You need *one query*.

Let's say you have a field named ID that is in common between the two
tables. Import the spreadsheets into Access tables. In the table
design, put an Index on ID - a unique index if the ID will appear once
and only once in the table, a non-unique index if there may be many
records with that ID.

Create a Query. Add both tables to the query grid. Join them on ID (by
dragging ID from one table to ID on the other). Select whichever
fields (columns) you want to see from the two tables.

Open the query. You will see all those records where ID matches.

If you need to join on two - or ten - columns, you can do that too.

 

[Clif McIrvin]

Thanks for the references!

You're welcome. As you noticed, I simply passed along quotes from
others that I believed useful (I confess -- I've not found time to study
them all for my own benefit yet! <grin>)

 

[EagleOne]

How simple and elegant! I knew that I was suffering from a bad case of the DA's!

I was just thinking of the Db as only a repository of data. The magic is the Join!

Thanks!

 

[John W. Vinson/MVP]

How simple and elegant! I knew that I was suffering from a bad case of the DA's!

I was just thinking of the Db as only a repository of data. The magic is the Join!

Not sure what DA's might be - I'd have diagnosed a case of
Spreadsheetitis, a very common ailment <g>.

Learning relational database concepts is a very important part of
learning to use Access. It's easy to confuse a table datasheet with a
"spreadsheet on steroids", but it emphatically ISN'T; it requires a
different mindset, and can actually be harder to learn to use well if
you're already expert in Excel - because there are things you need to
"unlearn".