MS Access 2003 Step By Step - Asim Abbasi

Chapter 1: Overview

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7

Queries

MS Access is not only an excellent data analysis tool but a wonderful RDBMS (Relational Database Management System). Once we have all the tables in MS Access both Local and Linked, the next step would be to run queries or in other words to analyze the data residing in those tables.

MS Access treats external linked tables as if those tables are MS Access Local tables when it comes to queries discussion or in other words as if those tables are created locally and the data inside them resides in the MS Access database. So while writing queries always treat linked tables as if the tables residing locally to MS Access database.

Whenever you want to do something or rather anything with queries the very first step would be clicking on the “Queries” object under the “Objects” group. If you want to create a brand new query then click the “New” otherwise select the already created query from the right pane of the window and click “Design” command as shown in the above screenshot. If you click “New” then “New Query” window will popup with all different options pertaining to new query development. Most of the time we use the “Design View” option to build new queries visually just by click and drag operations.

If you want to find duplicate records information in any table or already existing query then use the option “Find Duplicates Query Wizard”. On the other hand if you want to find unmatched information between two tables or two queries or between the query output and a table then select the “Find Unmatched Query Wizard” option. We will be discussing about queries in great details in the chapters a head.

Design Queries Visually

Most of the time we use the ‘Design View’ option to create new queries. New queries can be based on existing tables or queries or both. The knowledge of SQL language statements/commands help but it’s not required. No need to write SQL code to build query. While you build query visually using click, drag and drop operations, MS Access writes the corresponding SQL code in the background. Remember that in RDBMS environment the only communication protocol in between database systems is SQL.

Once we are in the Design View of query builder, all we have to do is to select the tables and queries we want for this new query. Next step would be to build the relationship in between these tables/queries and select the columns and fields we would be looking for in the output of our new query. Relationship between tables/queries helps in limiting the number of records in output of the query or you can say that it helps in bringing information scattered in two or more tables in the output of a query.

Its worth mentioning over here that once you run your query and if the result is up to your desire then save it. The data you see in the output of the query can be exported from MS Access database in nearly all different famous data formats including but not limited to MS Excel, CSV (Comma Separated Values), Tab delimited values, MS Word, HTML etc.

We will be learning about queries in great details in the chapter ahead.

Access Builds SQL Behind The Scene

As mentioned before whenever you are building query MS Access on the backend creates the corresponding SQL code for you. Whenever you want, you can view that code. The SQL code can be better viewed by first having the query in the Design View and then selecting ‘View’ from the menu bar. Next click ‘SQL’ view command. Once you are in the SQL view you can see the SQL code as shown in the screenshot below. You can also edit this SQL code according to your likeness. If you make mistake, you will see error message showing up once you go back to the Design View by clicking ‘View’ and then ‘Design View’.

Chapter 1: Overview

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2003 Step By Step - Asim Abbasi