Faster Query

pford

New Member
Joined
Sep 3, 2002
Messages
2
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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.
 

Forum statistics

Threads
1,143,925
Messages
5,721,562
Members
422,372
Latest member
RoyRodger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top