SQL is an acronym of structured query language; it is also a standard scripting language. You can use SQL to make requests from database, there are four types of SQL queries, and you can use them in MS access. These are: union, pass-through, data-definition and sub query. From this article you will learn how to create SQL queries in MS access.
Follow the steps given below to create SQL in MS access.
Step1: creating a subquery
First step is to create a new select or action query. Now go to query design view and add the desired fields to the design grid. After doing this, type a SQL statement in the criteria cell to use a subquery, it has to be done to define criteria for a field. You are using a subquery then type a select statement in the field cell instead. If it is desired to rename the field then do it and Click the View button to see the results.
Step2: Creating a union query
To create a union query, first open a database window. Now go to queries tab and click new. After clicking new, new Query dialog box opens. Next step in this section is to click design view and click ok, it will open show tables dialogue box appears. There is no need to add any tables and click close.
Now again go to query menu and then click on SQL specific and select union. After that enter SQL select statements and add the Union operation if you don’t want duplicate records or the Union All operation if you do want duplicate records. Now enter SQL statements and click view to see your results.
Step3: Creating a Pass-Through Query and Creating a Data-Definition Query
Follow above steps to create a pass-through query.
First step is to go to query menu and click on SQL Specific and then on Pass-through. Now go to view menu and select properties. After clicking property, the query property sheet appears. To define information about the database, set the database to ODBCConnectSTR property to define information. To do this, you will need information about the server. In the SQL pass- through Query window, type your pass-through query. To run this query, click run on toolbar. If the query returns records then click view instead of run.
To create a data-definition query Follow steps 1 through 4 above.
Now you will have to open the Query menu and select SQL Specific, then click on Data Definition. Now it is time to enter SQL statements for your queries and you can enter Create Table, Alter Table, Drop Table and Create Index. Now to view your records, click on run and you will be able to see records which you have created in MS Access.
There are some points; you should take care of when creating SQL queries. Pass-through queries is used to retrieve records in networked databases and data-definition queries are used database objects like those found in MS access or MS SQL servers.