SQL Query to auto-populate excel fields 1

[mjscrmpm]

i need to create an "active" excel spreadsheet.

basically one would punch in an item number in column A and if it matches a valid item number in our database, then columns B-F would get auto-populated with the correct corresponding data (from other database fields). here is an image that may better explain this.

Row 2 is an example of what this sheet would look like. Row 3 is an explanation of what i need to accomplish.

I would greatly appeciate some help on this one!

 

[SkipVought]

Hi,

This is know as a parameter query, where you supply one or more parameters.

Use MS Query via Data/Get External Data/New Database Query -- YOU DATABASE DRIVER...

You pick the table or tables you need to query and Edit the Query in the QBE grid.

Set up your criteria for item_no, but instead of a valid value, enter [What Item no] instead. Now when you run the query, it will prompt you for a valid value.

Edit/Return Data to Excel.

In Data/Parameters, choose the option button for Get the value from the following cell, and reference the cell containing your item_no. Check the box Refresh automatically when cell value changes if you want.

Skip,
[sub]
[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking. [/sub]

 

[mjscrmpm]

thanks a ton skip, worked great!

one last question though. do you know of anyway that each line could be a different item number? i'd like multiple item no's on the spreadsheet.

 

[mjscrmpm]

^^^sorry for above, i figured it out^^^

i simply created more parameters and assigned them to get values from A2, A3, etc.

 

[00trav]

First off, thanks for this post, it has helped me out a lot. But how did you figure out how to do multiple lines. I have a very similar table with unit prices and descriptions that i would like this to work with.

also is there a way that the query does not return the field names at the top of each column.