Getting Started with Microsoft Access
You have discovered that Microsoft Excel is an excellent tool to store data and play around with it as needed. But your supervisor found out that not everybody is as comfortable in inputting data and generating queries or reports as you. Therefore, he has decided to use another tool called Microsoft Access and train them on the same. You have also been asked to attend the training and gain knowledge about Microsoft Access.
At the end of the week-long training, your manager asks you the following questions:
- What kinds of tasks is Microsoft Access best suited for?
- Did you face any difficulties while creating a database?
- How would you overcome these difficulties?
- What in your opinion is the most significant reason for integrating Microsoft Access with other sources of data? Give reasons and examples to support your answer.
- How can you integrate your Microsoft Excel data with Microsoft Access and vice versa? Be sure to try this and share your experience.
How comfortable were you with integrating your Microsoft Excel data with Microsoft Access and vice versa? Suggest ways in which your comfort level with integrating Microsoft Excel and Microsoft Access can be enhanced.
- How can the functionality of Microsoft Excel and Microsoft Access be improved? How would your proposed changes help Microsoft Excel and Microsoft Access users? Give reasons to support your answer.
Employees Database (Part 1 of 2)
Microsoft Access is a powerful database application program, which can be used to store many types of informationâ€”from a simple list of cake recipes to major inventories with many thousands of products. After information is stored in a Microsoft Access database, it is easy to find, analyze, and print.
Being interested to learn more about databases, you determine that the most important database objects among tables, queries, forms, and reports are actually tables. Tables, which make up the heart of a database, holding data and records, are used to produce and use other database objects such as queries, forms, and reports. This means that without tables, these database objects would be useless.
To demonstrate the importance of tables, you have decided to create a simple Microsoft Access database with one table that supports the use of a simple form, query, and report.
You use the following steps to do this:
- Create a new, blank Microsoft Access file and name the file EU_ITS2021_W4_Project_LastName_FirstInitial. Save and store the database file at a location on your computer or on an external flash drive for easy retrieval.
- After your Microsoft Access database file is created and securely saved, create a table with the following fields:
- Employee ID: Use the AutoNumber data type with New Values set to Random and set this field as the primary key.
- Prefix: Use the Lookup Wizard to specify a list of values including Mr., Ms., Mrs., and Dr. for this field and ensure that you limit the input to this list only.
- First Name: Use the Text data type.
- Middle Name: Use the Text data type.
- Last Name: Use the Text data type.
- Address: Use the Text data type.
- City: Use the Text data type.
- State: Use the Lookup Wizard to specify a list of values including at least five state abbreviations for this field and ensure that you do not limit the input to this list.
- Zip: Use the Text data type with zip code input mask.
- Home Number: Use the Text data type with telephone number input mask.
- Cell Number: Use the Text data type with telephone number input mask.
- E-mail Address: Use the Text data type.
- Hire Date: Use the Date/Time data type with short date input mask.
- After all the fields are created, name or rename the table as Employees and close the table but not the database.
- Create a form based on the Employees table by using the Form Wizard. Include all the fields in the form except the Employee ID field.
- Select Columnar when prompted to choose the layout of the form. In the last step, select Modify the form’s design and set the form title to Employee Data Entry Form and center-align this title.
- On the left navigation panel, where objects can be accessed, right-click the form and rename it to Form: Employees.
- Now, open the form and enter at least ten fictional employee records.
- After all the records are entered, close the form and then double-check the Employees table to ensure that the ten records are updated.
- Now, use the Simple Query Wizard to create a query based on the Employees table. Include the Prefix, First Name, Middle Name, Last Name, and Hire Date fields. Run the query to ensure that it works and then close the query.
- On the left navigation panel, where objects can be accessed, right-click the query to rename it to Query: Employee Hire Date.
- Now, use the Report Wizard to create a simple report based on the Query: Employee Hire Date and include all the fields. Sort the report in the Descending order of Hire Date. Select Tabular from the Layout group box and Landscape from the Orientation group box.
- In the last step of the Report Wizard, select Modify the report’s design and change the title to Employee Hire Date. Run the report to see how it looks, and explore the design area of the report to fine-tune field sizes for properly balancing the report output.
- On the left navigation panel, where objects can be accessed, right-click the report to rename it to Report: Employee Hire Date.
- Before closing the database, go through and review your table, form, query, and report to ensure that they all work and then close the database.
Note: Do not discard this database as this file will be used for Week 5 Project.