databasedev.co.uk - database solutions and downloads for microsoft access

SQL Queries

SQL Queries in Microsoft Access:

Microsoft Access is and excellent package for handling databases. Its query feature is used to retrieve data in desired formats. You can also use SQL Queries. SQL queries are those that are created using the various SQL commands like SELECT, DELETE, UPDATE or APPEND. The various SQL queries are:

Union Query

In this type of query, data from one or more tables or queries is merged. Suppose you have sales results of four quarters available in four different tables. A Union query can be used to create a complete set of all four quarters by combining the four different tables. This is what the query will look like:

SELCT [dealer_code],“First” as Quarter, [sales] from sales_1
UNION
SELCT [dealer_code],“Second” as Quarter, [sales] from sales_2
UNION
SELCT [dealer_code],“Third” as Quarter, [sales] from sales_3
UNION
SELCT [dealer_code],“Fourth” as Quarter, [sales] from sales_4
              

Another example of Union query would be:

SELECT [product_code], [sales_year], [sales] from sales_hist 
WHERE [enduse] = 'EXPORT'
UNION
SELECT [product_code], [sales_year], [sales] from sales_curr
WHERE [enduse] = 'EXPORT'
ORDER by [product_code]

This example will give the historical and the current year sales of all the products exported. The number of columns in each component subqueries should be the same.

Pass Through Queries

This type of query is used to issue commands data directly to the ODBC Databases like Dbase or MS FoxPro. This sends the commands that can be understood by the server. Pass through queries can be used to retrieve or write data. Here you have to supply the ODBC Connect string for identifying your database. Pass through queries can be used to run stored procedures on the ODBC server.

For more on Pass Through Queries read the following article - Using Pass-Through Queries in MS Access

Data definition queries

They are used to change the structure of the database. You can use them to create, modify or delete tables and indexes.

The commands supported are CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX.

The following demonstrates the CREATE TABLE syntax:

CREATE TABLE sales_1
[sales_year] As Integer,
[product_code] As Text,
[endues] As Text,
[sales]	As Double,
PRIMARY KEY ([sales_year], [product_code]);

This query will create a table sales_1 with the appropriated fields. The next example uses data definition queries in a function:

Sub RUN_COMMAND(m_String As String)
    Dim mydb As DAO.Database, myqd As DAO.QueryDef
    Set mydb = DBEngine.Workspaces(0).Databases(0)
    Set myqd = db.CreateQueryDef("")
    myqd.SQL = m_String
    myqd.Execute
    mydb.Close
End Sub

This will create a Sub procedure RUN COMMND having m_string as a parameter. To invoke this use the following syntax:

RUN_COMMAND "CREATE TABLE Test1 (ID integer, DETAILS Text)"

SQL Subqueries

They are independent queries nested inside another query. You can use a subquery to define another field or in the Criterion to test a condition.

The following show some Subquery examples:

SELECT  * from sales_1 WHERE [product_code] IN (SELECT [product_code]
FROM product_master WHERE product_type = 1)

This example uses the subquery as a criterion to select those products having product type as (1) one only.

SELECT  [product_code], (SELECT [product_name] FROM product_master
WHERE product_master. [product_code] = sales_1.[product_code])
AS product FROM sales_1

This subquery is used to create a new field product retrieving the product name from the product_master.

Thus, we see how the various types of SQL queries are used for the efficient handling of data, and how they can be used in Microsoft Access.