Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-11-2019, 12:12 AM   #1
ECEK
Newly Registered User
 
Join Date: Dec 2012
Posts: 699
Thanks: 209
Thanked 4 Times in 4 Posts
ECEK is on a distinguished road
SQL vs Access Query Overview

I am just learning to query my data in an SQL Database.

I am totally familiar with the graphic interface of Access to create queries but need your advice.

I can end up creating several queries within queries to get my result.

How do I look to do this in SQL?
Do I build it in one complex query (Not dissimilar to VBA style or do I build views and then reference these (as one would do in Access).

Your direction is much appreciated

ECEK is offline   Reply With Quote
Old 04-11-2019, 03:40 AM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,440
Thanks: 85
Thanked 1,443 Times in 1,362 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: SQL vs Access Query Overview

Quote:
Originally Posted by ECEK View Post
Do I build it in one complex query (Not dissimilar to VBA style or do I build views and then reference these (as one would do in Access).
Put simply "Yes".

What you use depends on the context. A View may be good where there are a number of queries built on the same foundation. Provided they are not too complex they work pretty much like tables.

TSQL is a full programming language that supports multiple commands within one query. You have to use the SQL editor to do this because the designer doesn't support this functionality.

Don't be put off writing SQL just because you might have struggled with the SQL editor in Access. It always was hopeless. The Editor in the Management Studio is very powerful with Intellisense and on-the-fly syntax checking. It is much easier to build far more complex queries including subqueries.

The joins are much simpler to write than in Access without the need for nesting joins. The joins are far more diverse than in Access, with Full Outer Join, Cross Apply, Outer Apply as well as the familiar ones.

The learning curve to get into SQL Server through Management Studio is not especially steep but "the mountain is high" if you get my drift. You can get working with it fairly quickly but keep finding new stuff for years. I've always found a capability to do anything I have been able to imagine wanting to do (and I have a good imagination).

Learn about Stored Procedures early in your exploration. They are one of the most useful features.

Start your query coding using the Select TOP 1000 records on the right click of a table. This will load the SELECT for its columns and the FROM clause which you can start editing from.

Save time entering column names for other tables by Dragging the Column heading of the table from the Object explorer tree view into the query editor. This will paste a list of the columns into the SQL text.

The key to building complex queries is the formatting. Use new lines for the key words and indent the subordinate sections. Vertically align the open and close parentheses of the subsections.

Highlight a section of code in the editor and press run to get the results from that section. This is handy for testing.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
pbaldy (04-11-2019)
Old 04-11-2019, 07:06 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,715
Thanks: 11
Thanked 4,001 Times in 3,939 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: SQL vs Access Query Overview

I love this:

Quote:
Originally Posted by Galaxiom View Post
The learning curve to get into SQL Server through Management Studio is not especially steep but "the mountain is high"
I'll add that temp tables in a stored procedure can do your nesting, as can table valued functions.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 04-16-2019, 02:04 AM   #4
AlexDoran
Newly Registered User
 
Join Date: Feb 2014
Posts: 12
Thanks: 5
Thanked 0 Times in 0 Posts
AlexDoran is on a distinguished road
Re: SQL vs Access Query Overview

Im soon to be climbing that mountain and am keen to learn about SQL, is there any good recommended material / tutorials?
AlexDoran is offline   Reply With Quote
Old 04-16-2019, 04:03 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,160
Thanks: 146
Thanked 1,657 Times in 1,629 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: SQL vs Access Query Overview

If you are used to the Query builder in Access, use the New View as a starting point to just building queries and seeing the SQL used.
I frequently use the view editor, as you can see the criteria, results, pictorial joins and SQL all in one place. I use it to build the basics then simply paste the sql into the editor and refine it for further use, either in a stored procedure or function.

It's actually what the Access Query designer should be.

As already started the join types and general flexibility are far greater in SSMS.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Overview Query mveijndh Queries 1 09-22-2016 07:54 AM
Overview query where i can edit the checkboxes drpiso Queries 6 05-03-2012 11:06 PM
Access Overview for Programmers rbarlow General 1 03-12-2006 10:14 PM
Overview Form-Query problems with a Listbox Carn Forms 0 10-25-2004 02:23 AM
Create overview query for report Michelangelo Queries 3 07-08-2003 11:47 PM




All times are GMT -8. The time now is 08:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World