Faster Query

[pford]

I need a way to retrieve information from a database more efficiently.

I want to pass a PART# to our database and have it return the DESCRIPTION and UNIT OF MEASURE (UOM).

To make this...
PART# UOM DESCRIPTION
22345
89574
68890

To look like this...
PART# UOM DESCRIPTION
22345 EA BAFFLE CLIP
89574 FT GASKET
68890 LB PAINT

Right now I have setup a query which brings every part number in the database with its description and UOM onto a second worksheet and then use a VLOOKUP to insert the description on UOM onto the first page.

As you can imagine, it is very slow since there are 40,000 parts it has to sift through and my worksheet#1 only has about 200 parts that I need Description and UOM for.

It is an SQL database, but I couldn't get the SQL.REQUEST command to work nor could I see where you could pass it a PART# and have it return UOM and Description.

Thanks,
pford

 

[ChrisM]

Well I'm no expert so there may be a more elegant solution, but see if this works:

set up a loop or for...next to work through your range of part numbers, then pass that number to your sql statement:

example:
for i = 1 to 100
currentpartnumber = worksheet("test").range("a" & i)
SELECT tbl_Partnumber, tbl_PartDescrip FROM 'yourdatabase'.tbl_PartData tbl_PartData WHERE (tbl_Partnumber = " currentpartnumber " )

next i

I left a lot out but hopefully you get the basic idea. Unfortunately this method will have you making quite a few SQL requests to your database. I'm not sure if it would be more helpful to pass multiple part numbers at once.