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

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 :-)

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

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

If this is not posiblle then you need to add a "WHERE" clause into your
SQL to pull only the parts you are looking for. If you are doing a
query the "WHERE" is the command portion of the SQL. The Query Wizard
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 modify
the recorded macro to add the addition items. If you record the macro I
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 can
use a "Query Update" event to process the returned data moving only the
items you need to a different worksheet.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151847

Microsoft Office Help

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

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" wrote:

Is what I am trying to do even possible? HELP :-)

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

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" wrote:

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" wrote:

Is what I am trying to do even possible? HELP :-)

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

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

"Lorina" wrote:

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