Make SQL and QBE Smarter
- When a query that selects identifiable rows joins to a query that aggregates data, allow rows in the identifiable part to be updated rather than making the query non-updateable. Extend this to heterogeneous joins so if one table in the join is updateable but another is not, allow updates to the updateable one.
- Stop smothering expressions with excessive numbers of parentheses. There is no reason that I can think of that would require an individual column name to be enclosed all by itself in parentheses.
- Stop reformatting MY SQL strings. I have a work-around for this but it is iffy. I switch to SQL view and save the query. As long as I never switch to QBE view again, Access doesn't normally rewrite my query but that isn't a guarantee.
- Stop helping me by renaming all fields to Exprx when a table goes missing or at least keep track of it and fix them when the table is relinked.
If I had to pick one it would be #2. I spend a lot of time ripping unnecessary garbage out of SQL strings to make them readable.
Andy Rice commented
Especially "2. Stop smothering expressions with excessive numbers of parentheses."
Dick Watson commented
QBE needs ability to show non-equi-joins in the QBE grid. Show the line with a click button in the middle, say, that can show the exact join expression. Why not make it possible in the QBE grid to drill-down the displayed tables (open their table design), or, even better, create or edit embedded subqueries (open them in their own Query Editor) similar to what is done for setting a lookup query?
John Partyka commented
All good suggestions. Another feature I'd like is search/replace capability in the SQL view.
I see there are other good SQL view suggestions posted on this site that should be combined.
I enthusiastically second all of these excellent ideas.
Additionally, I will echo other users' suggestions and add a few of my own:
* Please add syntax coloring and find/replace functionality in SQL view! (My current cumbersome workaround for the productivity-killing primitiveness of SQL view is to copy and paste the SQL into Notepad++, make my changes in that much more friendly environment, and then copy/paste the SQL back into Access.)
* Add FULL OUTER JOIN.
* Add a GROUP BY function similar to ListAgg in Oracle SQL to concatenate text from multiple records.
* Add Intellisense to SQL view.
* When the combo boxes for field names in graphical query design mode are expanded, the visible text is currently truncated to the width of the combo box in the design grid. Please allow the column widths within the expanded list to be wide enough to show all of the text.
* Let users specify join conditions other than equality within the graphical query editor. (For instance, Tbl1 LEFT JOIN Tbl2 ON Tbl1.Fld1 < Tbl2.Fld2 -or- Tbl1 LEFT JOIN Tbl2 ON Tbl1.Fld1 LIKE Tbl2.Fld2.)
* Support inline comments within SQL.
* Make Alias a separate row within the graphical query design view instead of including it in the Field row. This will make it easier to see at a glance what field or expression an alias refers to.
* Allow queries to be opened in SQL view directly from the nav pane.
* Intellisense/Builder in query design view should recognize table aliases.
Frank Rotolo commented
Agreed on all 4 items, plus retain whitespace and indentation formatting in SQL View for better readability.