OPTIMIZE YOUR WORKING MODE
Having the appropriate information allows you to make better decisions, thus improving the productivity of the company. Microsoft Access 2000 is the best way to make your data always easy to find and use, thus enabling instant analysis of details and verification of critical relationships. Take advantage of your new report and form customization tools to turn raw numbers into quick responses to your needs.
Microsoft Access 2000 makes it easy for you to share information and provides you with powerful tools to organize and share your databases so you can make better decisions. Find the answers you need, share information across your intranet, and build business solutions faster and easier with Access2000.
ENTERING ACCES 2000
DESCRIPTION:
- Click Start - Programs - Access The window will display:
Create a new database using…. " • Click on:
"Access Database Blank"
- Click OK
CREATING A DATABASE IN ACCESS 2000
DESCRIPTION:
- Type your name or any word you want in:
"Filename:"
- Click on the Create icon The Database will be created by Default in the folder
"My documents", and will contain the extension ".mdb"
DATABASE READY TO START
A database is a collection of information relating to a particular matter or purpose, such as tracking customer orders or maintaining a music collection. If the database is not stored on a computer, or only parts of it are installed, you may need to keep track of information from various sources in order to coordinate and organize the database.
Using Microsoft Access, you can manage all your information from a single database file. Within the file, you can divide the data into separate storage containers called tables; you can view, add, and update table data using on-screen forms; you can also search and retrieve only the data you want through queries; and you can analyze or print data with a specific layout using reports. You can allow other users to view, update, or analyze data in the database from the Internet or from an intranet through data access pages.
MAIN PARTS OF THE DATABASE
- Tables.Consults.Forms.Reports.Macros.Modules.
Boards.
Concept:
They are accumulators of records, which consist or we can represent them as columns (field names) and rows (records).
These records can be entered directly into the table or through one or more forms.
They also have the property of entering in them (Tables) data of different types, such as: Text, Memo, Numeric, Date / time, Currency, Autonumeric, Yes / No, OLE Object and Hyperlink, which helps to maintain integrity of the Table and its records.
Creating a Table:
- In the database created, click on "create a table in design view". Subsequently, it presents a new window called table1: Table, with the fields "field name", "data type" and "Description". in "field name" the following fields:
- Employee ID wage date of entry observation
- Let us choose in "Data type" for each of the fields the following:
- Numeric Text Currency Date / Time Memo
Next we will close the table, clicking on the upper right x of the Table1 window.
Immediately a sub-window will appear in which we will type the name that we will give to the Table and click on accept.
Then we will click on the table created in the Database to bring up the window with the table ready to enter the data in each field.
CREATE A TABLE USING THE WIZARD
- In the Database we will click on "Create a table using the Wizard" or "Wizard". It shows a sub-window which will help us to create a new table. This contains example tables and example fields which can be chosen According to our need or requirement, we will choose some fields:
with the icon> they will be selected one by one or to select them all we will do it with the >> icon.
To remove unwanted or mistakenly selected fields we will do so with the <icon which will remove them one by one or, failing that, the << icon will remove all those already selected.
- We can also change the name of the field or fields that we want by simply selecting it and clicking on “Change name of the field”. After having chosen and changed the names of the fields we click on Next> The wizard asks us the name that we should Give the table, as well as if we want to assign it a primary key or primary field. Again we click Next> Finally the wizard shows us three options:
Modify the table layout.
Enter data directly into the table
Enter data in the table using a form that the wizard creates.
- For the purposes of creating the table, we will click on the second option and then Finish. So Access will show us a table with the fields we chose in the same way that we did it step by step without the wizard.
Forms.
Concept:
They are input screens for the records of a table with the difference that the fields are displayed graphically within the form, in other words, the interface is more user-friendly.
In the form, not only can there be fields where simple data can be entered, but also fields can be created that calculate data, percentages and complex operations.
In short, a form contemplates a variety of uses, which can be to enter records, calculations, query and all the requirements that the user wants in an Access project.
Creating a Form:
- In the database created previously, let's look at the Objects column and click on "Forms". Next we are presented with two items: "Create a form in design view" and "Create a form using the wizard". Click on the second item. It immediately presents us with a new window called "Form Wizard". This window has three options which are:
- Tables / Queries Available fields selected fields
- In the first option we will select one of the two existing tables, in the second option we will choose all the fields:
- Employee ID wage date of entry observation
- With the icon> one will select one by one or to select them all we will do it with the icon >>. We click on Next> It presents us with four options, which are the ones that will give us the location of each field of the Table in the form, but For uniformity of the exercise we will select “justified”. We click Next> Next, the wizard shows us the types of style that we can give to the bottom of the form, select one and click Next> Finally the wizard asks us for the name we want to give it to the form and shows two options:
Open the form to view or enter information.
Modify the design of the Form.
- For the purposes of creating the Form, we will click on the first option and then on Finish. So Access will show us a Form with the same fields as the Table, so that we can start using it by entering records.
INTRODUCTION ACCESS 2000
MODULE II
CREATING A DATABASE TO GET STARTED
To begin this Module we will create a New Database that will contain the following:
- A Table A Form A Report
in order to see the result of the exercises that will be carried out in the course.
- A table with at least 5 fields; This table will contain at least 20 records. It must contain continuous dates from one month to another, example: 02/01/01
02/02/01 The first 10 records or 02/03/01 or 02/04/01…. or 03/01/01
03/02/01 The next 10 records or 03/03/01 or 03/04/01….
- In addition, we will prepare a form either in design view or using the wizard, and finally we will make a report that will facilitate the view of the records resulting from each exercise.
OBJECTS OF THE B.. OF ACCESS 2000
Boards.
Queries.
Forms.
Reports. Pages. Macros.
Modules.
- In this module we will study the following items:
Queries, Links, Macros, Sub-Forms, Sub-Reports and Web Pages in Access.
Queries.
Concept:
They are parameters and criteria that are used to filter, view, modify and analyze data in different ways. They can also be used as the record source for forms, reports, and data access pages.
A select query is the most common type of query. This type of query gets the data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.
A select query retrieves data from one or more tables using criteria that you specify and then displays it in the order that you want.
A parameter query is a query that, when run, displays its own dialog box that prompts for information, such as criteria for retrieving records or a value that you want to insert into a field. You can design the query to request more than one piece of information; for example, you can design it to ask for two dates. Microsoft Access can then retrieve all the records that remain between those two dates.
Parameter queries are also useful when used as the basis for forms, reports, and data access pages. For example, you can create a monthly income report based on a parameter query. When you print the report, Microsoft Access displays a dialog box requesting the month for which you want the report. When a month is entered, Microsoft Access prints the appropriate report.
CREATING A QUERY
- In the database created, click on “create a
Consult using the wizard ”.
- Later it presents us with a new window called
"Wizard for simple inquiries".
- It contains Tables / Queries and available fields from which they are formed, which can be chosen according to our need or requirement. We will choose some fields:
the >> icon will select all the fields.
To remove unwanted or mistakenly selected fields we will do so with the <icon which will remove them one by one or, failing that, the << icon will remove all those already selected.
- After having chosen the names of the fields, we click Next> Next, it asks us if we want a query that presents the records in detail or in summary, which we will select according to our requirement. We click Next> To finish the creation of the query, it asks us what name we want to give it and if we want to open it to "see the information" or "modify the design…". To create the query, we will click on the second option and then on Finish. Then we maximize "
query to have a better visibility of your form.
- As we can see, it presents us with the DB fields box at the top, and the fields already displayed at the bottom. Additionally, it shows us the following items:
o Order: refers to whether the records will be sorted in ascending, descending or no order.
o Show: Indicates if the records will be seen when executing the query.
o Criteria: They are the location where the parameter that will govern the search or filter will be placed when executing the query.
o O: It is the place where additional criteria for the query will be located.
- In the field that contains the dates previously entered in the table and placing ourselves in the place of the criteria, we will type the following:. Next, we will click on the execution icon. Immediately afterwards a message box will appear asking you to "enter date ”, let's type the following: 02/02/01. Click OK
We can see how the query shows us the result of the parameter set in the criteria, that is, records with the date specified by us.
Let's try some other example parameters:
(each is an independent parameter example)
- In the Name field, in the Criterion row, let's type the following:
Criterion: C * (or any other letter)
Criterion: o: Is Null
Criterion: Denied "JUAN…." (type any name you don't want listed or displayed when running the query)
- In the Date of Entry field, in the Criteria row, let's type the following:
Criterion: # 02/02/01 #
Criteria: Between # 01/02/01 # and # 28/02/01 #
Criterion:> = # 01/03/01 # and <= # 31/03/01 #
- In the Salary field, in the Criterion row, let's type the following:
Criterion:> 4000
Criterion:> 4000 AND <= 6000
►The Assistant or Wizard for Queries:
- In addition, the wizard has several types of queries that help maintain the data in the tables with greater integrity and solvency for the final reports and reports.
Links or Links between Tables.
Linking data, which is a method of connecting to data from another application without importing it, so that the data can be viewed and modified both in the original application and in an Access database. In earlier versions of Microsoft Access, this process was called "attach."
In an Access database, the data bind operation allows you to read and, in most cases, update data in the external data source without the need for an import. The external data source format remains unchanged, so you can continue to use the file with the program that originally created it, and you can also add, delete, or modify data using Microsoft Access.
Microsoft Access uses different icons to represent linked tables and tables stored in the current database. Removing the icon for a linked table will remove the link to the table, but not the external table itself.
You can link tables from other Microsoft Access databases. For example, you may want to use a table from another Microsoft Access database that is shared on a network. This is particularly useful if you want to keep all of your tables in one database on a network server, while keeping forms, reports, and other objects in a separate database that is copied between users of the shared database.
CREATING A LINK BETWEEN TABLES
- To begin we must have the DB. Open. Next, click on File + Obtain external data. As we can see, it shows us the options Import… and Link tables… We will choose Link tables… Next, it presents the "Link" window where we can select the DB. that contains the table to be linked. Click on Link. It immediately shows us the window that contains the names of the tables that the database has. to link. We click to choose the table we need. We click OK. Note that the linked table differs from the one created in the DB. destination with a "" which indicates that it is only related to the DB. And so the table is already linked to be used to create queries, forms and reports based on it. We double click on The Table to see its records.
Macros
A macro is a set of one or more repetitive actions that each perform a certain operation, such as opening a form or printing a report. Macros can help automate common tasks. For example, you can run a macro that prints a report when the user clicks a command button
A macro can be a macro made up of a sequence of actions, or it can be a group of macros. A conditional expression can also be used to determine whether an action will take place in some cases when the macro runs.
Macros are an easy way to do simple tasks like opening and closing forms, showing or hiding toolbars, and running reports. They allow you to quickly and easily link the database objects created, since it is not necessary to remember much syntax; the arguments for each action are displayed at the bottom of the Macro window.
In addition to the ease of use, you can use macros to Run an action or series of actions when a database is first opened. Also, you can use the Home dialog box to perform certain operations when a database is opened, such as when a form is opened.
CREATING A MACRO
- In the Database window, click Macros under Objects. Click the New button on the Database window toolbar. In the Action column, click the arrow to display the list of actions. Click in the action "open form". Enter a comment for the action. Comments are optional but will make the macro easier to understand and maintain. At the bottom of the window, specify the action's arguments, if any are required. To add more actions to the macro, we'll move to another row action and repeat the previous steps. Microsoft Access performs the actions in the order in which they are listed. Click the “MsgBox” action. Enter a comment for the action. At the bottom of the window, specify the arguments for this new action.Click on X to close the Macro. Now click on Click OK to Finish. Click the icon, or double click directly on the Macro. That way you can see the result of the Actions created in said Macro. Click in accept.
Sub-Forms and Sub-Reports.
►Sub-Form:
A subform is a form within a form. The parent form is called the main form, and the form within the form is called the subform. A form / subform combination is often called a hierarchical form, parent / detail form, or parent / child form.
Subforms are very powerful when you want to display data from tables or queries in a one-to-many relationship. For example, you can create a form with a subform to display data from a Categories table and a Products table. The data in the Categories table is the "one" side of the relationship. The data in the Products table is the “miscellaneous” side of the relationship, as each category has more than one product.
A main form can have any number of subforms if you place each subform on the main form. You can nest up to ten levels of subforms. This means that you can have one subform within a main form, and that you can have another subform within that subform, and so on. For example, you might have a main form that displays customers, a subform that displays orders, and yet another subform that displays order details.
CREATING A SUB-FORM
- To begin, we must locate what our main form will be. Later, we will click on the icon to start working on the link with the sub-form. It should be noted that there must be two forms, one that will contain the other. The form window in design view, and in turn the DB window. We will click on Window + Vertical Mosaic in the upper menu bar. By having the two windows in vertical view we expand the footer of the form a little, which will be where place the SubForm. In the DB window we will click and hold on the form that will serve as Sub-form and we will drag it to the space previously expanded in the main form. We will release the Mouse and the sub-form will be created.We maximize the window to get better visibility of the form and to be able to locate and enlarge the Sub-Form as required. The Sub-Form should not necessarily be seen in its entirety. Click on the View icon to see how the form looks. This is the final product of the creation of the Sub-Form. As we can see, the main form has its “Scroll Buttons”, as well as the SubForm, which allow navigating between the records contained in each one of them.As we can see, the main form has its "Scroll Buttons", as well as the SubForm, which allow you to navigate between the records contained in each one of them.As we can see, the main form has its "Scroll Buttons", as well as the SubForm, which allow you to navigate between the records contained in each of them.
►Sub-Report:
A subreport is a report that is inserted into another report. When combining reports, one of them must serve as the main report. A main report is dependent or independent; that is, whether or not it is based on a table, query, or SQL statement.
A separate main report can serve as a container for unrelated subreports that you want to combine.
You can use the main report to display detailed records, such as all sales for a year, and use a subreport to display summary information, such as total sales for each quarter.
A main report can also contain data common to two or more parallel subreports. In this case, the subreports contain the detailed records related to the common data.
CREATING A SUB-REPORT
The procedure for creating the Sub-Report is in the same way as that carried out in the creation of the Sub-Form, so only the final product of how said report will be constructed will be shown below.
- As you can see, the Main Report now has a Sub-Report which allows you to see the records contained in each of them.
Web Pages In Access 2000.
A data access page is a Web page that can be used to add, modify, view, or manipulate current data in a Microsoft Access or SQL Server database.
You can create pages that will be used to enter and modify data, similar to Access forms.
You can also create pages that display hierarchically grouped records, similar to Access reports.
You can create a data access page as a Database object that contains a shortcut to the location of the corresponding HTML file for the page.
HTML (Hypertext Markup Language)
System to mark or add additional information to documents so that they can be published on the World Wide Web. Documents prepared in HTML include reference graphics and formatting marks. Use a web browser (such as Microsoft Internet Explorer) to view these documents.
You can use pages to view, modify, update, delete, filter, group, and sort active data in a Microsoft Access database or Microsoft SQL Server database, in Microsoft Internet Explorer 5.0 or later. A page can also contain additional controls including a spreadsheet, a pivot table list, and a chart.
CREATING A PAGE USING THE WIZARD
- In the Database window, under the Objects section, click Pages. Click the New button on the Database window toolbar. In the New Data Access Page dialog box, click Click Page Wizard. Click the name of the table, query, or view that contains the data on which you want to base the data access page. Click Next. Follow the prompts in the wizard dialog boxes. the page you get does not look the way you want, you can modify the page in Design view.
When you create a page using a wizard, Microsoft Access automatically saves the page as an HTML file in the current folder, and adds a shortcut to the page in the Database window. Hover the mouse pointer over the shortcut in the Database window to display the path to the file.
"THE SUCCESS OF THE PRESENT IS FOR THOSE WHO TAKE ADVANTAGE OF THE TECHNOLOGY OF THE FUTURE TODAY"
As an update we suggest the following Access 2010 Free Video Course, basic and advanced level.
Through the following video-course (24 lessons, 10 hours), from pildorasinformaticas.com, you will be able to introduce yourself to the basic environment of Access 2010. Enjoy it.
To finish and continue with the learning of this database administration software, below we leave you with another video-course (29 lessons, 12 hours), also from pillsinformaticas.com, so that you can become an expert in Access 2010 Happy learning.
Download the original file
