Chapter 3 Scored Assignment

Access Database 3 (Chamber Members)

Objective:  Add additional records to database file through a new table, (import new table), build table relationships, perform multiple advanced queries on records.

Open the Chamber3SC file from the Week 4 Files folder.
Save the file as Chamber 3SC_xx,  with your initials on the end of the filename, instead of the XXs.

Review the records in the Member Table to understand data involved.

Import Industry Code Table
The database file may be familiar to you.  There is a new field included in the Member table, named Industry Code. The industry name is no longer in the Member table, but will be housed in a separate table. This field categorizes businesses by a common industry.  The industry code table (Excel File) needs imported from Excel, to be included as a new table in this file. 

Importing data for a new table, is a different operation than appending records to an existing table.

From the File tab, Week 4 folder, open the Industry Code Table Excel file.  Save the file.
Review the contents of Industry Code Table, noting how many fields are included, along with how many entries.  Close the table.
Return to the Chamber 3SC_xx file.  Make sure all objects are closed.
From the External Data Tab, find the Excel icon under the Import & Link Grouping.  (Not export grouping)
Choose the Excel icon, and Browse to locate the Industry_Code_Table saved in prior directions.
Follow the wizard, and choose the first radio button in how to store the data being imported.
Choose the First Row Contains Column Headings Checkbox.
Review the setting in the next step, leave those settings unchanged.
Choose the setting for setting your own primary key, as Industry Code.
Finish the wizard and name the new Table Industry_Code_Table.
The new table should now be in the file.  Open the table and format fields to see all of the field.
Close the table.

Relational Databases, General Information
Relational databases, like Access will allow for multiple tables. This is important, as you have similar groups of data. For example, in this database we have Members table, and we also have a table for Industry Code. Each table contains fields that pertain to that table and those types of records. If you could only have one table in a database, it would be very difficult to keep track of all the fields, etc. So, Access allows for multiple tables.

Remember that when you have organized data in multiple tables, you can query and report from that data, and get answers to questions. When you have accurate, detailed data, users tend to make more informed decisions.  By grouping records into specific tables, it is easier to understand and use the data.  Data is a powerful tool to help use as decisions are made.

Create Table Relationships--Joining Tables Based on Common Field
Below is a screen shot of two tables, now involved in the file.
The second screen shot shows the tables when they are joined.

Joining Tables to Establish Field Relationships
Make sure all objects are closed, in the file.
With the Chamber 3SC_xx file open, click the Database Tools tab. Find the Relationships grouping, then the Relationships button.

Right click in the gray window to select the Show Table… command. Add both tables to the window. You can right click and delete tables in this window if you get duplicates. You should have two tables when done, no more.

Move the tables around to view them as shown in the diagram, and make the members table window larger, so you can see all fields. 
Using your mouse, click and drag the field from the Industry Code Table over the top of the Industry Code field in the Members Table. Release the mouse button.
An Edit Relationship window opens, and make sure each field listed in the table dialog box shows Industry Code. Then click the Enforce Referential Integrity checkbox. A line appears, that shows the table relationship established.
Your tables should follow the diagram above.  

Perform Advanced Queries

There are many types of queries that can be performed on data in tables.  Queries are another way of asking to view specific fields and conditions, rather than a whole table.  This week, some new types of queries are included.  Typically, we query from tables.

AND Query

Use the query wizard and build a query that displays the Retail & Shopping businesses in Pendleton.
When building an AND query, both criteria need to be true.  Both criteria are typed on the same level in the criteria line.
Start the Query Wizard and choose simple query, and move the Business/Organization Name, Industry Code and City fields from the Member table to query.  Name the query R&S-Pendleton.
Run the query, and all business show.
Switch to design view of the query and on the criteria line for industry type "44-45R" and on the City line type "Pendleton".  44-45R is the code related to Retail & Shopping.
Run the query. 24 Results display.

OR Query

Use the query wizard, simple query and build a query that displays the Retail & Shopping or Restaurant/Eateries/Pubs businesses.
From the Member table, move the Business/Organization Name, Industry Code and City fields to query.  Name the query RS or Restaurants.
When building an OR query, either criteria can be true.  Type one criteria on one line, and the other criteria on the or line of the query grid.
Run the query, and all business show.
Switch to design view of the query and on the criteria line for Industry Code type "44-45R" and on the or line under Industry Code type "72REP".  These are the codes related to that industry.
Run the query. 50 Results display.

Is Null Query

Use the query wizard, simple query wizard and build a query that displays the businesses that don't have a website URL listed.
From the Member Table, move the Business/Organization Name, and Website URL fields over.  Name the query No Website.
When building an IS Null query, type Is Null on the criteria line of the field you are looking to see is empty, in this case Website URL.
Switch to design view of the query and on the criteria line for Website URL, type Is Null.
Run the query. 62 Results display.

Like *E Query

Create a new query that includes the Business/Organization and Location Phone number, for those business that start with the letter E.
Use the Query Tips handout to help determine what needs written on the criteria line for Business/Organization name to produce the list of those businesses.
Name the query accordingly, save and close the query.
Hint:  21 Results

Industry Code Query (2 Table Query)
Create a new query that includes the Business/Organization and Industry Code from the Member Table, AND the Industry Name from the Industry Code Table.  
Sort the results from Z-A (descending order), by Industry Name.  Widen fields in finished query if needed to see data.
Name the query accordingly (Member By Industry), save and close the query.

Filter By Selection--Member Table

Make sure all your objects are closed.  Open the members table.
Click on the Industry Code field.
Find the Sort & Filter group in the Table Tools contextual Menu (contextual menus appear when you are clicked on specific objects, and provide more advanced tools).
Within the Industry Code field, click on the first entry for code 62HW.  Choose the Selection button, from the Sort & Filter grouping.  Select the command for Equals 62HW.  This should filter your data to just show those codes.  
Filters are different than queries, and quicker in some ways to see specific results quickly.
Filter results show 39 entries.  Save and close the table.  
Reopen the Member table, and find the navigation pane at the bottom of the table window.  
Click the Unfiltered Button, represented by the funnel and x.
The data switches to the Filtered data, created earlier.
Users can toggle the button to see the filters, or not see the filters.

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 seven objects in your Access Database called Chamber 3SC_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 Chamber 3SC xx.