You can retrieve specific columns from your dataset by explicitly defining the columns in your SELECT statement, as follows:

SELECT AccountManagerID, FullName,[Email Address]
FROM Dim_AccountManagers

Using the wildcard (*) allows you to select all columns from a dataset without having to define every column explicitly.

SELECT *
FROM Dim_AccountManagers

An inner join operation tells Access to select only those records from both tables that have matching values. Records with values in the joined field that do not appear in both tables are omitted from the query results. Figure B-4 represents the inner join operation visually.

Figure B.4. An inner join operation only selects the records that match values in both tables. The arrows point to the records included in the results.

The following SQL statement selects only those records where the employee numbers in the AccountManagerID field are in both the Dim_AccountManagers table and the Dim_Territory table.

SELECT Region, Market, Dim_AccountManagers.AccountManagerID, FullName
FROM Dim_AccountManagers INNER JOIN Dim_Territory
ON Dim_AccountManagers.AccountManagerID = Dim_Territory.AccountManagerID

An outer join operation tells Access to select all the records from one table and only the records from a second table with matching values in the joined field. There are two types of outer joins: left joins and right joins.

A left join operation (sometimes called an outer left join) tells Access to select all the records from the first table regardless of matching and only those records from the second table that have matching values in the joined field. Figure B-5 represents the left join operation visually.

Figure B.5. A left join operation selects all records from the first table and only those records from the second table with matching values in both tables. The arrows point to the records included in the results.

This SQL statement selects all records from the Dim_AccountManagers table and only those records in the Dim_Territory table where values for the AccountManagerID field exist in the Dim_AccountManagers table.

SELECT Region, Market, Dim_AccountManagers.AccountManagerID, FullName
FROM Dim_AccountManagers LEFT JOIN Dim_Territory
ON Dim_AccountManagers.AccountManagerID =
Dim_Territory.AccountManagerID

A right join operation (sometimes called an outer right join) tells Access to select all the records from the second table regardless of matching, and only those records from the first table that have matching values in the joined field (see Figure B-6).

Figure B.6. A right join operation selects all records from the second table and only those records from the first table with matching values in both tables. The arrows point to the records that are included in the results.

This SQL statement selects all records from the Dim_Territory table and only those records in the Dim_AccountManagers table where values for the AccountManagerID field exist in the Dim_Territory table.

SELECT Region, Market, Dim_AccountManagers.AccountManagerID, FullName
FROM Dim_AccountManagers RIGHT JOIN Dim_Territory
ON Dim_AccountManagers.AccountManagerID =
Dim_Territory.AccountManagerID

When you are using the GROUP BY clause, you cannot specify criteria using the WHERE clause. Instead, you need to use the HAVING clause. This SQL statement groups the Market field and only gives you the count of states in the Dallas market:

SELECT Market, Count(State)
FROM Dim_Territory
GROUP BY Market
HAVING Market = "Dallas"

The following SQL statement groups the Market field and gives you the count of states in each market. In addition, the alias State Count has been given to the column containing the count of states by including the AS clause.

SELECT Market, Count(State) AS [State Count]
FROM Dim_Territory
GROUP BY Market
HAVING Market = "Dallas"

This SQL statement gives the Dim_AccountManagers the alias "MyTable."

SELECT AccountManagerID, [Last Name], [First Name]
FROM Dim_AccountManagers AS MyTable
WHERE MyTable.[Last Name] Like "L*"

To get a clear understanding of what the SELECT TOP statement does, follow these steps:

The SELECT TOP statement is easy to spot. This is the same query used to run the results in Figure B-9.

SELECT TOP 25 Customer_Name, Sum(LineTotal) AS SumOfLineTotal
FROM Dim_Customers INNER JOIN Dim_Transactions
ON Dim_Customers.CustomerID =
Dim_Transactions.CustomerID
GROUP BY Customer_Name
ORDER BY Sum(LineTotal) DESC

Figure B.8. Change the Top Values property to 25.

Figure B.9. Running the query gives you the top 25 customers by revenue.

