Microsoft Query Multiple Parameters

Mar 12, 2014

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 ?

View 4 Replies


ADVERTISEMENT

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

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

View 2 Replies View Related

Returning Data From Microsoft Query

Sep 7, 2009

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;

View 2 Replies View Related

Column Sequencing Using Microsoft Query?

Sep 7, 2004

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?

View 2 Replies View Related

Referencing Cells From Microsoft Query

Jan 26, 2009

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.

View 9 Replies View Related

MS Query With Parameters

May 22, 2007

Is there any way to use an Excel cell as a parameter for MS Query.

i.e. Select fields from table where field = "cell value"

I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.

View 9 Replies View Related

MS Query Parameters

Feb 4, 2009

I have a query that has criteria based on "is one of". I would like to pass this off to a parameter selected from a list box where the user could select multiple items. Is something like this possible?

View 9 Replies View Related

Excel 2013 :: Delete Using Microsoft Query?

Apr 8, 2014

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

View 3 Replies View Related

Returning Data From Microsoft Query To Excel?

Jun 23, 2013

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.

View 3 Replies View Related

Microsoft Query - Return All Unless A Parameter Is Entered

Aug 20, 2009

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 Related

No Data Returned To Excel From Microsoft Query?

Sep 25, 2013

Im 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 Related

Microsoft Query: Data Source .xls Files

Mar 23, 2008

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

View 4 Replies View Related

Excel2003 :: VBA To SQL Query With Parameters

Aug 20, 2013

I am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.

I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.

I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'

Code:

Sub DateQuery()
With ActiveWorksheet.connections("ndserver01").oledbconnection
' .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
.Refresh BackgroundQuery:=True
.CommandType = xlCmdSql

[Code]...

What have I done wrong

View 1 Replies View Related

Web Query With Hidden Parameters

Aug 12, 2008

The web site is http://scores.covers.com/college-foo...chups.aspx?t=0. This website defaults to a Top 25 scoreboard. I would like to access the Full scoreboard instead. I've looked inside the web site source code for the parameter names, but using the most likely candidates in web queries pull in the Top 25 scoreboard instead. The web site name does not change when scoreboards are changed.

View 9 Replies View Related

Microsoft Query :: Put Floating Date Range In Criteria

Jun 21, 2007

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 Related

Excel 2013 :: Creating Two Tables That Can Reference With Microsoft-query

Jun 28, 2013

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

View 3 Replies View Related

Query Parameters: Editing The Middle Of A String

Jan 3, 2007

My question is in regards to query and setting up parameters. I have the following query (I am using an example for security purposes)

http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA21&mode=New&rfilter=****&afilter=A&exact=False" _

What I am trying to do is have a parameter for the user to change the AA21. For example the user could type AA33 and it would change the code to:

http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA33&mode=New&rfilter=****&afilter=A&exact=False" _

I am familiar with creating parameters and have been able to add things to the end of the string but not in the middle. I have been trying a very long time to figure this out.

View 6 Replies View Related

Microsoft Query :: Show Start Dates Greater Than Today/yesterday

Sep 29, 2009

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 Related

Query Access Database Using Cell Reference Parameters

May 28, 2014

Is it possible to use cell references to define parameters in an SQL query to an MS Access database?

I assumed it was similar to using SQL but I'm getting an error in excel.

Here is my query: .....

The error I get in excel is: "Invalid character value for cast specification" and it wont let me specify a cell.

View 6 Replies View Related

Excel 2010 :: Workbook With Two Sheets / Query Parameters

Mar 6, 2012

I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.

View 9 Replies View Related

CountIF Multiple Parameters

Jun 18, 2009

I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:

1) Job Type - Example would be Medical or Imprint

2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.

All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.

I wish to first count how many jobs fit a specific job type. This was easily done:

=COUNTIF(Data!A2:A4121,A7)

A7 = Medical
A1:A4121 = the range of the names.

Count comes up as 346, which is correct.

I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:

=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")

Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.

However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.

View 14 Replies View Related

Multiple Parameters In Sum Product

Oct 18, 2011

Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?

=SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P11111"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P22222"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P33333"))

View 5 Replies View Related

Multiple Criteria Parameters In SUMIFS

Apr 29, 2014

I have a below table as below

A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1

When i put the formula

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))

it returns 12 However when i put

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))

it returns only 5

I don't want to use multi formula like

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))

I want to achieve this using SUMIFS only

View 5 Replies View Related

Formula With Multiple Lookup Parameters

May 8, 2009

In the attached file I have a sheet containing my data in A1 - D73. Column A contains a list of names, Column B contains a specific month, Column C contains a specific category and Column D contains the raw data.

Is it possible to create a formula similar to VLOOKUP to look not only at Column A, but to look at Column B as well in determining the value returned? I would like other users to first select a name and then select a month to view the data. I've attached a sample of what I've created so far. The original file contains 14 Names, 9 Months and 40 Categories.

View 3 Replies View Related

Multiple Parameters Using A Range Of Cells To MSQuery

Apr 17, 2003

how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?

View 9 Replies View Related

Import Multiple Text Files Based On Parameters

Mar 11, 2008

I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column B). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns C and D, the third text file in columns E and F etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the .txt part of the file name isnt important). Ive attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.

View 6 Replies View Related

Web Query Looping Multiple Outcomes

Dec 8, 2009

I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.

Here is where I'm starting out.

View 9 Replies View Related

Using Power Query To Do Multiple Searches On Website?

Jul 10, 2014

I've been trying to get power query to search a list of parcel numbers on an assessor site: [URL]. The problem I'm getting, every time you put in a different parcel number, you get the same URL for the results. I want to extract just a few pieces of information for each parcel search, but I can't initiate a search through excel. I'm thinking the problem is in the Data source settings. How can just get it to show me the assessors page results for a single parcel search in excel?

View 5 Replies View Related

Export Excel Query To Multiple Text Files

Apr 2, 2012

I need to create a function which will take a objectname (a query or table) and a file name format

The objective is to export this query or table into multiple text files each with a maximum of 1000 lines per text file. These files are used as an input for SAP - the interface only takes a maximum of 1000 lines per file.

I am having three issues with the code below.

1. How do I split an excel query based on line numbers? Is there a way to get the equivalent of ROW_NUM in oracle ? Or do I have to loop through it and maintain a counter?
2. What is the best way to split the file ? Loop within loop? I need the files to be tab separated (no header required)
3.The rst.RecordCount is acting strange - when I pass a name of the query - the property returns the right no. of rows, however when I pass a any table name it only returns 1 - is this the expected behavior - or am I missing something

Code:
Function ExportAsText(strObjectName As String, strFileName As String) As Long
' Purpose: Export any given query, table to mutliple files each of certain length
' the no. of lines per text file is defined by the config parameter SAP UPLOAD, LINE LIMIT PER TEXT FILE
' in the LKUP_CONFIG database

Dim db As DAO.Database 'This database

[Code] ..........

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved