How to use access to create a simple MIS Management System _ access

An excellent way to get started with an Access database.

Reprinted from http://www.mb5u.com/biancheng/access/access_28527.html

After actual operations, it is feasible.

There must be a lot of Cd and VCD in your home, right? Is it common for you to find a CD to turn a drawer of dishes in a mess, you must not be less complained-you don't want to sort them out? For example, whether the film is made in China or outside China, whether it is romantic or gun battles. The star starred in the cards, and the director of the films. If you want, let's use Microsoft Access to build a home CD and VCD Management MIS system.

1. Preparation-determine the tables and fields required by the database

Because the MIS system we want to create is a small management system, it is not necessary to create several tables, just one table. The fields in this table include: Number, film name, leading role, Director, distribution company, film type, and whether or not to win the award. The "Number" is the primary keyword that uniquely identifies the field or field set of each record.

2. Create a database

(1) use the "Database Wizard" to create a database:

When Microsoft Access is started, a dialog box is displayed, showing the options for creating a database or opening an existing database. Click Database Wizard, and then click OK. In the create blank database dialog box, click OK, specify the name and location of the database, and click Create to start the new database. Create a new table, enter the corresponding field name and data type, and set the field size, index, and other items. Do not fill in other items or take the default value. The maximum value of a field is 255 bytes. The data types include text, remarks, values, date/time, currency, automatic number, and OLE object. You can select the value based on your requirements.

(2) do not use the Wizard to create a database

When Microsoft Access is started, a dialog box is displayed, showing the options for creating a database or opening an existing database. Click "null Database" and then click "OK ". If the database is already opened or the dialog box displayed when access is opened is closed, click the "create database" button on the toolbar and double-click the empty database icon on the "common" tab. Specify the database name and location, and click "CREATE.

3. Basic Database Operations

After the database structure is created, what we need to do next is to perform a series of basic operations on the database. Add record: Open the corresponding form in the "data table" view or "form" view, click the "New Record" button on the toolbar, type the required data, and press the tab key to go to the next field, at the end of the record, press the tab key to go to the next record.

Edit record: If you want to edit the data in a field, click the field to be edited. If you want to replace the value of the entire field, point to the leftmost field. When the cursor changes to the plus sign, click the field, enter the text to insert.

Note: If you enter an error, press the backspace key. If you want to cancel the changes to the current field, press the ESC key. If you want to cancel the changes to the entire record, press ESC again before removing this field. When you move from a record to another record, access will save the changes to this record.

Copy or move data: in other applications, such as Excel or word, select one or more rows to copy or move. If you copy data from word, make sure that the records have been arranged in the table or are separated by tabs before selection. If you paste the data to the data table, make sure that the columns in the data table are in the same order as the data to be copied or moved. If you paste the data to the form, and the corresponding control name on the form is the same as the name of the copied data column, access will paste the data to the control with the same name. In access, open the data table or form to which you want to paste the record. If you want to replace the existing record, select the record to replace, click the append or paste button on the toolbar. If the record selector in the form is unavailable, select the current record, click the "select record" command in the "edit" menu.

Delete record: Click the record to be deleted and click the delete record button on the toolbar.

4. Sort, filter, and query data

If you want to make the videos in your VCD database neatly arranged and easy to query, it is very important to know how to sort, query, and filter the databases.

Sorting records:

In the form view or data table view, click the field to be used to sort records. Right-click the field to select the ascending or descending order of the field. In a data table, you can select two or more adjacent fields for sorting. Microsoft Access sorts records from the leftmost column selected. Microsoft Access saves the sort order when saving the form or data table.

Create filter:

If you can easily find and select an instance in a form, subform, or data table that you want to filter the values in a record, you can use "filter by selected content ". Click "filter" in the "record" submenu to "filter by selected content.

If you want to select a value from the list and do not want to browse all records in the data table or form, or you want to specify multiple criteria at a time, you can use "filter by form ".

If the focus is in fields of a form or data table, and you need to enter the searched value or the expression that uses the result as the criterion, you can use "input filter target ". For more complex filtering, you can use "Advanced filtering/sorting ".

