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:
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.
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
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)"
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.