Microsoft Access

 

 

 

 

Table of Contents Answers for Problems

 

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]

 

Access Chapter 3 Problem 1

 

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

   

    To Solve:

       1.  Begin Microsoft Access.

       2.  Choose open an existing database in the prompted window.

       3.  The database Friends is a choice: choose it to take you to this new window as shown below, and under objects, click reports and highlight Friends in Design view.

 

 

b)  Preview the report.

    

    To Solve:

       1.  This will always be a common point to come back to when using Microsoft Access' databases.

       2.  In the left hand side tool bar, click the white piece of paper with the magnifying glass in it.

       3.  Choose layout preview to look at the report in proper format instead of under design view.  This step is shown below:

 

c)  Save the report.

 

   To Solve:

       1.  In Microsoft Access, your databases are usually automatically saved for you.  However, to be safe, sometimes the windows will not prompt you to save your existing information and it is possible that you may loose it.

       2.  To be safe, always save your work, clicking on the menu toolbar under File and Save. 

       3.  You should become accustomed to saving your work--it is essential not to loose any of your databases.

       4.  More will come later on saving a database under a different name so that it can be modified.

       5.  Steps are shown below for saving the report.

 

Back to Top

 

 

Access Chapter 3 Problem 2

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.

 

    To Solve:

       1.  Highlight all the outer boxes of the page headers and use the pull-down menu for font size and select size 12pt.

       2.  Do the same for the title, Friends, except make the title font size 20pt.

       3.  Your process will look something like this:

 

 

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

 

    To Solve:

      1.  Go to File and click Page Setup.

      2.  Choose the Page tab and from there, select Portrait Orientation, as shown below:

 

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

 

    To Solve:

       1.  Simply highlight click and drag the field Last Name with the cursor until the outer edges turn black, then drag it to the Social Security number spot.

      2.  Do the same for the Social Security number, and drag it to replace where the First Name field was.

 

 

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

 

    To Solve:

       1.  Simply click and drag the field Age with the pointer cursor and resize the columns so that age appears first and all other fields appear last.

       2.  To change the sort order, click on the view menu for the pull down bar, click on Sorting and Grouping.

       3.  Choose the drop down arrow in the Field/Expression box and choose Age and in the Sort Order box, choose Descending.

 

 

e)  Save the report as Practice 2.

 

    To Solve:

       1.  Choose Save As and a Copy To box will appear, simply rename the report as Practice 2 and close. 

 

    Your final report should look like this:

Back to Top

 

 

Access Chapter 3 Problem 3

 

a)  Create a new report 

 

    To Solve: 

       1.  Open Microsoft Excel

       2.  When the pop up box surfaces, click the open existing database: Friends database and the Open command.

       3.  In the new window, select under Objects the Reports command and in the new dialog box, click the second choice down: Report Wizard.

       4.  In the selection at the bottom, click the drop-down arrow.  In this, select what the report will be based on: Friends.

       5.  The report wizard box will pop up, in this, choose all the available fields that will be needed using the > key to choose all the selected fields.

       6.  Click Next.

       7.  Sort your column heading records on the next screen by choosing Age. And in the following screen, sort your detail in Gender a-z.

       8.  In the new box, make sure that stepped is selected within the layout menu.  Choose portrait orientation and make sure that the Adjust field width so all fields fit on a page is checked off.

       9.  You can choose Bold as your report style.

      10. As the title of the report, you can name it My Friends, Practice 3 and then click the Finish button to see your work.

     11. Adjust all the headings and detail so that they fit neatly on the page.

 

Your Finished Report will look like this:

 

 

Back to Top

 

 

Access Chapter 3 Problem 4

 

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

 

    To Solve:

       1.  Click the border of the control First Name and wait for the pointer to appear.  Then click the right mouse button where a label box will appear and choose the tab All and at the near bottom, click in the white space next to where it says Font Size to give you a drop down arrow where you can change the font size.  Then above the Font size space, click in the white space next to Font name.  The drop down arrow will allow you to scroll until you find Arial.

 

 

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

    To Solve:

 

       1.  Click the mouse to where the label under the detail section is Last Name and the go to the top menu bar where there is a the symbol A with a color line under it.  Click it and choose the color red.

 

 

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

 

    To Solve:

 

       1.  In the toolbox bar, select the Label tool and click and drag the label to the report footer and release it when you find a suitable place for it in the page footer.

       2.  In the label box, type in Prepared by (your name) and press enter.

 

