Microsoft Access;5 Facts You Must Know

Microsoft Access . System Management Database for Microsoft Windows which offers you the ability to organize, find and present information , taking advantage of the maximum graphics power of Windows offering visual methods of access to their data and providing simple ways and direct filing and work with your information.

Summary

[ hide ]

• 1 Database
• 2 Basic concept and terms
  • 1 Tables
• 3 Field properties
  • 1 Field Size
• 4 Format
  • 1 Decimal places
  • 2 Input mask
  • 3 Title
  • 4 Default value
  • 5 Validation rule
  • 6 Validation text
  • 7 Required
  • 8 Indexed
• 5 Queries
  • 1 With Queries you can:
• 6 Types of queries
  • 1 Selection queries
  • 2 Cross reference table queries
  • 3 Consultation of actions
  • 4 Union query
  • 5 Pass-Through Query
  • 6 Data definition query
• 7 Queries for advanced users
  • 1 Parameter Queries
  • 2 Queries of Cross Reference Tables
  • 3 Specify column headings
• 8 Forms
  • 1 The forms are useful for:
  • 2 How to create a form?
  • 3 With the help of the wizard you can create:
• 9 Reports
  • 1 Operation of reports
• 10 Controls
• 11 Macros and modules
  • 1 Macros
  • 2 Modules
• 12 Source

Database

A database is a set of information related to a particular topic or purpose. Employee records, lists of potential customers and suppliers constitute a database.

A Database Management System (DBMS) is a system that stores and retrieves information in a database. This program can be used to store and retrieve data on your computer .

Microsoft Access is a Related Database Management System that stores and retrieves information according to relationships that you define in the relational Database of Microsoft Access. Each data is stored in one place, which saves disk space by eliminating duplicate records.

Updating information is faster and more accurate, since changes are made in one place. Restructuring a small table in DBMS is easier than restructuring a large table in a non-related database, and you can add tables for new information topics without changing existing tables.

Concept and basic terms

Boards

A table is a set of data about a particular topic. The data in the table is represented in columns (fields) and rows (records), all the information in the table describes the subject of the table. For example, one table can store data about the products that a company sells, another can contain customer information, and a third table could include data from suppliers.

In a table a field is a category or type of information, they can be company names, product prices, customer addresses, etc. A record is a set of information about a person, thing, or event. It can be all the information of a product, a transaction or a supplier.

Each record in a table contains the same field record, and each field contains the same type of information for each record. Field values ​​can be used to filter the information you want to see.

A field can have a maximum of 64 characters (letters or numbers), including blank spaces, but this facility, despite being more descriptive, can make it difficult to use it in expressions, SQL statements and Access Basic, therefore short and easy-to-use names are recommended. remember.

By choosing the type of data the field will store, you are deciding the following:

• Values ​​allowed in the field.
• The amount of space that Access will reserve for the field, for example: the currency type uses 8 Bytes.
• The types of operations you can perform on the field values.
• If Access will be able to index or order the values ​​of the field, for example. this is not possible for memo fields or Ole objects.

Field properties

Field Size

Sets the maximum size of data that can be stored in a field. For text data up to 255 characters, for numeric it can be Byte, integer, long integer, single, double.

Note: use the currency data type , if many result in a field of up to 4 decimal places. Single and double fields operate on floating point and currency fields on fixed point which is faster and avoids rounding errors.

Format

It presents files and numbers in a certain format such as long date or currency #, ## 0, – #, ## 0, “zero” if the value is zero, “unknown”, null value, positive values.

Decimal places

Determines the number of decimal places Access uses to display numbers.

Entry mask

Present formatting characters in a field so you don’t have to type them in and make sure that the data entered fits the mask.

Title

Specifies the text of the labels attached to the controls that were created by dragging a field from the field list on new forms and reports. It also serves as a column heading when the table or query associated with a table is in “data sheet” presentation mode; if omitted, the field name is taken.

Default value

Automatically populates a field with a certain value in the new records that you add to the table. It is not valid in fields whose data type is counter or OLE object.

Validation rule

Limits data entered in a field to values ​​that meet a certain condition (expression).

Validation text

Specifies the text of the message that appears if a field in the table does not meet the conditions listed in the validation rule.

Required

Requires entering data in a field.

Indexed

Speed ​​up the search in the fields where you search most frequently.

Note: Memo, YES / NO, OLE fields cannot be indexed. To create multi-field indexes, use the indexes window.

In the attached tables it is only possible to set the format, decimal places, input mask and title properties. The remaining properties are not applicable, however, if you have attached tables from another Access database , you will be able to set all the properties in the original database.

Inquiries

A query is a question you ask about the information contained in your database , example: what products do Australian suppliers have?

The data that answers the question can come from a single table or several, or even from another query. The query gathers the requested information, the set of records that respond to the query is called: “Dynamic Answer Sheet”.

