Chapter 2 Scored Assignment

Access Database 2 (Chamber Members)

Objective:  Add additional records to database file (copy and append), perform multiple queries on records, create a basic form and report.

 (Links to an external site.)

Adding Excel Data to an Existing Access Table
The chamber directory is much larger than shown in the first assignment.  The Excel file has over 300 members. We will add more members to our database file, by copying and appending records to the table in the Chamber Member database.  This will save time.
Note:  The Excel file had more names in it than actual chamber listing.  Some information was deleted to reduce size.  If you want a detailed directory list, view the Pendleton Chamber of Commerce Links to an external site. Directory Listing, on their website.

Proof Data/Copy/Append Records.

Making A Query (Member City--Pendleton)

• A query will produce specific results from the data in the table. You can create one by using a wizard, or by design.
• We will start with a wizard. Click your Create tab. Find the Queries group, click Query Wizard button.
• Choose Simple Query Wizard; make sure Members Table is the table it will use to make the query.
• Move the Business/Organization Name, City, and State fields to selected fields box.
• Choose Next. Change the name given to Member City Pendleton Query. Choose Finish. The query with those three fields opens.
• Right click and switch to design view of your query.
• On the city field in the table grid below, type Pendleton on the Criteria line.
• Choose the Run button (red exclamation point in ribbon) from the Results group at top of your screen. It should now only list Pendleton locations for members.
• How many results show Pendleton businesses?
• Widen the fields to so all data.
• Close and save the query. It now appears in the navigation pane at the left.
• The table and query are Access objects. We have two objects now in an Access database file called Chapter 2_SC_xx. 
  If you have mistakes that are objects, make sure they are closed, then right click and delete them.

Making A Query (Member Industry)

• One query has been created, now create a second query.
• Make sure all objects are closed.
• Start with a query wizard. Click your Create tab. Find the Queries group, click Query Wizard button.
• Choose Simple Query Wizard; make sure the Members Table is the table it will use to make the query.
• Move the Business/Organization Name and Industry fields to selected fields box. Choose Next.
• Change the object name given to By Industry Query. Choose Finish.
• The query with those three fields opens. 
• Sort the Industry field in ascending order (a-z).
• Widen the fields to show all data.
• How many businesses have a blank, empty industry?
• Save and Close the query. 

The table and queries are Access objects. We have three objects now in an Access database file called Chapter 2SC_xx.

Making a Form

• Forms are used to input more records, but only one at a time, instead of like in Datasheet View where users see the whole list of records. You used that method last time you entered businesses.  If you enter a person, place or thing in a form, it will automatically be added to the table.  (Those objects are connected)
• Make sure the Members table is closed. Click once on it. Choose Create, Forms group, Form button.
• A new Form called Members Table appears. Close it, right click it and rename the object to New Member Form.
• Now you have four Access objects, in your Access database file called Database 2SC_xx.
• Notice the icons for each of them looks different.  Pay attention to those.  Understand what type of object you are working on.

Making a Report

• Reports are more formal, eye-appealing, can easily be formatted, alphabetized, grouped, etc.
• Make sure the Members table is closed. Now, click once on it.
  Choose Create, Reports group, Report Wizard button. Make sure Members table is the source object in the dialog box.
• Move the Business/Organization Name, and Industry fields to selected fields box. Choose Next.
• Important, use the grouping level of Industry move that field over. Choose Next.
• Sort the Business/Organization name for first level sort.
• Choose Next. Use Stepped Layout. Name the Report Chamber Industry Report. Choose Finish.
• The report should be grouping your members by Industry and then within that group, members names are alphabetically sorted.
• A report appears. Close the report. (May need to hit close – X – twice).
• Reopen the report in Layout view. In layout view, you can adjust column widths, if data is not displaying completely.
• Rename the report header (at top of report) as Member by Industry.

Additional Report Adjustments (If Needed).
If you need to make some format adjustments, as the fields are too wide apart and some text might be cut off or display as #######, use Layout View.

• In Layout View, those fieldname labels, and the data under them can be moved. You can resize them, too. Your mouse pointer will adjust to a pointer of four arrows to move as you hover over them, and a mouse pointer of two arrows to resize them. Move the fields closer together.
• Scroll to the end of the report, and fix the page numbering so it is on the page, not displaying partially on a different page.
• Make sure the report looks as good as it can. Close and save the report.
• Use Print Preview, how many pages will it take to print the report?

Final Review

• Notice now, that tables have a blue table icon, queries have an overlapping table icon, forms have a pink icon, and finally reports have green icons.
• Now you have made five objects in your Access Database called Chapter 2SC_XX.
• If you did not name an object correctly, you can right click on it and rename it. It must be closed to rename.
• If you have objects that are mistakes in your navigation pane, right click and delete them. Do not leave extra, unneeded objects in the database.
• Close the whole database file, by clicking the top, right X at the top of the window. That closes the whole file.
• Find the file on your storage device and reopen it. Practice opening and closing each object again. Close the whole file again.
• MAKE SURE the file is closed before submitting it, otherwise it will be locked.
• Database files need closed before you can submit them.
• Submit your file named Chapter 2SC xx.