Bear in mind that you don't have to be working with totals or currency to use a top values query. In the following SQL statement, you are returning the ten account managers that have the earliest hire date in the company, effectively producing a seniority report:

SELECT Top 10 AccountManagerID, [Last Name], [First Name]
FROM Dim_AccountManagers
ORDER BY HireDate ASC

The SELECT TOP PERCENT statement works in exactly the same way as SELECT TOP except the records returned in a SELECT TOP PERCENT statement represent the Nth percent of total records rather than the Nth number of records. For example, the following SQL statement will return the top 25 percent of records by revenue:

SELECT TOP 25 PERCENT Customer_Name, Sum(LineTotal) AS SumOfLineTotal
FROM Dim_Customers INNER JOIN Dim_Transactions ON
Dim_Customers.CustomerID =
Dim_Transactions.CustomerID
GROUP BY Customer_Name
ORDER BY Sum(LineTotal) DESC

Make-Table queries use the SELECT...INTO statement to make a hard-coded table that contains the results of your query. The following example first selects account manager number, last name, and first name and then creates a new table called Employees:

SELECT AccountManagerID, [Last Name], [First Name] INTO Employees
FROM Dim_AccountManagers

Append queries use the INSERT INTO statement to insert new rows into a specified table. The following example will insert new rows into the Employees table from the Dim_AccountManagers table:

INSERT INTO Employees (AccountManagerID, [Last Name],
[First Name])
SELECT AccountManagerID, [Last Name], [First Name]
FROM Dim_AccountManagers

Update queries use the UPDATE statement in conjunction with SET in order to modify the data in a dataset. This example updates the List_Price field in the Dim_Products table to increase prices by 10 percent.

UPDATE Dim_Products SET List_Price = List_Price*1.1

Delete queries use the DELETE statement to delete rows in a dataset. In the example here, you are deleting all rows from the Employees.

DELETE *
FROM Employees

As the name implies, the ADD clause enables you to add a column to an existing table. The basic syntax is as follows:

ALTER TABLE <TableName>
ADD <ColumnName> Type(<Field Size>)

To use the ADD statement, simply start a new query in SQL view and define the structure for your new column. For instance, running the example statement shown here creates a new column called SupervisorPhone, which is added to a table called TempLog.

ALTER TABLE TempLog
ADD SupervisorPhone Text(10)

When using the ALTER COLUMN clause, you specify an existing column in an existing table to work edit. You primarily use this clause to change the data type and field size of a given column. The basic syntax is as follows:

ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> Type(<Field Size>)

To use the ALTER COLUMN statement, simply start a new query in SQL view and define changes for the column in question. For instance, the example statement shown here changes the field size of the SupervisorPhone field.

ALTER TABLE TempLog
ALTER COLUMN SupervisorPhone Text(13)

The DROP COLUMN clause enables you to delete a given column from an existing table. The basic syntax is as follows:

ALTER TABLE <TableName>
DROP COLUMN <ColumnName>

To use the DROP COLUMN statement, simply start a new query in SQL view and define which column you want to delete. For instance, running the example statement shown here deletes the column called SupervisorPhone from the TempLog table.

ALTER TABLE TempLog
DROP COLUMN SupervisorPhone

For many analysts, Access serves as an easy-to-use ETL (Extract, Transform, Load) tool. That is, Access allows us to extract data from many sources, then reformat and cleanse that data into consolidated tables. Many analysts also automate ETL processes with the use of macros that fire a series of queries. This works quite well in most cases.

There are, however, instances where an ETL process requires primary keys be added to temporary tables in order to keep data normalized during processing. In these situations, most people do one of two things. They stop the macro in the middle of processing to manually add the required primary keys. Or they create a permanent table solely for the purpose of holding a table where the primary keys are already set.

There is a third option. The ADD CONSTRAINT clause allows you to dynamically create the primary keys. The basic syntax is as follows:

ALTER TABLE <TableName>
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (<Field Name>)

To use the ADD CONSTRAINT clause, simply start a new query in SQL view and define the new primary key you are implementing. For instance, the example statement shown here applies a compound key to three fields in the TempLog table.

ALTER TABLE TempLog
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (User, Description, LogDate)