Formatting Data being extracted from SQL D/B

S

Steven

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;
 
N

NickHK

Steven,
You can certainly query DBs from Excel. Check out Data>Get External Data>New
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automation of report, daily, monthly then yearly as per need 1
Array.BinarySearch ALWAYS NEGETIVE 3
Return US State Name or Territory from USPS Abbrevation - an example 3
validation rule in query? 4
converting a for next loop into a for each loop 2
moving State Data 2
Query of Expired Docs 3
IF - THEN Statement 3

Top