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.