syntax for basic Basic SQL Ms Access Gurus

If you are helped, please return the favor and help support this site, thank you.

Home Articles Code Tools

Basic SQL Syntax for Access

Syntax for basic SQL statements in Access. SQL = Structured Query Language

Quick Jump

SQL Steps Example Reference Backstory Share Donate

Goto Top  

SQL

Basic SQL

Here is basic SQL for a query.

SELECT fieldlist
FROM tablename
IN 'path\anotherdatabase.accdb'
WHERE criteria
GROUP BY fieldlist
HAVING criteria for fields that are grouped (aggregated)
ORDER BY fieldlist;

Where:

fieldlist
is a list of fields or expressions separated by a comma

path\anotherdatabase.accdb
is a fully qualified name of a file, delimited with a single quote

criteria
is how you want to limit the records returned

The SELECT clause is always required.
All other clauses are optional if SELECT only contains expressions that don't require a table to calculate

Goto Top  

Steps to create a new query from SQL

  1. Create ribbon
  2. from Queries group, choose Query Design
  3. right-click in top pane for sources and, from shortcut menu choose SQL View, or switch to SQL View using the Home or Query Design ribbon
  4. copy the SQL and paste
    be sure not to keep any characters before the word SELECT, or after the terminating semi-colon ;
  5. switch to Datasheet View to look at results
  6. save the query if you want

Goto Top  

Example

You can create a query in any database from this SQL without using any table -- so there is only a SELECT clause because all the columns use calculated expressions that don't depend on any table. All the expressions report information pertaining to the current date. 

SELECT Date() AS DateToday
, Format(Date(),"ddd") AS DayName
, Format(Date(),"d") AS DayNumberInMonth
, Format(Date(),"mmmm") AS MonthName
, Format(Date(),"yyyy") AS Yr;

Goto Top  

Reference

Access SQL: basic concepts, vocabulary, and syntax

Access SQL

Goto Top  

Backstory

Understanding basic SQL is very helpful

Access has a wonderful graphical query designer! Good to take advantage of it. However, realize that what Access stores to get the data is the SQL statement, so if you plan to develop, good to switch to SQL view to see how everything is specified.

If you like this page, please let me know, thank you. Donations are always appreciated

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/SQL_BasicSyntax.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/SQL_BasicSyntax.htm

Get Help with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time, and I'll give you lots of links to good resources.

SQL is important to get familiar with. I'm happy to help. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top