Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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; |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
data entered in each coloumns of one sheet to be extracted with r. | Excel Discussion (Misc queries) | |||
Format Extracted Data from Pivot Table | Excel Discussion (Misc queries) | |||
how can the necessary information be extracted? | Excel Discussion (Misc queries) | |||
EXcel, create a list from extracted data, Conditional | Excel Programming |