Excel/MS Query question

[Preka]

I've never really had cause to use SQL queries in Excel before. Having made my first, I noticed that when I viewed the SQL, instead of the inner joins I'm used to seeing, there is a series of "where" statements that perform that function instead.

If I edit the SQL to use inner joins, the query becomes invalid for use in the graphical query editor (which wouldn't be a problem except I want to use cells as parameters values, and apparently you can't parameterize a query that isn't valid for the GUI editor). It still returns rows just fine, just no more GUI.

If I go to table->joins, it displays the join syntax I'm used to seeing, but in the SQL statement for the query as a whole, it isn't present.

Is there a reason for that? This is a largely an academic/curiosity question, since the "where" statements get the job done just as well for my purposes, but I'm just curious about why it works that way, and why using "join" breaks the GUI.

 

[SkipVought]

Hi,

Use the SQL as generated, with table joins in the Where clause.

If you do an outer join (Limited to 2 tables and 1 outer), you will see something closer to what you expect to see.

CAVEAT: When doing any aggregation with additional filter criteria, edit the SQL to move the criteria from the HAVING clause to the WHERE clause, with the exceprtion of any aggregation expression, which must remain in the HAVING clause.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Preka]

Well, yes, I was planning on just using the SQL as generated, but as I said before, I'm really just curious about the "why" of it.

 

[SkipVought]

MS Query SQL has fewer 'bells n whistles' than, for instance, MS Access SQL. It's merely a design and capability difference. I learned the differences by trial and error.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

In fact the SQL that is generated is identical to that used by (earlier?) versions of MS Access.

I believe it may be to do with the fact that MS Query and Access used the JET SQL database engine (

http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine)

rather than the later SQL Server database engine - this was in fact how I 1st started learning SQL back in the day and it took me a while to get used to doing the joins seperately as I was so used to messing around with SQL generated by MS Query and MS Access

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question