CSCI A110: Introduction to Computers & Computing Page 1 of 5 Lab Training – tm2 General: Follow the instructions here to complete the training tutorial. When you are finished, upload your completed file(s) to Canvas. What You Will Need to Complete This Training To complete this training successfully, you will be provided with, or will need to obtain the following: q The use of ms-access, more than likely via IUanyware q The files required for the lab: Indiana_arboretum_2.accdb Learning Outcomes: This training assignment is intended to fulfill the following outcomes of the course: q I.1.2 You will develop your ability to apply principles and generalizations already learned to new problems and situations by creating database queries for the evaluation of data. q I.1.3 You will analyze database information and summarize, by (1) examining the problem domain in light of specific requirements, and (2) selecting appropriate techniques to achieve the goals. q III.1.0 You will develop skill in using technology central to this subject by creating queries to extract meaningful information, developing reports to display information, and designing reports for various data and record views. q III.1.4 You will understand database concepts by and recognize the table and query connectivity by adding a record and running the query; that reports can be built from various database objects; and that forms have a variety of uses. Assignment Description: This assignment involves the following tasks: CSCI A110: Introduction to Computers & Computing Page 2 of 5 Lab Training – tm2 q Use an existing database to add queries from tables, to query and sort database tables using various criteria; work with multitable queries. q Use calculated fields within a query; and to format query results; use built-in functions. q Create a query with grouping and conditions. q Work with forms. q Build a report with sorts and groups. The Morris Arboretum expands on its new database. Congratulations. Your job is to show plants gifted to donors, and to identify any trends in donations. Instructions: Use an existing database to add queries from tables, to query and sort database tables using various criteria; work with multitable queries (5 points) q Download and open the database file named Indiana_arboretum_2.accdb q Use the query wizard to begin a simple query, using the Donors table. Add fields FirstName, LastName, Address, Phone, ENewsletter, and Volunteer. Name the query Volunteers without Newsletter and then finish the wizard. Run the query, and then switch to Design view. Sort LastName in ascending order, and set Criteria for ENewsletter to “no”, and Volunteer to “yes”. Save and close the query. q To the Donations table, add the following record, and then close the table: DonorID PlantID DonationDate DonationAmount 9 10 12/15/2020 500 q Modify the query Volunteers without Newsletters by adding the Donations table to the top panel in Design view. Add the field DonationAmount to the query, and run it. You should see that of all the volunteers not receiving the newsletter, only one has made a donation (twice) – a nice donation. Return to Design view, and show the Total row. Change the Group By in the Total row of the DonationAmount field to Sum and run the query again. Switch to Design view and add DonationAmount to the query a second time, and change CSCI A110: Introduction to Computers & Computing Page 3 of 5 Lab Training – tm2 the Group By in the Total row to Count, and then run the query. Save a copy of the query as Donor Volunteers without Newsletter, and then close the query. q Begin to create a query in Design view (not the wizard). Add The Donations table, the Donors table, and the Plants table to the query. From the Donors table, add the Firstname, Lastname, and Phone fields; from the Donations table, add DonationDate and DonationAmount fields; and from the Plants table add the PlantName and PlantsOnHand fields. To DonationDate field, add a criteria that will return only donations made on or after 1/1/2020. Run the query. In Datasheet view, Display the Totals row, and display a Sum for the DonationAmount , and the Count of the Donations (can apply to DonationDate column). Save the query as Donations 2020, and then close the query. Use calculated fields within a query; and to format query results; use built-in functions (5 points) q Create a query in Design view (not the wizard). Add tables Donors and Donations to the query. Add fields from Donors table: Firstname, Lastname, Phone. Add fields from Donations table: DonationDate. Run the query. o You should see all donors who made donations, but if you open and view the Donors table, there are many more donors. Perhaps these are donors who did not donate in 2020, and we want to encourage them to donate again? o Return to the query, and edit the join properties (ie., double-click the join – the line that connects the two tables, or ctrl+click on the line (option+click for MacBook users), and select Join Properties). In the first pass, we were only seeing option 1 (all donors who also had a DonorID, and therefore a donation, in the Donations table). To see all the donors who do NOT have a DonorID in the Donations table, and therefore have not made a donation, select option 2 in the Join Properties and then click OK. Run the query. o Notice you can see all the donors, including those who have not donated. We want to show only those who have not donated, and ignore those who have a DonationDate. Return to Design view, and add a Criteria to DonationDate: Is Null (hint: this is a Boolean, or logical function, which will return all records that are null, meaning they have no values in them). Run the query again to see the changes. o Now, we do not need to see the DonationDate column any more, so return to Design view, and uncheck the box in the Show row. Run the query again. Save it as Inactive Donors. CSCI A110: Introduction to Computers & Computing Page 4 of 5 Lab Training – tm2 q Modify the Inactive Donor query. Since donations can come in the form of money, but also in the form of time, add the field Volunteer to the query, and include a criteria to return records of those who do not volunteer. Run, save and exit the query. q Modify the query Donations 2020 by opening it in Design view, and inserting a column after DonationAmount. Add a calculated field named Hours that takes the DonationAmount and divides it by 50. (The arboretum has very limited part-time staff, who can be paid $15 per hour to do work beyond what a typical volunteer would do). Knowing how many hours can be paid out will help the arboretum with their planning. Run the query. Return to Design view, and adjust the Hours field properties, formatting the field as Fixed, and changing the caption to Work Hours. Run the query again, and add a Sum under Work Hours to the Totals row. Save and close the query. Create a query with grouping and conditions (5 points) q Let’s see all donors who gave both money and time. Create a new query, add all tables, and from Donors table add: Lastname, Firstname, Volunteer; from Donations table add: DonationDate, DonationAmount; and from Plants table add: PlantName. Show Totals, and for Volunteer change Group By to Where (make sure the checkbox in Show row is uncheckced), and set Criteria to “yes”. Save the query as Volunteers who donate, run the query, and exit the query. Work with forms (5 points) q Using the table Plants, create a split form. Drag the field right edge of the PlantName field to the left so it lines up with the right edge of the Donation table below it. Save the form as Plants, and exit the form. q Using query Donations 2020, create a split form. Save the form as Donations 2020, and exit the form. Build a report with sorts and groups (5 points) q Using query Inactive Donor, create a report. Save the report as Inactive Donor, and exit the report. q Using the table Plants, create a report using the Report Wizard. Add fields PlantsOnHand and PlantName. Do not add a grouping, but sort on PlantsOnHand in ascending order. Finish the wizard. In layout view, drag the left edge of the PlantName field to the right, so that there is space between the number of PlantsOnHand and the PlanName fields. Save the report as Plants on Hand and exit the report. Before You Go: CSCI A110: Introduction to Computers & Computing Page 5 of 5 Lab Training – tm2 q Ensure your database is saved to your local desktop. Close all database objects. Close the database and then exit Access, and exit IUanyware. q Upload the final version of this file to Canvas.