d)  Save your document as Practice 4.  Your Report should look like this:

 

 

    To Solve:

 

       1.  Pull down the file menu and click save.  You can also click the small icon of a disk in the menu bar at the top of the screen.

 

 

Back to Top

 

 

Access Chapter 3 Problem 5

 

a)  Open the Friends Database.

 

    To Solve:

       1.  Start Microsoft Access, click open existing file and open the Friends Database.  

       2.  Now, within the database, choose under options Queries and the new command button at the toolbar of the box.

       3.  In the new query pop up box, choose Design View and OK.

 

b)  Add in the Friends Table

 

    To Solve:

       1.  As soon as the query opens, a Show Table pop up box will appear, and in it, choose the tab Tables.

       2.  Click the Add button to the right of the box and a field list will appear in the query.

       3.  You can maximize the query design window when all the fields are inputted so that all the fields can be seen.

 

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

 

To Solve:

       1.  Click on and drag each field from the Field list to the field row one at a time and put them in the order they appear listed, into the fields.

       2.  Now save your work as Practice 5.

 

 Your Finished Query should look like this:

Back to Top

 

 

Access Chapter 3 Problem 6

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

 

    To Solve:

       1.  There is a Criteria row five field rows down, and in this criteria row, under birthday, type in "Annual Babies."

    

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

 

    To Solve:

       1.  In the sort row under the social security number field, choose ascending in the drop down arrow.

 

 

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

 

    To Solve:

       1.  In the sort row under the Last Name field, choose ascending in the drop down arrow.

 

d)  Save your work as Practice 6.

 

    To Solve:       

       1.  Save your work by clicking the save button or the drop down file menu as Practice 6.

 

Back to Top

 

 

Access Chapter 3 Problem 7

a)  Open the Friends document.

 

    To Solve:

       1.  Start Microsoft Access, click open existing file and open the Friends Database.  

       2.  Now, within the database, choose under options Queries and the new command button at the toolbar of the box.

       3.  In the new query pop up box, choose Design View and OK.

 

 

b)  Show only 20 year olds.

 

    To Solve:

       1.  Under the criteria row in age, type in =20 and make sure the show field box is selected.

 

Save this first part as Practice 7 pt 1, it should look like this:

 

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

 

    To Solve:

       1.  In the criteria row under gender, type in "Female."  Keep the =20 in the criteria under age.

       2.  Save the revised query by clicking the save button.  To run the query, click the Run button to see your query.

       3.  Only females who are 20 years old will now appear in your query.

This Part will look like this:

 

d)  Create a report using the modified query.

 

    To Solve:

       1.  Go to the main box in Access and under Objects click Reports. 

       2.  Choose to Create report by using wizard.

       3.  The next box will allow you to input which Tables/Query you want to choose to base your information on in the drop down arrow box, and choose Practice 1 to base your report on.

       4.  In available fields, make sure that all the fields are chosen under selected fields and click Next.

       5.  No grouping levels are needed, nor should you sort your report in any specific manner.  Click Next to exit these screens.

       6.  Under Tabular layout, select Portrait orientation and check where it says Adjust field width so all fields fit on a page.

       7.  Choose any of the styles listed.

 

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

 

    To Solve:  

      1.  Click the maximize button to see the full report and play around with the zooms in the tool bar to get different views of the page.

       2.  Make sure that all the headers fit onto the page in proper format and do not overlap each other or cut off letters.

 

 

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

 

    To Solve:

 

       1.  You may print here and submit to an instructor if you'd like, otherwise, save by clicking the disk icon in the toolbar and exit.

Your Finished Report reflecting Part 1, will look like this:

 

Back to Top

 

 

Access Chapter 3 Problem 8

 

