We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
5 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
I want to use sheet 1 to filter the query on Sheet 2
Posted on 2011-02-18
I have an Excel spreadsheet, sheet 1 finds all data of merchandise that has been returned. On sheet 2 I want to create a table that finds the location of where to put the returned items. I can create a query that combines 2 tables on ItemKey. However, I am getting all the data in Sheet2 instead of just the returned items. I created an external datasource using Data -> From Other Sources -> Microsoft Query, pointing to the Big Database and getting only the following : Table1.ItemKey, Table2.ItemKey, quantity and Location. where Table1.ItemKey = Table2.ItemKey. How do I limit the output to just the items in Sheet 1?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
8
5-
2
15 Comments
I think what he wants to do is select the data in the two table based on the data set in the excel sheet already.
Something like (Suedo sql code)
SELECT * from Table1,Table2
WHERE Table1.id = Table2.ID
AND
Table1.ID IN ( SELECT ID FROM Excel.workbook.NamedRangeO
Something like (Suedo sql code)
SELECT * from Table1,Table2
WHERE Table1.id = Table2.ID
AND
Table1.ID IN ( SELECT ID FROM Excel.workbook.NamedRangeO
Right, but specifying inner join will do the same thing since it will only pull values that exist in both tables.
No, if sheet one only has 10 rows with unique ID's he ONLY wants those 10 records from the db not all 50,000
Please do at least a simple Google search before arguing with another expert.
http://www.w3schools.com/sql/sql_join.asp
INNER JOIN will only pull records that exist in both tables.
It does matter what kind of query you are running though. If it's an SQL query than joins apply, otherwise you might need something else.
SELECT Table1.ItemKey, Table2.ItemKey, Table2.quantity, Table2.Location
FROM Table1 INNER JOIN Table2 ON Table1.ItemKey = Table2.ItemKey
http://www.w3schools.com/sql/sql_join.asp
INNER JOIN will only pull records that exist in both tables.
It does matter what kind of query you are running though. If it's an SQL query than joins apply, otherwise you might need something else.
SELECT Table1.ItemKey, Table2.ItemKey, Table2.quantity, Table2.Location
FROM Table1 INNER JOIN Table2 ON Table1.ItemKey = Table2.ItemKey
Neilsr is correct. I want to look at an item in sheet 1 and find the corresponding 4 fields in Sheet 2 and record them on Sheet 3.
Nobody is argueing. IF you read what both the questioner AND I said you will see what your mistake is.
We KNOW how a join works. That is on the two tables in the database. HE ALSO wants to filter on the data in sheet one at the same time to reduce the subset of data returned.
YOUR query takes into account ONLY the two tables in the external database.
So please, next time before you you critisise ME or anybody else, READ THE QUESTION AND THE COMMENTS.
We KNOW how a join works. That is on the two tables in the database. HE ALSO wants to filter on the data in sheet one at the same time to reduce the subset of data returned.
YOUR query takes into account ONLY the two tables in the external database.
So please, next time before you you critisise ME or anybody else, READ THE QUESTION AND THE COMMENTS.
Oh, I see. I was assuming that Table1 was on Sheet1. If you do the join with sheet1 as one of the tables, it would work.
OK Here goes....
I have made a few assumptions but bear with me.
In your spreadsheet assume your data in sheet1 has an Index column in column A that is your IndexKey
once you have your data in sheet 1, define a named range on column A
Now in your database you need to ad a LINKED TABLE that is infact sheet1 of your excel sheet. So your database now has 3 tables. Table1, Table2 and your linked table called DATA1
Now you can do a Get external dataFrom another source in sheet 2 that has the following SQL Query as its source.
SELECT Table1.IndexKey, Table1.Field1, Table1.Field2, Table1.Field3, Table2.IndexKey, Table2.F1, Table2.F2
FROM `C:\Documents and Settings\neilrsr\My Documents\Database1.accdb`
WHERE Table1.IndexKey = Table2.IndexKey AND Table2.IndexKey = DATA1.F1
That should get your query set to return ONLY records from the database where the IndexKey is in column A of Sheet1.
I have made a few assumptions but bear with me.
In your spreadsheet assume your data in sheet1 has an Index column in column A that is your IndexKey
once you have your data in sheet 1, define a named range on column A
Now in your database you need to ad a LINKED TABLE that is infact sheet1 of your excel sheet. So your database now has 3 tables. Table1, Table2 and your linked table called DATA1
Now you can do a Get external dataFrom another source in sheet 2 that has the following SQL Query as its source.
SELECT Table1.IndexKey, Table1.Field1, Table1.Field2, Table1.Field3, Table2.IndexKey, Table2.F1, Table2.F2
FROM `C:\Documents and Settings\neilrsr\My Documents\Database1.accdb`
WHERE Table1.IndexKey = Table2.IndexKey AND Table2.IndexKey = DATA1.F1
That should get your query set to return ONLY records from the database where the IndexKey is in column A of Sheet1.
You may be able to do it without the linked table by referencing sheet1 directly I dont know. Maybe TommySzalap… can advise?
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month5 days, 2 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.