Microsoft Access

 

 

 

What you will learn in Access Chapter 3:

  • How to create reports using information from databases.

  • Modifying forms as well as databases and reflecting their changes within queries and reports.

  • Creating queries and action queries (Make-Table, Delete, Update, and Crosstab Queries).

  • The basic and rudimentary ways of modifying certain aspects of each portion of the reports, databases, and queries.

 

 

Table of Contents

 

Access Chapter 3 Problem 1

 

Access Chapter 3 Problem 2

 

Access Chapter 3 Problem 3

 

Access Chapter 3 Problem 4

 

Access Chapter 3 Problem 5

 

Access Chapter 3 Problem 6

 

Access Chapter 3 Problem 7

 

Access Chapter 3 Problem 8

 

Access Chapter 3 Problem 9

 

Access Chapter 3 Problem 10

 

Access Chapter 3 Problem 11

 

Access Chapter 3 Problem 12

 

Access Chapter 3 Problem 13

 

Access Chapter 3 Problem 14

 

Access Chapter 3 Problem 15

 

The following five projects, 16-20, are the most challenging and should be done in groups:

 

Access Chapter 3 Problem 16

 

Access Chapter 3 Problem 17

 

Access Chapter 3 Problem 18

 

Access Chapter 3 Problem 19

 

Access Chapter 3 Problem 20

 

 

 

[Projects]

 

Materials Needed for Problems 1-10:

  • The existing file located in Access, 'Friends'

 

 

Access Chapter 3 Problem 1

Objective:  To start report wizard

 

Using the Friends Report, complete the following:

 

a)  Open the report wizard and open the existing file, Friends.

b)  Preview the report.

c)  Save the report.

 

Back to Top

 

 

Access Chapter 3 Problem 2

Objective: To become familiar using the report wizard.

 

Using the Friends Report, complete the following:

 

a)  Alter the report 'Friends' so that the title is font 20 and that the column headings are all italicized and bolded in size 12pt.

b)  Change the layout so that it is portrait oriented.

c)  Sort the records so that First Name appears before social security number.

d)  Sort the records so that Age appears as the first column AND in descending order.

e)  Save the report as Practice 2.

 

 Back to Top

 

 

Access Chapter 3 Problem 3

Objective:  To be able to use Access to create a new report.

 

Using the Friend's Database, complete the following:

 

a)  Create a new report.

b)  Name your report My Friends, Practice 3.

 

Back to Top

 

 

Access Chapter 3 Problem 4

Objective:  To modify existing controls and add a control.

 

Using the Friends Report, complete the following:

 

a)  Modify the label First Name by changing the Font Size to 18pt and its Font Name to Arial.

b)  Change the color of the detail section under Last Name to Red.

c)  Insert Prepared by (Your Name) in the page footer.

d)  Save your document as Practice 4.

 

Back to Top

 

 

Access Chapter 3 Problem 5

Objective:  To be able to create a select query.

 

Using the Friends Query, complete the following:

 

a)  Open the Friends Database.

b)  Add in the Friends Table

c)  Create the query for Friends and Save as Practice 5.

 

 Back to Top

 

 

Access Chapter 3 Problem 6

 Objective:  To be able to specify criteria for the query and run the query.

 

Using the Friends Query, complete the following:

 

a)  In the Criteria row for birthday, type in "annual babies."

b)  Sort your query in ascending order according to social security number.

c)  Change the query to reflect alphabetical order of Last Name.

d)  Save your work as Practice 6.

 

Back to Top

 

 

Access Chapter 3 Problem 7

 Objective:  To be able to modify the query, and from it, create a report. 

 

Using the Friends Query, complete the following:

 

a)  Open the Friends document.

b)  Show only 20 year olds (Save this first part as Practice 7 pt 1)

c)  Show only females that are 20 years old and save as Practice 7, part 2.

d)  Create a report using the modified query.

e)  View the report and make sure the fields fit into the headers accordingly based on size.

f)   Save the last part as Practice 7 pt 3.

 

Back to Top

 

 

Access Chapter 3 Problem 8

 Objective:  To be able to use queries by specifying selection criteria and create a crosstab query.

 

Using the Friends Query, complete the following:

 

a)  Open the Friends document.

b)  Show men who are younger than age 20 and save as Practice 8 pt 1.

c)  Create a crosstab query by computing the sum of all the ages in the Gender group of Friends.

d)  View your query and save as Practice 8 pt 2.

 

Back to Top

 

 

Access Chapter 3 Problem 9

 Objective:  To be able to modify queries using calculated controls.

 

a)  Open the Friends document.

b)  Create the group footer.

c)  Determine the minimum age value.

d)  Find the maximum age value.

e)  Save as Practice 9 and exit Access.

 

Back to Top

 

 

Access Chapter 3 Problem 10

 Objective:  To create a report based on grouping a query.

 

a)  Open the Friends database.

b)  Group the Friends report according to Male and Female headings and save as Practice 10.

c)  Sort in ascending order according to Birthday year.

d)  Save as Practice 10 pt 2.

 

Back to Top

 

 

Access Chapter 3 Problem 11

Objective:  To effectively use Access to create a database table and a report.

 

For the following problems 11-15, you will need the information below:

 