a)  Open the Friends document.

 

    To Solve:

      1.  Start Microsoft Access, click open existing file and open the Friends Database.  

       2.  Now, within the database, choose under options Queries and the new command button at the toolbar of the box.

       3.  In the new query pop up box, choose Design View and OK.

 

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

 

    To Solve:

       1.  Under the criteria row in age, type in <20 and make sure the show field box is selected.

      2.  In the criteria row under gender, type in "Male."  Keep the <20 in the criteria under age.

       2.  Save the revised query by clicking the save button.  To run the query, click the Run button to see your query.

       3.  Only males under 20 years of age will now appear in your query.

 

Your table will look like this when you've completed the query part:

 

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

 

    To Solve:

       1.  Start a new query in Create query in Design view and begin the wizard.

       2.  In the Queries option button, choose create query by using wizard. 

       3.  Place Gender in the field list and put First Name as the column header field.

       4.  Click on Age to be the field that is calculated and under Function, choose Avg.

       5.  You Do want to show row sums, so make sure that the box on the left is chosen.

       6.  Save as Practice 8 pt 2 and click Finish.  Your crosstab query is shown.

       7.  You can change the decimal points if needed by going to View then Properties and clicking on Format row.  From there, set the field as Fixed and the decimal places to two or three (whichever you see fit).

 

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

 

    To Solve:

       1.  To run the query, click Run and now, average age is displayed to a modest decimal place.

       2.  Save your query by clicking on the small disk icon in the toolbar.  Close the query and exit Access.

A portion of your Practice problem should look like this:

 

Back to Top

 

 

Access Chapter 3 Problem 9

 

a)  Open the Friends document.

 

    To Solve:

 

       1.  Start Microsoft Access, click open existing file and open the Friends Database.  

        2.  Now, within the database, choose under options Queries and the new command button at the toolbar of the box.

        3.  In the new query pop up box, choose Design View and OK.

 

b)  Create the group footer.

 

    To Solve:

 

       1.  In the mini toolbox bar, choose the ab button and click and drag the footer where you want it to go.

       2.  Just click inside the text box so that the unbound control disappears.

       3.  You now have a group footer.

 

c)  Determine the minimum age value.

 

    To Solve:

 

       1.  Inside the group footer, enter in this:  =Min(Age) to figure out the minimum age for your group of friends.

       2.  In the attached unbound control on the left of the calculated control, type: Minimum Age for Friends as its label.

       3.  Click on the Format box and select fixed, and in the decimal places box, choose 2 spaces.

 

d)  Find the maximum age value.

 

    To Solve:

 

       1.  Inside the group footer, enter in this equation:  =Max(Age) to figure out the maximum age for your group of friends.

       2.  In the attached unbound control on the left of the calculated control, type: Maximum Age for Friends as its label.

       3.  Click on the Format box and select fixed, and in the decimal places box, choose 2 spaces.

 

e)  Save as Practice 9 and exit Access.

 

    To Solve:

 

       1.  Pull down the file menu and click save.  You can also click the small icon of a disk in the menu bar at the top of the screen.

 

Back to Top

 

 

Access Chapter 3 Problem 10

 

a)  Open the Friends database.

 

    To Solve:

 

       1.  Start Microsoft Access, click open existing file and open the Friends Database.  

        

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

 

    To Solve:

 

       1.  Begin by selecting under objects, Report and create report by using wizard.

       2.  Select your table as Friends for where your information is coming from and choose all the available fields to select.

       3.  For the grouping levels, choose Gender in ascending order.

       4.  Choose a tabular layout and portrait orientation.

       5.  Choose Soft Grey as your style.

       6.  Adjust all the page headers and detail so that they fit accordingly onto the page.

Your Report should look like this:

 

c)  Sort in ascending order according to Birthday year.

 

    To Solve:

 

       1.  Highlight the Birthday detail and go to sorting and grouping and click ascending order.

       2.  Now preview and the order of the years will be in ascending order starting from 1980 on.

 

d)  Save as Practice 10 pt 2.

 

Back to Top

 

 

Access Chapter 3 Problem 11

 

 

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

 

    To Solve:

 

       After creating your database, it should look something like this:

 

b)  Create a report to formalize your list.

 

    To Solve:

 

       Your report will look like this:

 .

 

 

