Databases exercises in Microsoft Access 2013 Karolina Muszyska

Databases – exercises in Microsoft Access 2013 Karolina Muszyńska http: //www. gcflearnfree. org/access 2013

Microsoft Access 2013 tutorial � http: //www. gcflearnfree. org/access 2013 � Creating reports � Database design: ◦ Modifying tables ◦ Creating and formatting forms ◦ Designing your own database from template � Contents of the project 2

Creating reports � Choosing a table or a query for the report � Adjusting and formatting the report in the Layout view ◦ ◦ ◦ Resizing Deleting unwanted fields Sorting and filtering Printing or exporting reports Using the Report Wizard Formatting reports � Exercises: ◦ Use the „From Garner or not emailed customers” query and create a report ◦ Resize the fields so that all data is visible ◦ Sort it by City field ascending ◦ Export the report as a pdf 3

Creating reports � Exercises: ◦ ◦ ◦ Prepare a sample report using the Report Wizard Open the Cookies Sold report Add the date and time to the header Add page numbers to the footer Choose a new theme and theme fonts Add a logo 4

Modifying tables � Adding and rearranging fields � Advanced field options ◦ Changing data type for existing fields ◦ Field character limits ◦ Validation rules and corresponding messages � Additional table options: ◦ Calculated fields and total rows � Exercises: ◦ Open the Products Table, and change the data type for the description field to Long Text ◦ To the same table add a new field that uses the Short Text data type and name it Average Shelf Life ◦ Add the following field validation rule to the Average Shelf Life field: „day” or „week” or „two weeks” or „month”. ◦ Set a field validation message for the same field (i. e. „Enter a valid Average Shelf Life Type: Must be „day”, „week” „two weeks” or „month”) 5

Creating forms � Creating forms and subforms � Adding additional fields from different tables � Adding design controls � Customizing form settings � Exercises: ◦ Create a form from the Customers table. Delete the subform. ◦ Create a combo box. Add the following choices to the combo box: Raleigh, Durham, Hillsborough, Cary, Chapel Hill, Garner, Charlotte ◦ Store the value in the City field, and label the combo box NC City List. Click Finish when you are done with the Combo Box Wizard. ◦ Switch to Form view, and click on the drop-down arrow in the combo box you created. You should see a list of cities. 6

Formatting forms � Using command buttons � Modifying form layout � Exercises: ◦ Open the Order Items form ◦ Add a command button that creates a new record ◦ Format the newly added command button with the colors and style of your choice ◦ Add a logo 7

Creating your own database � Creating a database from a template � Creating a database from scratch � Microsoft tutorial http: //www. quackit. com/microsoft_access/microso ft_access_2013/tutorial/create_a_database. cfm ◦ ◦ ◦ Create Create tables relationships forms queries reports 8

Contents of the project A Microsoft Access file containing: � Table designs (attributes, attribute types, key attributes and initial data) � Design of relationships among tables � Form designs for inserting data (at least 2) � Report designs (at least 2) � Query designs (at least 3) Deadline – 16 th January 2018 9