0

I am trying to do a simple subquery join in Microsoft Query, but I cannot figure out the syntax. I also cannot find any documentation for the syntax.

How would I write the following query in Microsoft Query?

SELECT *
FROM (
    SELECT Col1, Col2
    FROM `C:\Book1.xlsx`.`Sheet1$`
) AS a
JOIN (
    SELECT Col1, Col3
    FROM `C:\Book1.xlsx`.`Sheet1$`
) AS b
ON a.Col1 = b.Col1

Is there official documentation for Microsoft Query?

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
Kuyenda
  • 4,529
  • 11
  • 46
  • 64

2 Answers2

1
SELECT * 
    FROM (SELECT ID, Company FROM (`Sheet1$`)) AS a 
    INNER JOIN 
    (SELECT ID, Name FROM `Sheet1$`) AS b 
    ON a.ID = b.ID;

That worked for me. It looks like the only difference is INNER JOIN vs. JOIN. My fields are ID, Company, Name in that order for Col1, Col2, Col3.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Dick, I was able to get your query to work copied verbatim. There is some character that causes MS Query to balk, but it isn't "INNER JOIN". I keep getting "Could not add table `(`." I just can't seem to figure out what I am doing is outside the syntax rules. Thanks! – Kuyenda May 10 '10 at 20:51
  • I had some typos in my query, I fixed them and now it works. Unfortunately, it looks like Excel can only handle one join at a time in MS Query. – Kuyenda May 10 '10 at 22:13
  • Apparently Microsoft Query is capable of doing more than one INNER JOIN, but I haven't figured out the syntax. http://support.microsoft.com/kb/115340 – Kuyenda May 10 '10 at 22:31
0

Follow Excel the Query Wizard to create/edit queries.

You can access it from Excel menu: Data->Import External Data->Import Data

As well you can check this link http://www.exceluser.com/explore/msquery1_1.htm

volody
  • 6,946
  • 3
  • 42
  • 54
  • Thanks volody, I figured that much out. I am editing the query from within Microsoft Query. The query I am setting up is too complext to be represented in the query builder GUI. – Kuyenda May 10 '10 at 16:40
  • I see, I don't know you requirements but as a suggestion probably you have to find alternative solution. Like import data into recordset and then run standard sql statement. – volody May 10 '10 at 16:46