what is the best way to pull in external data from SQL table

[Lorina]

I want to pull in information from our ERP system into excel so I can combine
it will other information (that is not in our ERP system) and I want to be
able to refresh it.

The data I need is on our parts table (1000's of records) but I only need
approx 100 of those for th report.

Everything I have tried so far pulls in all of the records. I find the
ones I need and link it to a separate sheet that has the other data. I also
do a calculation with the data I pull in (qty on hand, order, demand to come
up with ATP. ATP is the value I link to the other sheet). Everything works
fine...until we add a new part in the ERP. Once that happens and I refresh
because it's looking for the line (which now has a different part on it)

So -is there a way to keep out any new items? I tried deleting the parts I
didn't need but when I refresh they all come back in.

Is what I am trying to do even possible? HELP

 

[joel]

I would think the BEST way is to add a new column into the sourc
database which indicates the specific part you are looking for. The
filter on the new column to get your specific parts.

If this is not posiblle then you need to add a "WHERE" clause into you
SQL to pull only the parts you are looking for. If you are doing
query the "WHERE" is the command portion of the SQL. The Query Wizar
can enter a "Where" clause but not when it is 100 itmes.

If you want a query I would recommend recording a macro and then modif
the recorded macro to add the addition items. If you record the macro
can make the modifications to add more fields into the where clause.

Another method would to be retrieve all the data in a query. You ca
use a "Query Update" event to process the returned data moving only th
items you need to a different worksheet

 

[berniean]

Without knowing which ERP system you're using, or the method for data
extraction, it's difficult to say. Are you using an SQL statement to extract
the data? If so, you should be able to include a Where clause to restrict the
dynaset. If you are exporting directly, is there no way, within the ERP
system to filter the results? Does your system support ODBC? If so, you might
be able to create a query to extract those records directly into Excel. The
easiest method is to use some form of data query software to extract the data
from the ERP, then use MS Access or MS Query to filter for your part list. I
work with Oracle and use Oracle Discoverer to extract records and a
combination of Access and Excel to create reports. Does your company have a
database administrator? They may have a better answer for you.

 

[Lorina]

Sadly the administrator is me! We are a small company and have to wear many
hats. You have given me some ideas though. I will try the access route.

Thanks

 

[berniean]

I'd be very interested in knowing the results. I hope you meet with success!