Link to home
Start Free TrialLog in
Avatar of casper114
casper114Flag for United States of America

asked on

Excel External Data Connections

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
Avatar of telyni19
telyni19
Flag of United States of America image

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.
Avatar of casper114

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of telyni19
telyni19
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This resolved it. Thank you very much