Formatting Data being extracted from SQL D/B
 

Hi,

I currently have an excel based application that will extract a whole
load of data from our Databeses which I'm then having to filter
additonally by going back into SQL.

Is there anyway I can incoporate this SQL query into the excel app?

Many thanks in advance.

SELECT Multi_J_Data.[Period Start]
, Multi_J_Data.[Period End]
, Multi_J_Data.SURNAME
, Multi_J_Data.[FIRST NAME]
, Multi_J_Data.[Withholding State]
, Multi_J_Data.[Resident State]
, Multi_J_Data.[Worked State]
, Multi_J_Data.[Worked State Hours]
, Multi_J_Data.[Total Worked Hours]
FROM Multi_J_Data
WHERE (((Multi_J_Data.[Withholding State])<[Worked State])
AND ((Multi_J_Data.[Worked State])<'DC'
And (Multi_J_Data.[Worked State])<'NON-USA-FOREIGN'
And (Multi_J_Data.[Worked State])<'TX'
And (Multi_J_Data.[Worked State])<'SD'
And (Multi_J_Data.[Worked State])<'TN'
And (Multi_J_Data.[Worked State])<'WA'
And (Multi_J_Data.[Worked State])<'WY'
And (Multi_J_Data.[Worked State])<'CA'
And (Multi_J_Data.[Worked State])<'CO'
And (Multi_J_Data.[Worked State])<'PA'
And (Multi_J_Data.[Worked State])<'IL'
And (Multi_J_Data.[Worked State])<'NJ'
And (Multi_J_Data.[Worked State])<'IN'
And (Multi_J_Data.[Worked State])<'IA'
And (Multi_J_Data.[Worked State])<'KY'
And (Multi_J_Data.[Worked State])<'MD'
And (Multi_J_Data.[Worked State])<'MI'
And (Multi_J_Data.[Worked State])<'MN'
And (Multi_J_Data.[Worked State])<'MT'
And (Multi_J_Data.[Worked State])<'ND'
And (Multi_J_Data.[Worked State])<'OH'
And (Multi_J_Data.[Worked State])<'RI'
And (Multi_J_Data.[Worked State])<'VA'
And (Multi_J_Data.[Worked State])<'WV'
And (Multi_J_Data.[Worked State])<'WI')
AND ((Multi_J_Data.[Worked State Hours])200)
AND ((([Multi_J_Data].[Worked State])<'NJ')<'AZ')) OR
(((Multi_J_Data.[Withholding State])<'CA')
AND ((Multi_J_Data.[Worked State])='CA')) OR
(((Multi_J_Data.[Withholding State])<'CO')
AND ((Multi_J_Data.[Worked State])='CO')) OR
(((Multi_J_Data.[Withholding State])<'NY')
AND ((Multi_J_Data.[Worked State])='NY')
AND ((Multi_J_Data.[Worked State Hours])100)) OR
(((Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'NJ')
AND ((Multi_J_Data.[Worked State])='NJ')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'NJ'
And (Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'MD'
And (Multi_J_Data.[Withholding State])<'IN')
AND ((Multi_J_Data.[Worked State])='PA')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'CA'
And (Multi_J_Data.[Withholding State])<'IN'
And (Multi_J_Data.[Withholding State])<'OR'
And (Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'AZ')
AND ((Multi_J_Data.[Worked State])='AZ')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'IL'
And (Multi_J_Data.[Withholding State])<'WI'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'IA')
AND ((Multi_J_Data.[Worked State])='IL')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'IN'
And (Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'WI')
AND ((Multi_J_Data.[Worked State])='IN')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'IA'
And (Multi_J_Data.[Withholding State])<'IL')
AND ((Multi_J_Data.[Worked State])='IA')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'IL'
And (Multi_J_Data.[Withholding State])<'IN'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<' WI')
AND ((Multi_J_Data.[Worked State])='KY')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'MD'
And (Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'FL')
AND ((Multi_J_Data.[Worked State])='MD')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'WI'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'MN'
And (Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'IN'
And (Multi_J_Data.[Withholding State])<'IL')
AND ((Multi_J_Data.[Worked State])='MI')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'MN'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'ND'
And (Multi_J_Data.[Withholding State])<'WI')
AND ((Multi_J_Data.[Worked State])='MN')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'MT'
And (Multi_J_Data.[Withholding State])<'ND')
AND ((Multi_J_Data.[Worked State])='MT')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'ND'
And (Multi_J_Data.[Withholding State])<'MN'
And (Multi_J_Data.[Withholding State])<'MT')
AND ((Multi_J_Data.[Worked State])='ND')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'IN')
AND ((Multi_J_Data.[Worked State])='OH')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'RI'
And (Multi_J_Data.[Withholding State])<'MI'
And (Multi_J_Data.[Withholding State])<'MN'
And (Multi_J_Data.[Withholding State])<'MT'
And (Multi_J_Data.[Withholding State])<'NE'
And (Multi_J_Data.[Withholding State])<'NH'
And (Multi_J_Data.[Withholding State])<'NJ'
And (Multi_J_Data.[Withholding State])<'NM'
And (Multi_J_Data.[Withholding State])<'NY'
And (Multi_J_Data.[Withholding State])<'NC'
And (Multi_J_Data.[Withholding State])<'ND'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'OR'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'SD'
And (Multi_J_Data.[Withholding State])<'TN'
And (Multi_J_Data.[Withholding State])<'TX'
And (Multi_J_Data.[Withholding State])<'UT'
And (Multi_J_Data.[Withholding State])<'VT'
And (Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'WA'
And (Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<'WI'
And (Multi_J_Data.[Withholding State])<'WY')
AND ((Multi_J_Data.[Worked State])='RI')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'VA'
And (Multi_J_Data.[Withholding State])<'FL'
And (Multi_J_Data.[Withholding State])<'MD'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'WV')
AND ((Multi_J_Data.[Worked State])='VA')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'WV'
And (Multi_J_Data.[Withholding State])<'KY'
And (Multi_J_Data.[Withholding State])<'MD'
And (Multi_J_Data.[Withholding State])<'OH'
And (Multi_J_Data.[Withholding State])<'OR'
And (Multi_J_Data.[Withholding State])<'PA'
And (Multi_J_Data.[Withholding State])<'VA')
AND ((Multi_J_Data.[Worked State])='WV')
AND ((Multi_J_Data.[Worked State Hours])200)) OR
(((Multi_J_Data.[Withholding State])<'WI'
And (Multi_J_Data.[Withholding State])<'MN')
AND ((Multi_J_Data.[Worked State])='WI')
AND ((Multi_J_Data.[Worked State Hours])200))
ORDER BY Multi_J_Data.SURNAME;

Formatting Data being extracted from SQL D/B
 

Steven,
You can certainly query DBs from Excel. Check out DataGet External DataNew
Database Query.
Whether Excel/MsQuery/<Your DB driver can handle that monster is another
question.

Why not create a stored procedure or query in your DB, then ADO you can set
the parameters and create a recordset etc.

As for SQL, could you not simplify with something like:
..... WHERE (Multi_J_Data.[Worked State]) NOT IN (SELECT {However your decide
not these])
....etc

But it depends somewhat on what you are currently doing to get the data into
Excel.

NickHK

"Steven" wrote in message
ups.com...
Hi,

I currently have an excel based application that will extract a whole
load of data from our Databeses which I'm then having to filter
additonally by going back into SQL.

Is there anyway I can incoporate this SQL query into the excel app?

Many thanks in advance.

SELECT Multi_J_Data.[Period Start]
, Multi_J_Data.[Period End]
, Multi_J_Data.SURNAME
, Multi_J_Data.[FIRST NAME]
, Multi_J_Data.[Withholding State]
, Multi_J_Data.[Resident State]
------------ CUT ---------