LastName FirstName Address City State Phone Number Gender Thank you note sent
Gainsbor Josephine 1754 Orange Rd. Greenbelt MD (240) 777-3421 F No
Lafayette Crystal 4264 Springbrook Dr. Potomac MD (301) 498-7654 F No
Jacobs Brandon 1562 Rolling Hills Ct. Landover VA (410) 840-2245 M No
Hampton Anne 1501 K Street DuPont Circle DC (202) 764-5213 F No
Summers Billy 4284 Layhill Rd. Silver Spring MD (301) 976-5485 M No
Urlich Christopher 1244 Wisconsin Ave. Bethesda MD (240) 487-6959 M No
Hasegawa Dayna 9431 Lawn Way Anne Arundel VA (410) 457-5684 F No
Smith Emily 55461 Park Ave. Manassas VA (410) 243-5845 F No
Brenners David 8756 Rockville Pike Rockville MD (301) 487-5622 M No
Cunningham Melissa 1842 Florida Avenue Van Ness DC (202) 114-5425 F No
Adams Timmy 27995 Titonka Way Van Ness DC (202) 774-5111 M No
O' Toole Mike 4652 Metro Center Ct. Chinatown DC (202) 184-5232 M No
Gomez Cassandra 6533 Oliver Drive Tysons Corner VA (240) 212-5786 F No
Delaney Helen 13346 Gray St. Van Ness DC (202) 159-8462 F No
Jackson Andrew 14856 Hudson St. Silver Spring MD (301) 330-4582 M No
Clinton Thomas 45776 Nestle Ave. Anne Arundel VA (240) 453-2584 M No
Hartford Alexa 1784 Hungerford Dr. Bethesda MD (301) 846-2253 F No
Epstein Patrica 7740 Terrance Parkway Landover VA (410) 885-2654 F No
Jaudhary Katie 19510 Parish Ave. Greenbelt MD (240) 515-3352 F No
Mills Jason 1414 Scott Dr. Chinatown DC (202) 995-6481 M No

 

You have just had your big High School Graduation party and have received an abundance of gifts of all different sorts!  You must create an organized way to keep track of all the attendees and their gifts so you know where to send thank you notes!

 

a)  Create a table which lists all the above information in the order which it is given. 

b)  Create a report to formalize your list.

c)  Save the database and reports as Graduation Celebration.

Back to Top

 

Access Chapter 3 Problem 12

Objective:  To use Access to create forms and the regular format to a query in entirety.

a)  Create a list of forms given the information from Problem 11. 

b)  Create a query and show all the fields.

c)  Save the forms as Graduation Forms and the query as Graduation Query.

Back to Top

 

Access Chapter 3 Problem 13

Objective:  To create the make-table and delete table query.

a)  Create and run the make-table query using the information given in Problem 11 and save it as Gift Amount Make-Table Query.

b)  Create the delete table query and run it, saving it as Purge Completed Thank you notes.

 

 

Back to Top

 

 

Access Chapter 3 Problem 14

 Objective:  To use Access to create the append-table and update queries.

 

a)  Create the append-table query.

b)  Create the update query.

 

Back to Top

 

 

Access Chapter 3 Problem 15

Objective:  To use Access to create the crosstab query--the hardest of all the queries.

 

a)  Create a crosstab query with row heading as State, column heading as Gender, and the calculated field as Gift Amount and Avg.

Back to Top

 

The following problems 16-20 are difficult and it is recommended that they be done in groups of 3-4 people.  You will need the information given in Problem 16 to complete all of the exercises from here on.

Access Chapter 3 Problem 16

Objective:  To effectively show that the users are able to create a difficult table and report.

 

Create your own fictional database of up to 35 students, and then a report using the following fields as guides.  Call them both Business School for the Titles.

 

    In it, include the fields that follow:

                    1.  Major

                    2.  Last Name

                    3.  First Name

                    4.  Gender

                    5.  Credit Level

                    6.  GPA

                    7.  Student Id #

 

 

a)  First, save each of the tables.  These are the original files.  The database and report both as Major.

b)  Alter your database so that student id # appears first.

c)  Modify your report so that Major appears in the last column of the tabular layout.

d)  Save the altered database as Business School

 

Back to Top

 

 

Access Chapter 3 Problem 17

Objective:  To effectively show that the users are able to create both difficult forms and queries.

 

Using the information given in Problem 16:

 

a)  Create a list of forms for all the students in the business school.  Save the forms as Business School.

b)  Create a query that shows all the fields listed from above.  Save as Business School Query.

 

Back to Top

 

 

Access Chapter 3 Problem 18

Objective:  Create the make-table and delete table query.

 

a)  Create and run the make-table query for students with credit level over 60 using the information given in Problem 16.

b)  Create and run the make-table query for students with credit level under 30 using the information given in Problem 16.

c)  Create the delete table query and run it for those students who are graduating (=or over 90 credit level).

d)  Create the delete table query and run it for those students who are freshmen (under 30 credits).

 

Back to Top

 

 

Access Chapter 3 Problem 19

Objective:  To use Access to effectively and successfully create the append-table and update queries.

 

a)  Create the append-table query.

b)  Create the update query for >=55 credits.

 

Back to Top

 

 

Access Chapter 3 Problem 20

Objective:  To use Access to effectively and successfully put together a crosstab query and a report reflecting the crosstab query information.

 

a)  Create a crosstab query with row heading as Major, column heading as Gender, and the calculated field as GPA and St.dev.

b)  Create a report for accounting majors with GPAs of >=3.

c)  Sort in ascending order of major and create a report based on your table.

 

Back to Top

 

 Back to Menu