Create query:

You can use different methods to view, change, and analyze data through queries. You can also use query as the record source for forms and reports. The most common Query type is query. SELECT query to install the specified criterion, obtain data from one or more tables, and display the data in the desired order.

Specific Operation: click "query" in the database view, press "new", select design view, "OK", select table and query, add table, and then press "add ", select "field" in the "SELECT query" table, confirm the sorting requirements, and save the query. In this way, a SELECT query is complete.

You can also select the simple query wizard, cross tabulation query wizard, duplicate query wizard, and query mismatch wizard in "New" to create a query.

5. Report Creation

Maybe you want to print out your VCD database and check it at any time. If you are the owner of the VCD store, this will also be the best publicity. The report function in Microsoft Access allows you to control the size and display mode of each object in the report and display the corresponding content as needed.

Specific Operation: click "Report Wizard", select available fields in the "Report Wizard" dialog box, and click "Next". Then, select "Sort fields". Up to four fields can be sorted, click "Next", select the report layout, and click "finish ".

There are other report design methods, such as automatic report (vertical column table), automatic report (table), icon wizard, and label wizard. I will not talk about them one by one. You can try it on your own.

6. Share the database with other software

(1) There is a problem with database type conversion. Acess database files can be easily converted to other formats. After creating a new empty table, select the table and right-click it to import some other formats (mdb, xls, * dBase, etc.), but the MDF file of sqlserver cannot be transferred!

In the past, different databases often failed to share information they created. If you used to use DBASE to create a database, but because of the arrival of the Windows world, the boss issued a command to catch up with the trend. In the future, the company will not use DBASE to create a database, you need to re-enter all the databases created on DBASE to the database of Access 97. When you hear this message, you must have been crying and helpless. Even if you have one thousand reasons for using Access 97, you are reluctant to change the database. In fact, Access 97 has long been ready for countermeasures. Access 97 provides multiple methods such as calling and exporting to convert your database. Click "Convert Database" in the "Tools" menu "database utility" sub-menu, and select other types of databases to be converted, such as DBASE, the database will be successfully converted from DBASE to access
97, very simple.

(2) Call Access Data in Word

You can save the output of data tables, forms, or reports in the. RTF format file. You can use Microsoft Word, other Windows Word processing programs, or desktop publishing programs to open rich text files. For example, in Microsoft Access, Open Table 1 of the CD and VCD Management System databases, click "Save As/export", and save table 1 in the RTF format, open Table 1 at the specified position in the Microsoft Word file. you can do that.

(3) export access data to excel:

As shown in tables, in the Database window, click the name of the table or query to export, table 1, and then click the Save As/Export command on the "file" menu, in the "Save as" dialog box, click "Save as an external file or database", and then click "OK. In the Save Type box, click the expected workbook format. Click the arrow on the right of the Save location box to select the target drive and folder for export. Enter a new file name in the existing data table or in the file name box. In this case, when saving the workbook to an existing workbook, access will Delete and replace the data. Therefore, be careful. F. Click the export button. Access will create an electronic data table file containing or querying data. The field names in a table or query are placed in the first row of the workbook.

7. Protect the database

> (1) encrypt or decrypt the database

Database Encryption compresses database files and prevents them from being decrypted by tools or word processing programs. Database decryption is the reverse encryption process.

Method: Start Microsoft Access, but do not open the database. Click "encrypt/decrypt Database" in the "Tools" menu "security" submenu ". Specify the database to be encrypted or decrypted, and click OK. Specify the name, drive, and folder of the database after encryption or decryption, and click OK. If you use the original database name, drive, and folder, after encryption or decryption is successful, Microsoft Access will automatically replace the original database with the encrypted or decrypted version. If an error occurs, Microsoft Access retains the original database file.

(2) set a password for the database:

Method: Open the database, select the "exclusive" check box, click "open", click "set Database Password" in the "Tools" menu "security" submenu, and enter the password for verification, finally, click "OK" to complete the password set for the database. When you open the VCD database, You Need To prompt you to enter the password.