MIS Management SystemIt is also a very practical management system. It can put many things in an orderly manner, so that everyone can search. The following article will take CDs and VCDs that everyone has at home as an example to introduce how to establish a MIS management system to make these things organized.
Access creates a MIS management system:
1. Preparation – Determine the tables and fields required by the database
Since the MIS system we are creating is a small management system, there is no need to create several tables, just one table is enough. The fields in this table include: number, film name, leading actor, director, distribution company, type of film, and whether the award is won. Where "number" is the primary keyword, the keyword uniquely determines the fields or field sets 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 automatically displayed with the option to create a new database or to open an existing database. Click the Database Wizard and then click the OK button. In the New Empty Database dialog box, click OK, and then specify the name and location of the database, and click the Create button to start the newly created database. Then create a new table, then fill in the corresponding field name and data type, and set the field size, index and other items. Do not fill in the other tables or take the default values. The maximum setting value of the field is 255 bytes, and the data types include text, notes, values, date/time, currency, automatic numbering, OLE objects, etc. You can choose according to different requirements.
(2) Create a database without using the wizard
When you start Microsoft Access, a dialog box appears automatically with the option to create a new database or open an existing database. Click Empty Database, and then click OK. If the database is already open or the dialog box displayed when Access is open is closed, click the New Database button on the toolbar and double-click the empty database icon on the Commons tab. Specify the name and location of the database and click the Create button.
3. Basic database operations
The database structure has been created, the next thing we need to do is to perform a series of basic operations on the database. Add records: 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 you want to edit, if you want to replace the value of the entire field, point to the leftmost of the field, when the mouse changes to a plus sign, click the field and type the text you want to insert.
Note: If you type incorrectly, press the BACKSPACE key; if you want to cancel changes to the current field, press the ESC key; if you want to cancel changes to the entire record, press the ESC key again before moving out of the field. When moving from one record to another record, Access saves the modifications to the record.
Copy or move data: In other applications, such as Excel, Word, select one or more rows to copy or move. If you are copying data from Word, make sure that the records are arranged in the table before selection or separated by tabs. If you paste data into 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 data on the form and the corresponding control name on the form is the same as the column name of the copied data, Access will paste the data on 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 an existing record, select the record you want to replace, and then click the Paste Append or Paste button on the toolbar; if the record selector in the form is unavailable, to select the current record, click the Select Record command in the Edit menu.
Delete Records: Click the record you want to delete and click the "Delete Records" button on the toolbar.
4. Sorting, filtering and querying of data
If you want to place the videos in your VCD database neatly and query easily, it is very important to master the sorting, query and filtering of the database.
Sort records:
In Form view or Data Table view, click the field you want to use to sort records. Right-click and you can select the ascending or descending order of the field. In the data table, two or more adjacent fields can be selected simultaneously for sorting separately. Microsoft Access sorts records from the leftmost column selected. When saving a form or data table, Microsoft Access saves the sort order.
Create a filter:
If you can easily find and select instances of the values that the record contains in a form, subform, or data table, you can use Filter by Selection. The operation method is: click "Filter by Selected Content" in the "Record" submenu.
If you want to select the desired value from the list without browsing all records in the data table or form, or you want to specify multiple criteria at once, use Filter by form.
If the focus is in a field in a form or data table, where you happen to need to enter the searched value or the expression to use its result as a criterion, use "Input Filter Target". And if it is a more complex filter, you can use Advanced Filter/Sort.
Create a query:
Queries can be used to view, change and analyze data in different ways. You can also use queries as a record source for forms and reports, the most common query type is to select queries. Select the query to install the specified criteria, get data from one or more tables, and display it in the desired order.
Specific operations: Click "Query" in the database view, press "New", select the design view, "OK", select tables and queries, add tables, and then press "Add", select "Fields" in the "Select Query" table, and determine the sorting requirements, and finally save the query. In this way, a selection query is completed.
You can also select the Simple Query Wizard, Crosstab Query Wizard, Find Duplicate Query Wizard, and Query Mismatch Query Wizard to create a query in "New".
5. Report production
Maybe, you want to print out your VCD library and check it out at any time. If you are the owner of a VCD store, then this will also be the best publicity. The report function in Microsoft Access can be achieved. It can control the size and display of each object in the report, and display the corresponding content in the desired way.
Specific operations: Click "Report Wizard", select available fields in the "Report Wizard" dialog box, press "Next", and then select field sorting, and up to four fields can be sorted, "Next" select the layout of the report, and finally press "Finish".
There are also other reports design methods, such as automatic reports (column table), automatic reports (tables), icon wizard, and label wizard. I won’t talk about them one by one, you can try them yourself.
6. Share the database with other software
(1) Database type conversion
In the past, different databases often failed to share the information they created. If you used to use dBase to build a database, but because of the arrival of the Windows world, the boss gave an order to keep up with the trend. In the future, the company no longer uses dBase to build a database, and you also need to re-enter all the databases established on dBase into the Access 97 database. After hearing this message, you must be crying and being at a loss. Even if there are a thousand reasons to use Access 97, you are reluctant to change the database. In fact, Access 97 has long thought of countermeasures for this. Access 97 provides various methods such as calling and exporting to enable you to convert databases. You just click "Convert Database" in the "Tools" menu Database Utilities" submenu and select another type of database to convert, such as dBase, and the database will be successfully converted from dBase to Access 97, which is very simple.
(2) Call Access data in Word
The output of a data table, form, or report can be saved in an RTF format (.rtf) file. RTF format files retain formatted information such as fonts and styles, and can be opened with Microsoft Word and other Windows word processors or desktop publishing programs. As in the previous example, in Microsoft Access, open Table 1 of the CD and VCD management system database, click "Save as/Export", save Table 1 in RTF format, and then open the table in the specified location of the Microsoft Word file.
(3) Export Access data to Excel:
As shown in the previous example, in the database window, click the name of the table or query you want to export, Table 1, and on the File menu, click the Save As/Export command, in the Save As dialog box, click Save As an external file or database, and then click the OK button. In the Save Type box, click the desired spreadsheet format. Click the arrow to the right of the Save Location box to select the exported target drive and folder. Enter a new file name in the existing spreadsheet, or in the File Name box. At this time, be careful when saving to an existing spreadsheet, Access will delete and replace the data in it, so be especially careful. fClick the Export button and Access will create a spreadsheet file containing the table or query data. The field names in the table or query are placed on the first row of the spreadsheet.
7. Protect the database
(1) Encrypt or decrypt the database
Encrypting the database will compress the database file and make it impossible to decrypt it through the tool program or word processor. Database decryption is an anti-process of encryption.
Method: Start Microsoft Access, but do not open the database. Click Encrypt/Decrypt Database in the Security submenu of the Tools menu. Specify the database to encrypt or decrypt, and click the OK button. Specify the database name, drive, and folder after encryption or decryption, and then click the OK button. If you use the original database name, drive, and folder, after the encryption or decryption is successful, Microsoft Access will automatically replace the original database with the encrypted or decrypted version. However, if an error occurs, Microsoft Access will retain the original database file.
(2) Set a password for the database:
Method: Open the database and select the "Exclusive" check box, click "Open", and then click "Set database password" in the "Security" submenu of the "Tools" menu, enter the password, verify, and finally click "Confirm" to complete the password set by the database. When opening the VCD database, you must prompt for the password.
After a few steps above, we can easily create a MIS management system in Access, which provides convenience for everyone to find things in the future and save time. I hope you can like the knowledge points introduced above.
For more study materials, please pay attention to the topic "Management system development》。