Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Nov 3, 2003

MS Access for the Business Environment: Access Query Techniques: Crosstab Queries - Page 3

By William Pearson

We will now move to SQL view, as we wish to use direct SQL and to work with queries at a level that goes beyond working with wizards, or even the Design view we have used in the past.

11.  Select SQL View using the View Selector button in the main toolbar (it appears under the File item on the main menu), as shown in Illustration 6.


Illustration 6: Select SQL View

The SQL view editor appears, complete with a SELECT keyword in place, followed by the ubiquitous ending character for MS Access queries, the semicolon (";"). Illustration 7 depicts the initial view.


Illustration 7: Initial SQL View

Here we can enter, display, and / or modify a query using SQL directly, as we will throughout this lesson. (We can do many things here that might prove difficult or impossible in Design view, or under the auspices of wizardry. We want to see the actual SQL for a crosstab in the present example, and the powerful flexibilities it affords us.)

We will compose a simple SELECT query to gain an understanding of our fundamental dataset; our query will focus on the customer and product information, with the appropriate joins to create relationships between various tables in the Northwind database.

12.  Type the following basic SELECT query into the editor:

SELECT

   Customers.CompanyName, Categories.CategoryName, 

     Count(Products.ProductID) AS Qty

FROM
 
   (Categories 
 
     INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)  border=0 alt="">

        INNER JOIN ((Customers 
 
           INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
 
              INNER JOIN [Order Details] 
 
ON Orders.OrderID = [Order Details].OrderID) 
 
   ON Products.ProductID = [Order Details].ProductID

GROUP BY Customers.CompanyName, Categories.CategoryName, 
 
   Products.ProductID;

We can run the query at this stage by selecting Query --> Run from the main menu.

13.  Save the query as ACC06-Step1.

14.  Select Query --> Run from the main menu.

The resulting dataset appears as partially shown in Illustration 8.


Illustration 8: The SELECT Query Dataset



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date