Excel External Data Connections

casper114
casper114 used Ask the Experts™
on
I currently have an excel spreadsheet used to work up a job profit analysis based upon customers. This was created by somebody else years ago and works fine. I have copied and modified the required tables and SQL queries to duplicate what I want in SQL and to get the data into a table I can just pull from in the spreadsheet. However, once I plug my changes into the sql command text I get unexpected results. Below show the original query, and then the modified query to replace the CustomerID and Address code with the VendorID and vendor Name in my new spreadsheet. However, once this change is input I do not get the same results showing in my spreadsheet as what is returned by the query.

I still get all the fields from the original query, but my 2 changed fields show up as the last 2 returned results instead of closer to the beginning of the results as my Select statement shows they should be located. This has resulted in all automatically returned results being shifted to the left1 to 2 positions (depending on field's place in the select statement) and I can't figure out how to get the fields to populate into my spreadsheet in the same order as they are on the select statement. Can anybody help me figure out what I'm doing wrong?

SELECT JOBANALYSIS.CUSTNMBR, JOBANALYSIS.Service_Call_ID, JOBANALYSIS.ADRSCODE, JOBANALYSIS.DSCVBDT, JOBANALYSIS.DSC_Service_Type, JOBANALYSIS.DSC_BC_VenChgs, JOBANALYSIS.DSC_BC_VenCost, JOBANALYSIS.DSC_BC_VenPrts, JOBANALYSIS.DSC_PV_VenPrts, JOBANALYSIS.EXT_PRICE, JOBANALYSIS.EXT_COST, JOBANALYSIS.FREIGHT_PRICE, JOBANALYSIS.FREIGHT_COST
FROM UCLI1.dbo.JOBANALYSIS JOBANALYSIS

SELECT VENDORID, Service_Call_ID, VENDNAME, DSCVBDT, DSC_Service_Type, DSC_BC_VenChgs, DSC_BC_VenCost, DSC_BC_VenPrts, DSC_PV_VenPrts, EXT_PRICE, EXT_COST, FREIGHT_PRICE, FREIGHT_COST
FROM UCLI1.dbo.JOBANALYSIS_VENDOR
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you run your changed query manually in your database, and do you get the same results then? Can you look at a temporary copy of your query in the query design view, and are all the columns there still in your desired order? Queries are supposed to return their columns in the order you specify.

Author

Commented:
If I run the query in SQL management studio I get exactly what I would expect. However, excel is throwing me for a loop since It's not returning the data in the same order it is in the query.
If you're using Excel's data connection feature, try looking at the External Data Properties window and unchecking the "Preserve column sort/filter/layout" option.

Here's an article that mentions this and shows a screenshot, although that may not be of the same Excel version as you: http://www.dailydoseofexcel.com/archives/2004/06/02/add-new-column-to-external-data/

Author

Commented:
This resolved it. Thank you very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial