Microsoft Access 2003 part 3

Queries

A way of locating/retrieving information in an Access database in order for a user to view, change/update or analyze. A query is also a question, answer to such is retrieve from the database. Query results can be the basis for other access objects like another query or reports.

SQL view – you can create a query in this view by typing the query using an SQL (Structured Query Language) syntax.

Design view – you can create a query in this view by dragging the chosen field of a particular table or another query from the table area to design grid.


Types of Queries

Select query – the most common type of query. Created by selecting information from chosen tables (based on user criteria). Information is displayed in datasheet for viewing and analysis. Changes can be made to the underlying data, the user should be careful not to make accidental changes.

Update query – a query that updates/modify data in the underlying table.

Append query – a query that appends data to the underlying table.

Delete query – a query that deletes records from the underlying table based on a criteria set by the user.

Crosstab query – a special type of query that summarizes data based on user defined row headings and column headings.


Forms

A window or user interface where you can place controls for users to view or enter information.

Form view – the view in which you enter data.

Design view – you work with the controls and properties of the form to refine how its works and looks.

Datasheet view – looks like a table in opened in datasheet view.


Reports

Display information based on tables or queries in a format chosen by the user. Report objects can be programmed to respond to user choices. Can include information from multiple tables and queries, calculated values from database information, elements that can be formatted such as headers, footers, titles, and headings.

Design view – where you can make changes to report design as in the case of form design view.

Print preview – show exactly how a report looks when printed.

Layout preview – the same as print preview except that not all details are shown.


Macros

Used by Access to respond to an event. Macros can be executed using a VBA (visual basic for application) code through form buttons that are clicked by users. Macros automate common routines like opening and closing forms or printing of reports.


Modules

VBA programs that are more powerful than macros. VBA is a high level programming language present in every MS Office application. If you have programmed before with Visual Basic, the VBA environment and syntax would be familiar to you. VBA has hundreds of commands that can handle complex operations that cannot be handled by macros.

Microsoft Access as an Audit or Analysis Tool!

This post assumes that you already have an understanding of tables, queries, and reports in MS Access. Access may be used in audit or accounting analysis in two ways:

1. Provide your MS Access database with read only connection to your corporate databases. This can be accomplished by downloading an "Open Database Connectivitiy (odbc)" driver. The odbc driver must be specific to the type and version of your corporate database system. Ex. If your company utilizes Oracle 10g, then you must download Oracle 10g odbc driver. Ask assistance from you I.T. unit in connecting your MS Access after you have installed the driver in your PC unit, or seek assistance in installing the odbc driver if you are not allowed to install such drivers without their consent.

2. If you are not allowed to connect to your corporate database, then request for an extraction of your needed data for a specific period depending upon your need. You may request data in dbase format, foxpro format, textile or other specific format. However, most I.T. units would provide you with text file formats because this is the most safe format to convert data to in order to avoid extraction errors.

----- More on my next post on the field formats you should require on your textfile conversions from your I.T. unit and how you convert the text files into MS Access tables --------

Microsoft Access 2003 part 2

Getting to Know the Database Window

The Database Window has three (3) basic parts:

• Objects menu bar and Groups menu bar


• Toolbar


• Open pane

The image below shows the database window and its 3 parts.

The Objects Menu bar is composed of seven (7) tabs: Tables, Queries, Forms, Reports, Pages, Macros and; Modules.

Tables

The main object in any database, the purpose of which is to store information. A database object has two or more views, and tables has two views, namely design view and datasheet view.

Datasheet view – you can view and modify the table data.
Design view – you can view and modify the table structure.

Datasheet View of a table

Design View of a table