Access DB & Excel

[Bob]

I have a spreadsheet that contains a part number,
description, and price for parts. Essentially it's a
customer quote.

In an MS Access database ( which has 78K rows, too large
to import into excel ) resides the same information, but
along with it an "internal" part number.

What I need to do is this : read the list of part numbers
in excel, look them up in the database, and return me
the "internal" part number, placing it in a column next to
the "external" part number in the spreadsheet.

Something like a vlookup command but from a Access
database instead of another spreadsheet. Any ideas ?

Bob

 

[Larry Wallis]

Hi Bob

May I suggest you stand on your head! You appear to want
to do a fair bit of data manipulation, therefore I think
you may be better off using Access rather than Excel.

In other words think along these lines:

export your Excel data into Access as a new table (or at
least the part number bits you want to play with).

Now you have everything in Access you can use a variety of
Queries on your original data tables combined with your
new table (you may have to create relationships). You
could then export the final result of the Query back into
Excel.

Further thought would also lead me to suggest you move
completely to Access. Rule number one for data
storage...never record the same thing more than once! You
don't really want Access and Excel doing similar things.
Anything Excel can do Access can do better (before the
1000's of protests, Excel is much easier to set up quickly
and is better for "higher maths/equations" type
stuff...I'm only being controversial as well!). You can
generate a flash looking customer quote in the Reporting
section of Access as well. Consider tables of customer
data, parts data, invoice information, costs in and
out...it could even generate a tax return for income tax
and VAT...and make a cup of tea mid morning!

Hope this is food for thought.

Larry