I used the Get External Data function within Excel and the query returned the fields in alphabetical order. I went back into the query and rearranged the fields into a more logical order, but when refreshed the query continues to return the fields to Excel in the original alphabetical order.
Am I missing something, or do you only get one shot at the order of the fields being returned?
I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
I would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2
WHERE 2.ITMNR IN ?
I have a worksheet that pulls data from an ODBC datasource (import, External Data). The datasource is a SQL server 2005 database.
the query's sql is complex enough that it says it cannot be represented graphicly.
part of the WHERE clause fro my query specifies a date range for one of the date fields.
I am wondering if from within the SQL query in Microsoft Query I can reference the value of a cell. that way i coudl have the user enter his date range values in two specific cells.
Using Excel 2013. Is it possible to delete some records from Sheet1 if the records exist on Sheet2?
I tried several SQL variations in MSQuery but all come back with error messages.
I tried:
[Code].....
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.
View 3 Replies View RelatedIm using a query to connect to a SQL server and return data into Excel. I can query and return the data in Microsoft Query editor but when I attempt to return the data to Excel in a table, it just says the name of the connection in cell A1. If I attempt to return it into a pivot table a get and "Problems obtaining data" notification.
View 1 Replies View RelatedTrying to work through tutorial on creating offline cube from an Excel file, A CompleteGuide to PivotTables: A Visual Approach, Cornell, ISBN 1-59059-432-0, pg 248. Steps I am taking:
1. Click on Data Menu
2. Import External Data
3. New Database Query
On Databases tab
4. Click on Excel Files*
5. Click on Browse Button
I migrate to the loction of the sample files I downloaded, but no files appear in the Browse Data Sources dialog window. Files of type drop down reveals only "Data Sources" (w/o quotes). I browsed the folder with Windows Explorer. There are several Excel files in the folder as well as sample, .cub and .mdb files. I do have Excel 2007 installed on the PC as well, but working through the tutroail with Excel 2003.
Is there a way to put a floating date range in the criteria much the same as can be done in excel ie greater than Today() but less than Today()+3? I don't want to keep going in and adjusting the query if I don't have to and if I don't keep the range tight there is too much data.
View 9 Replies View RelatedI don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
I am importing data that contains specific start dates and I was wondering how to filter the criteria in Microsoft query to only show start dates greater than today or perhaps yesterday. This would eliminate all entries that have already occurred. It seems I can only select a date in the criteria that exists in the data.
View 14 Replies View RelatedI need a column to autopopulate based on quantities of title entered by a user.
Each row has a Type (Column A) and a Sequence # (Column B), and I can get as far validating if a given row has same Type and Sequence# <= the sum of the quantities.
Now I'm struggling with counting down through the quantity of each title.
Each title should exhaust itself before moving on.
Example:
COUNTIF Sequencing.xlsx‎
I do not have any information about exel but there is an urgent need to program.The numbers on a roulette wheel automatically want to calculate the distance between. There are 37 numbers on the roulette wheel and Sequences matter of form
0 26 3 35 12 28 7 29 18 22 9 31 14 20 1 33 16 24 5 10 23 8 30 11 36 13 27 6 34 17 25 2 21 4 19 15 32 0
[code]....
I'm trying to sequence some information in column(E). However if a cell is blank(B) then a blank cell appears and the sequenced number is placed in the following row of the column. e.g:.........
View 2 Replies View RelatedI am working on a data integration and have transactional data sets with multiple columns of data. In reviewing the data, I can see that based on values in 2 different columns, I can identify unique transaction records. The data is already sorder by transaction # and date. Creating a pivot table gives me back summary info for header records (I can get unique header records based on a concatenation of the trx # and Date), but I am having a difficulty obtaining uniqueness for the line items. The id that I created for purposes of header record summation means nothing from a user standpoint and I need to come up with more meaningful info.
What I have and what I want is...:
Tranaction#DateWhat I want is…15801911/23/2005015801911/23/2005015801911/23/2005015801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801912/12/2005215801912/12/20052
If I can get the data defined like that, then I can concatenate the trx# and the increment to block out the transactions into individual transactions.
I have a non Microsoft program file that I can open in excel. I usually make changes to the file; save it to its native format, then open up the non Microsoft program and open that file that I just edited in excel.
If I had to simplify it, using notepad as an example.
I open the TXT in excel and make changes.
Then Save the TXT in a folder location.
Open Notepad.
Browse to the folder Location.
Open the edited file.
I did not know if there was a quick VBA that I could use to get this to work?
I have a database of customers in sheet1 and can search colA by using .find command and find part of text string entered in textbox1, results are filtered and shown in listbox1. my problem is the listbox is only showing 9 columns and i need 12.
i think this is because i am using the additem command.
can anyone suggest a way around this or alternative coding method??
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a2", Range("f65536").End(xlUp))
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind & "*"......................
How to define column in MS Query (ODBC)?
i have one column like Date of purchase. what i want is to define column based on date of purchase the results showing month of the date of purchase
1. When I query it grabs the column name and then the value I am looking for, how do I just grab the value?
2. How can I have the query return the results in the order that they are obtained? For example, lets say I query 3 data points. The first is 1, second is 2, and 3. But when they are put on the excel SS they are in the order 3, 2, 1.
3. In addition to the table I am grabbing below, I have a second table I want to query. The table name is two words, and last time I tried to query it, it did not work.
Sub proSQLQuery1()
Dim varConnection
Dim varSQL
For i = 0 To 1
varConnection = "ODBC; DSN=MS Access Database;DBQ=C:Documents and SettingskbreslinDesktopLydia.mdb; Driver={Driver do Microsoft Access (*.mdb)}"
varSQL = "SELECT VitalStats.birth_date FROM VitalStats WHERE VitalStats.Cases_ID='" & Cells(1 + i, 1).Value & "'"
With ActiveSheet.QueryTables.Add(varConnection, Worksheets("Temp Hold").Cells(2, 4))
.CommandText = varSQL
.Refresh BackgroundQuery:=False
End With
Next i
End Sub
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
I have simplified this spreadsheet from the original to get to the core of what I want it to do. There are three tabs: 1) tab:"query fields" is an area that places data that is retrieved from the web, in this case it retrieves tables from yahoo finance given a ticker input. The ticker input cell is currently on the "score card" tab. 2) tab:"score card" in this tab you can input a ticker symbol in the indicated box and it will populate the "query fields" with corresponding data. This tab also extracts certain fields from the retrieved tables and performs a pass/fail test then produces a score %
This is great if I'm doing one symbol at a time, however I eventually would like to 'loop' this process such that a score field is populated for a list of tickers, hence tab 3) "list". Ideally I'd create a control button to initiate the web retrieval process and it would go down my list, retrieve the table data, perform the pass/fail tests, then populate the corresponding score % in the cell next to the ticker symbols. I know I can keep running separate web queries for specific symbols, but I guess my secondary goal is to have this be as dynamic as possible, since the ticker list will change and the list could be sizable at times.
CSH- web query.xlsx
I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...
say if i select in B5 as ID then D5 value should show me India
******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
In access you can create a crosstab query grouping by more than one field/column and totaling on more than 1 field/column
ie Goup by Acc No then Cost Centre
Sum on costs by date -up to 31
I am working on MS-Query in Excel.
Selecting few datetime columns by using ms query but it returns that columns data in wrong format.
And CONVERT function is also not working in ms-query.
Below is the MS-Query:
SELECT `Consolidated$`.Payroll_ID, `Consolidated$`.Full_Name, `Consolidated$`.`Current Designation`, `Consolidated$`.`Date of Joining`, `Consolidated$`.Department,
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
This is where I am stuck ....
EmployeeTimeSheet_2012.xlsm
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!