Solved

I want to use sheet 1 to filter the query on Sheet 2

Posted on 2011-02-18
15
272 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:chaverly
  • 8
  • 5
  • 2
15 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Specify that it be an INNER JOIN
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
If that doesn't make sense, then post the query, please.
0
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
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.NamedRangeOnSheet1 )
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Right, but specifying inner join will do the same thing since it will only pull values that exist in both tables.
0
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
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
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Some programs use JOIN in place of INNER JOIN
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:chaverly
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
So just join all three tables.
Sorry about all that.
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 250 total points
Comment Utility
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`.Data1 Data1, `C:\Documents and Settings\neilrsr\My Documents\Database1.accdb`.Table1 Table1, `C:\Documents and Settings\neilrsr\My Documents\Database1.accdb`.Table2 Table2
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.

0
 
LVL 37

Expert Comment

by:Neil Russell
Comment Utility
You may be able to do it without the linked table by referencing sheet1 directly I dont know. Maybe TommySzalap… can advise?
0
 

Author Closing Comment

by:chaverly
Comment Utility
Thank you both so much.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
There are ways to pull parameters from a query directly from cells in Excel without linked tables, but use what works for you. I don't want any points on this one anyway because I kind of made a fool of myself.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now