c)  Save the database and reports as Graduation Celebration.

 

    To Solve:

 

       1.  Click the save as option within the file drop down menu.

       2.  When the save as option appears, enter in Graduation Celebration.

       3.  Exit Access.

Back to Top

 

Access Chapter 3 Problem 12

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

 

    To Solve:

 

An Example of one of the forms is below:

 

 

b)  Create a query and show all the fields.

 

    To Solve:

 

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

 

    To Solve:

 

       1.  Click the save as option within the file drop down menu.

       2.  When the save as option appears, enter in Graduation Forms or Graduation Query, depending on which one you are saving.

       3.  Exit Access.

 

Back to Top

 

Access Chapter 3 Problem 13

a)  Create and run the make-table query using the information given in Problem 11.

 

    To Solve:

 

       1.  Your should alter your query for the first few steps to look like this:

 

Your next steps will consist of the following to complete the make-table query:

 

       2.  After clicking on Make-Table query as shown above, enter the name of the query as 'Gift Amount Make-Table Query.'  Make sure that the Graduation Celebration data is selected in the drop-down bar.

      3.  Click okay, and then under your tools, click RUN to run the query and YES when it asks if you want to paste new rows in.

      4.  Close the make-table query and save the query.  When finished, your make-table query should look like the following:

 

b)  Create the delete table query and run it.

 

    To Solve:

 

       1.  In the regular Queries menu, choose Graduation Celebration and right click to display a fold-out menu, and click Save as.

       2.  When the copy menu appears, rename a copy of your original file to: Purge Completed Thank you notes.

 

    It should look something like this after steps 1 and 2.

       3.  Open the new query from step 2 in design view.

       4.  On the toolbar, click the delete table query.

       5.  In the criteria field, enter in <150 as your completed thank you notes for those who have sent you money under $150.00.

       6.  Choose the Run button in the toolbar to run the delete table query.

 

    It should look something like this after steps 3-6:

 

       7.  When the pop up message appears, agree to it by clicking yes when it asks about deleting records.

       8.  Close the query menu and make sure to save your modified query.

       9.  Open the table for Purge Completed Thank you notes, and you'll notice that the thank you notes have been completed and are gone for the guests who gave LESS than $150.00 as they are no longer there!

Your query table should look as the following:

 

Back to Top

 

 

Access Chapter 3 Problem 14

 

a)  Create the append-table query.

 

    To Solve:

 

       1.  In the queries menu, choose to create query in Design View.

       2.  In the pop up box, select the Graduation Celebration table and Add and close that box.

 

If followed properly, it will look like this after steps 1 and 2:

 

       3.  Find the asterisk in the table and then click on it and drag it to the design grid.

       4.  On the toolbar under Query, click on append query and then use the > to choose Graduation Celebration and OK.

       5.  In order to run the append query, click Run under query in the toolbar. When pop up message appears choose Yes.

       6.  Save your query as Append Gift Celebration.

      

b)  Create the update query

 

    To Solve:

 

       1.  In the opening window, click Queries and open Graduation Celebration Query.

       2.  Copy the query by highlighting it and pressing Ctrl and then C.

       3.  Paste the query in the dialogue box.

       4.  Enter in Update State and open the query in Design view.

       5.  In the tool bar, select Update query and then in the criteria row for Gift Amount enter >=100.

       6.  Find the Update To row under the Update State, choose YES.

       7.  The result will change the value of the State field to "yes" for all gift amounts greater than or equal to $100.00.

       8.  On the toolbar, choose Run to run the new query, and when the warning box appears, choose yes.

       9.  Close the new query and save.

 

Back to Top

 

 

Access Chapter 3 Problem 15

 

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

 

    To Solve:

 

       1.  In the database window, choose Queries and New and choose the crosstab query.  Then, click OK.

 

It should look something like this:

 

                                           

 

 

 

       2.  Bubble in the Queries selection and at the top, choose the Graduation Celebration query.

 

It should look like this:

 

 

         3.  Choose next and in the next window, choose state as your row headings and next.

       4.   In the next box, it will ask for the field you want as the column heading.  Click gender as your selected field.

       5.  The next box will ask you for the field you want calculated, so choose Gift Amount.  

       6.  In the Functions box, choose Avg. and next.

 

