I want to use sheet 1 to filter the query on Sheet 2
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?
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
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.
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.
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.