A dynamic response sheet is an updateable type of recordset, which is any recordset defined by a table or query.

By creating a query that describes the recordset you want to achieve, updated data is automatically obtained from the tables. If the data displayed in a dynamic response sheet is modified, Access updates it in the underlying tables in a multi-user environment, to immediately see in a dynamic response sheet the changes that other people make in the records.

Since the data in a dynamic answer sheet is always up-to-date, you can use queries and their corresponding dynamic answer sheets with the assurance that the data presented is up to date and that any changes you make are appropriately saved in terms of data access .

You will see that there is not much difference between the way tables are used and the way dynamic answer sheets are used. The main difference between them is that the information is actually stored in the tables and not in the dynamic sheets.

The real power of a database lies in its ability to display the data you want and in the order you need it. With the queries you can ask questions about the data stored in the tables.

The data of a query can come from one or several tables or even queries. Queries can be used as the basis for a form, report, graph, or other query.

With Queries you can:

1. Choose fields: It is not necessary to include all the datain a table in a query .
2. Choose records: You can specify criteria that records must meet.
3. Sort records: you can view the records in a certain order, example: alphabetically order the names of customers. Ask questions about data from various tables).
4. Answer a question about data from more than one table and view the data in a single datasheet, you can even ask about data from other databases like Microsoft FOXPRO, PARADOX, DBASE, BTRIEVE, as well as SQL SERVER from Microsoftor SYBASE.
5. Perform calculations: New fields can be created, called calculated fields that contain the result of a calculation.
6. Use a query as the data source for a form, report, and other queries.
7. To select only the data that should appear in a form or report. You can create a select query and use it as the source of the form or report data that can include data from multiple tables and set criteria to display only a limited set of data. Every time you open the form or print the report, the query will retrieve updated information from the tables.
8. Enter new data or modify existing data directly in the data sheet of the query or in a form that is based on this query.
9. Modify the data in the tables
10. Update, delete or add a group of records simultaneously or you can also create a new table containing records from other tables.

Types of queries

Selection inquiries

They are the most frequently used. With them you can see data from the tables, analyze them and even make changes to them. Access collects the requested data into a Dynamic Answer Sheet that behaves like a table and looks the same but is not. Through these Dynamic Answer Sheets, the data that affects the underlying tables is modified.

Cross reference table queries

Present the data with titles in the rows and columns, just like in a spreadsheet. This type of query can summarize large amounts of information in an easy-to- read format .

Stock consultation

Modify many records in one operation. It is used to create a new table, delete records, add or modify them in a table.

Union query

Combines matching fields from two or more tables in which the participating tables have to return the same number of fields and in the same order; the result is a snapshot “Snapshout”, which is a type of non-updateable recordset.

Pass-through consultation

Sends commands to an SQL database , such as Microsoft’s SQL SERVER or SYBASE.

Data definition query

Create, modify, or delete tables from an Access database using SQL statements.

Queries for advanced users

Parameter Queries

If you frequently run the same select query but change the criteria every time you run it, you will save time if you create a parameter query.
For the parameters you can select a data type and these can be criteria defined in a form.

Cross Reference Table Queries

You can create these queries to summarize the data in a compact spreadsheet-like format; It is useful as a basis for a report.
Creating a Cross Reference Table Query without a wizard is similar to creating Queries with Totals.

You must specify which field to use for row headings, which field to use for column headings, and which field will supply the values. When executed this will return a snapshot which is a non-updateable recordset.

Specify column headings

When you run a crosstab query, you may want to have more control over the appearance of the recordset column headings. By default they are arranged in alphabetical or numerical order.

You can define the column titles properties for:

• Specify the order of the column headings (Ex .: If these headers are names of countriesmay want to see them in a certain order. These must exactly match the column names)
• Improve performance (If the query is run very frequently or is used as the basis for a form or report, you can speed up its execution by specifying fixed column headings.
• Specify which columns to display in case you want to display information even if the query did not return information for any columns.

Forms

A form is often the best design for entering, changing, and viewing the records in your database . Designing a form specifies how the data is presented. When opening a form, Access retrieves the desired data from the tables and presents them according to its design, either in the template or in printed format. In a form it is possible to include lists of values ​​from which you can choose, use colors to highlight important data, and present messages to validate if you have captured an incorrect value, you can also have Access automatically include data and present the results of calculations. It is used to enter, modify and view the data using the form itself.

The forms provide an easy way to view the data. You can view all the values ​​in a record in form display mode, or you can switch to datasheet display mode to see all records in the form.
The use of Form is also an efficient way of entering data and for this it has various design elements ( text , data, images , lines and color ) as well as for viewing and modifying them.

The forms are useful for:

• Present datain an attractive format, with special fonts and other graphic effects, such as colors and shading.
• Offer an appearance similar to the paper forms you already know.
• Calculate totals
• Show data from multiple tables
• Automate tasks you routinely perform.

The quickest way to understand how a form works is to look at it and see the data source. Some of the information in a form can come from a table or query, although it can also be independent, such as: a Logo.

The appearance of a form is stored in the design of the form. All the information in a form is contained in “controls”. These display data, perform actions, or decorate the form. Some controls rely on existing fields in the underlying table or query, and allow you to enter, edit, or view data in the fields. For example: you can enter and display names and numbers using text boxes, while object frames are used to view images.

How to create a form?

Forms can be created with or without the help of wizards.

Wizards speed up the creation process by automatically doing all the basic work.

With the help of the wizard you can create:

• Automatic Form: The fastest way to create a Form. This wizard does not ask you for information by immediately creating a single column, presenting all the existing fields in the underlying table or query.
• Single Column Form – Displays the values ​​of the records in a single column, includes each value on a separate line, and presents one record at a time.
• Tabular Form: Displays the values ​​of the records using a row and column format. This form shows multiple records at once.
• Main / Subform form displays a relationship of “one to many” between the dataof main form and the subform data. Presents in a single column the data from the main form (end “one”) and displays the data from the subform (end “several”) in datasheet format.
• Graph: a graphical form shows the data in graphical format such as bar graphs, columns, pae, etc.

Reports

A set of information that is organized according to certain criteria and where a format is applied according to its specifications, so that it represents significant information that can be used and distributed. Many design elements can be used for this, such as: text , images , lines, boxes and graphics .

A report is used to present the data on a printed page and to display the subtotals and totals for any set of records with publication-friendly quality and minimal effort.

Creating a report is a very effective method of presenting data in printed document form. While it is also possible to print the forms and data sheets, the reports provide more control over how the data will be presented, thus providing greater flexibility in presenting summary information. For example: in a report showing sales by product category, you could add up the total for each category and calculate the percentage each category represents of the total sum.

Reports operation

The quickest way to understand how a report works is to look at a printed model and examine its design. Some of the information in a printed report may come from an underlying table or query (source of the report data) there is also information from the report that is stored as part of your layout.

• Automatic Reports: the fastest way to create a report. It will not request information but will create a report immediately. The report created is already preformatted to a single column (default) and displays all the fields in the underlying table or query.
• Groups / Total Reports: Organizes the data into groups and displays them using a tabular form (rows and columns). You can calculate a total for each group and one for all groups.
• Mailing Label Reports – Prints names and addresses in a format suitable for your label paper.

Controls

The elements of a form or report that allow you to present or print the data are called controls. With a control, you can present the data of a field, the results of a calculation, the text for the title or for a message, or a graphic, an image or another object, even another form or another report.

Macros and modules

Access has a wide variety of objects that allow you to display and manage information . Your database can be an even more powerful productivity tool when using these objects together. It is possible to use Macros and Modules to change objects that adapt to the needs of each user.

Macros

A macro automatically performs a task or series of tasks. Each task is called an “action”. Access offers a list of actions, from which you select one or more to create a macro. Access performs the actions in the same order in which they are contained within the Macro, using the specified objects or data.

Any repetitive or routine task that Access performs is a good candidate to run from a Macro. Automating routine tasks will achieve greater efficiency and precision in the database, since a macro always performs the task in the same way.

Macros allow:

• Make your forms and reports work together, eg create a button on a form to print a report.
• Search and filter records automatically, macros can speed up the search process for the records you want to see eg. , in an employee form you can attach macros to buttons so that they automatically filter the records to present them in subgroups

(see examples of Neptune supplier forms).

• Set Values ​​in Controls: Macros allow you to assign a form control the value resulting from a calculation or a value from another table.
• Ensure the accuracy of the data: macros are ideal for data handling and for linking them in forms.
• Set forms, reports, and controls properties: Macros can be used to set most forms, reports, and controls properties.
• Automate datatransfers : you can use a macro to import or automatically export your data between different file formats, eg to Excel .
• Create your own custom work environment.
• With macros, you can combine individual parts of a database to integrate them into an application.

Even without knowing how to program, macros can be used to automate basic actions and make objects work together. An Mmacro is a list of actions that you want Access to perform automatically, for example opening a set of forms or printing a report. These can be used in a variety of places. For example, you can attach a macro to a form, report, control, keystrokes, or menu commands.

Modules

To gain maximum control over the operation of your databases, Access incorporates a powerful database programming language called Access Basic. Procedures written in Access Basic allow you to solve operations that require a more complex automated process than macros do.

A module is an Access object that contains the Access Basic procedures that you write.

TIP: You can use an Access wizard to automate forms without writing macros or modules. These assistants are database experts who ask you questions about the object you want to create and generate it based on the answers you provide. Using the wizard for buttons named Command Button, the user is asked to choose the action that the Button will execute (ex: open a form)