After step 6, your progress will look like this:

 

 

       7.  The query is named already, just click finish.

       8.  The query that shows up lists the average money amount received from each gender of each state.

 

The query should look like this when finished:

 

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

 

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.

    Answer:

 

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

 

    Answer: b, c, and d:

 

Back to Top

 

 

Access Chapter 3 Problem 17

 

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

    Answer:

 

 

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

    Answer:

 

Back to Top

 

 

Access Chapter 3 Problem 18

 

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

 

    To Solve:

          1. The first step should look something like this:

       2.  After clicking on Make-Table query as shown above, enter the name of the query as 'Upcoming Juniors.'  Make sure that the Business School data is selected in the drop-down bar.

       3.  Click okay, and then under your tools, click RUN to run the query and YES when it asks if you want to paste new rows in.

       4.  Close the make-table query and save the query.

 

 

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

 

    To Solve:

 

       1.  Simply repeat the steps from above to complete this make-table query, only in the criteria field for credit level, input  <30 and save as Upcoming Sophomores.

 

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

    

    To Solve:

 

       1.  In the regular Queries menu, choose Business School and right click to display a fold-out menu, and click Save as.

       2.  When the copy menu appears, rename a copy of your original file to: Purge Seniors.

 

    It should look something like this after steps 1 and 2:

 

        3.  Open the new query from step 2 in design view.

        4.  On the toolbar, click the delete query.

        5.  In the criteria field for credit level, enter in >=90 as the seniors who are going to soon graduate from the Business School.

        6.  Choose the Run button in the toolbar to run the delete table query.

 

    It should look something like this after steps 3-6:

 

       7.  When the pop up message appears, agree to it by clicking yes when it asks about deleting records.

       8.  Close the query menu and make sure to save your modified query.

       9.  Open the table Purge Seniors, and you'll notice that the seniors (those with 90 credits or more) are no longer there!

 

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

 

    To Solve:

 

       1.  Simply look at the example above to complete this exercise and enter into the criteria field <30.  Call this query, Purge Freshmen.

 

Back to Top

 

 

Access Chapter 3 Problem 19

 

a)  Create the append-table query.

 

    To Solve:

 

       1.  In the queries menu, choose to create query in Design View.

       2.  In the pop up box, select the Business School table and Add and close that box.

 

If followed properly, it will look like this after steps 1 and 2:

 

 

       3.  Find the asterisk in the table and then click on it and drag it to the design grid.

       4.  On the toolbar under Query, click on append query and then use the > to choose Business School and OK.

       5.  In order to run the append query, click Run under query in the toolbar. When pop up message appears choose Yes.

       6.  Save your query as Append Business School.

 

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

 

    To Solve:

 

       1.  In the opening window, click Queries and open Business School Query.

       2.  Copy the query by highlighting it and pressing Ctrl and then C.

       3.  Paste the query in the dialogue box.

       4.  Enter in Update Credits and open the query in Design view.

       5.  In the tool bar, select Update query and then in the criteria row for Credits enter >=55.

       6.  Find the Update To row under the Update Credits, choose YES.

       7.  The result will change the value of the State field to "yes" for all credit levels greater than or equal to 55.

       8.  On the toolbar, choose Run to run the new query, and when the warning box appears, choose yes.

       9.  Close the new query and save.

 

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.

 

    To Solve: 

        1.  In the database window, choose Queries and New and choose the crosstab query.  Then, click OK.

 

 

       2.  Bubble in the Queries selection and at the top, choose the Business School query.

       3.  Choose next and in the next window, choose major as your row headings and next.

       4.   In the next box, it will ask for the field you want as the column heading.  Click gender as your selected field.

       5.  The next box will ask you for the field you want calculated, so choose GPA.  

       6.  In the Functions box, choose St.dev and next.

 

So far, your query will look something like this:

 

       7.  The query is named already, just click finish.

       8.  The query that shows up lists the Standard Deviation for each gender of each major.

       9.  Save as Business School Query_Crosstab.

 

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

 

    Your report will look like something like this depending on your personal preference of layout:

 

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

 

    To Solve:

 

This is the table sorted in ascending order by major:

 

 

This is how the report should look when finished:

 

Back to Top

 Back to Menu