Avatar of casper114
casper114
Flag 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
Microsoft ExcelMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
casper114

8/22/2022 - Mon
telyni19

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.
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
telyni19

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
casper114

ASKER
This resolved it. Thank you very much
Your help has saved me hundreds of hours of internet surfing.
fblack61