SQL vs Access Query

[ECEK]

I am really familiar with the way that you can create querys in Access. Often involving quite complex calculations.

I wouldn't dream of "writing" them in SQL.

Does SQL Server have a similar way to create queries like Access or is it all text based?

 

[Frothingslosh]

It has a truly terrible GUI interface that is basically a more primitive version of the one in Access.

As someone who went from only using the Access GUI to writing queries in SQL Server, it's really not hard to make the change. Just make sure you have internet access, because for the first few months you'll likely be using Google a lot to nail down the specific syntax.

 

[CJ_London]

Does SQL Server have a similar way to create queries like Access or is it all text based?

this link may be of use. As Froth says, pretty basic compared with the Access equivalent

https://www.mssqltips.com/sqlservertip/1086/sql-server-management-studio-query-designer/

Not sure why you have an aversion to writing in sql - all the query builder does is help you write it, Its how a lot of people learn, use the gui to create the query then switch to the sql view to see what it looks like. And as with code on forms/reports there are things you can do which are not available in the gui

 

[plog]

I'd work through this tutorial - https://www.w3schools.com/sql/

SQL really is pretty simple. The problem with GUI interfaces is they make it seem like things behind the scenes are super complex. Especially Access's--it creates bloated SQL with tons of unnecessary parts and then jams it all together so that no human can parse it by looking at it.

That tutorial will walk you through all the parts of an SQL statement and let you know when adn where to use them.

I mostly use MSSQL and Oracle, but when I develop SQL that uses those systems I start in Access. I use the query designer to get the results I want, then I grab the SQL from Access, parse it so that I can read it (spaces, line breaks, removal of all the bloat). Then I convert domain specific functions (e.g. DateDiff(), Date(), etc) to the new domain they are going to (Oracle, MSSQL, etc.) and then I have working SQL in the correct domain.

 

[ECEK]

This is great stuff thanks guys.

CJ. I don't have an aversion. Much the same that I don't have an aversion to bicycles: I just happen to prefer a car !!

 

[Minty]

I find if you use "Create a new View" in SQL , the query by grid - although not quite as funky as access is pretty easy to work with.
It will also represent expressions in joins and allows an easy way of using employing sub queries (Derived table) in the query editor, as well as showing the SQL as well as the graphical representation. Certainly much better that the plain old SQL query editor from the new query window.

You can then either save the View or paste the SQL into your Stored procedure etc and discard the view as you would a temporary quick query in Access.

 

[jleach]

I wouldn't dream of using a designer to write queries. Raw SQL is my preference any day.

There's a lot of queries that can't even be realized in the Access designer. Sometimes you can work your way around it, but that means the underlying stuff is much more complex than it has to be (if you find a workaround using the designer).

Understanding (and being fluent in) raw SQL will force you to truly understand what you're asking the database to do: things like performance, structure, indexing... so many aspects of core database principles I took for granted and abused before I started using raw SQL.

(I even use it near exclusively for DDL now...)

 

[The_Doc_Man]

The only time I use the grid form of the query designer is if I have a JOIN that will be using a relationship, OR if I want to use the graphics portion of that designer to declare a one-time relationship. Otherwise, I prefer SQL. And in any case, I use the grid to get started and then go forward from there.

This is similar to my attitude about the Access wizards. They are ALL dumber than a box of rocks - but they build a GREAT framework that I can then customize to Hell and gone. Once the scaffolding has been built, the nit-picky detail work is easy.

 

[Pat Hartman]

Not sure why you have an aversion to writing in sql

It is TEDIOUS and error prone. I work with dozens of applications in any given month and who can keep all those schemas in their mind along with all the variations on how customer is spelled? When I was writing embedded DB2 in COBOL in the eighties, I dreamed of something like the QBE and Access finally gave it to me. The SQL server version is a poor substitute. The GUI you get when you build a view is MUCH better so I frequently use that if I have the misfortune of having to use T-SQL and then just change the generated SQL to not be a view but there is no going back for changes so its usefulness is limited.

The benefit of the QBE view in Access is also what causes it to be a PITA to the text purists. Access rewrites your SQL string to make it render in QBE and so every one complains about that.

If I were always working with the same schema (one that I created so I like all the names), then writing an SQL String would simply be a matter of typing. Still more time consuming than point and click but not terrible. Unlike the Access QBE, the SQL editor window does give you intellisense so it isn't as bad as it was in the early days when you were on your own typing every single character. The Access SQL view is pretty much useless INMNSHO and the Access team should be ashamed of it.