Can you create a query in MS Access using a SQL Statement?

Microsoft Access is designed such that even if you do not know SQL or SQL syntax you could still use the application. As a consequence, there is no obvious starting point to write a SQL Statement and execute it. However, it does allow you to write a SQL Statement, save the query and execute it.

Open access application. I have just one simple table 'BOOK1'.

In the 'Queries' tab of Create click on Query Design to open a tabbed page 'Query 1" as shown.

Query 1 has two panes. The top one is empty and it is here you can write a SQL Statement and the bottom pane is where you can compose a query using visual elements.

Right-click in the empty top pane to show this drop-down.

Click on 'SQL View'. This view is not the same as 'View' in SQL Server. This opens a text editor where you can write a query. Note that it has just the 'SELECT'. It expects you to write a "SELECT" statement.

Note that there is a Table 1 called 'Book1' shown here.

Now I write a SQL query against this table.

SELECT ID, Field2 

From Book1

Here is the statement in the SQL View's editor pane.

Now save the query, Query1. You need to click on the tab to save as shown.

You can save it with a different name which is usually recommended. I saved it as BookQry as shown.

Now double click on BookQry on the left to open the query to show the result.

How does TableSample in FROM clause of a SELECT Query work?

TableSample clause started with SQL Server 2015 limits the number of rows returned from a table to a sample % or sample numbers. In fact, it may not provide the specified rows of %. Also, it is not for tables with too few rows.

Here is how TableSample is defined in the MSDN site:

------------

TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.

--------------

For these scenarios it cannot be applied:

Derived tables
Linked Server Tables
Tables from Table-Valued functions
Row-set functions
Open XML

This here is the syntax for TableSample clause:

----------------

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )

[ REPEATABLE (repeat_seed) ]

TableSample in FROM Clause does not behave as defined in the syntax and could provide surprising results:

I queried the Northwind databases Orders table using the following syntax in SQL Server 2016 Developers edition.

SELECT * FROM Orders TableSample(10 Percent)

SELECT * FROM Orders TableSample (10)--surprisingly this does not result in error

SELECT * FROM Orders TableSample (10 ROWS)

These queries were run a number of times and the rows returned were variable from run to run and some times resulted in 0 returned rows.

More details here:
https://hodentekmsss.blogspot.com/2018/03/tablesample-in-from-clause-to-limit.html