Chapter 1: Scored Assignment

Access Database 1 (Chamber Members)

Objective:  Create an Access table from blank database, setup table structure/fields, data types, enter new records, format table.

Website Resource:  GCF Site--Working With Tables Links to an external site.

Sample Solution

Review Data
Before working with data, review and understand what the data is about, how much information is included.  Review the table above.

Start MS Access

Use the database application (MS Access) to create a Chamber Member database.  

• Start MS Access. Start a NEW Blank desktop database
• Access makes you save the file first.
• Save the file on your storage device as Database 1 XX.  Add your initials at end of filename.

A table is open in the database window ready for you to start.
Use the View button, change the table to show in Design View. Save the Table as Chamber Member Table, when prompted.

In the table structure setup window, type in the following field names and data types as follows below.

• Field 1: ID (AutoNumber)
  • Change field to read MemberID, instead of ID. Leave it as an AutoNumber field data type. (Access will number them when input business names)
• Field 2: Business/Organization Name (Short Text)        
• Field 3: Physical Location Phone (Short Text)
• Field 4: Mailing Street Address (Short Text)
• Field 5: Mailing City (Short Text)
• Field 6: Mailing State (Short Text)
• Field 7: Zip Code (Number Type)
• Field 8: Website-URL (Short Text)
• Field 9: Industry (Short Text) 
• Close the table by right clicking the X within the table window, not the X at top right of your screen (that would close the file). Objects are closed this way. You will be prompted to save the table. Choose Yes. Resave.
• You should see the table in the navigation pane at the left of your window.

Navigation Pane (Datasheet View or Design View) & Adjust Field Size

• The navigation pane will hold the database objects as we create them. 
• Double Click to open and see information in datasheet view.
• Right click  which allows for design change of the table structure, field settings  or editing of fieldnames.
• Right click and reopen the Members table in Design View.  Click on the State field.
  In the General tab below the field list, specific setting are available. This allows more standardization of data and less input errors.  Change theState field size from 255 to 2, so users will only be allowed to enter two letters for state. Close and save the table.

Design View or Datasheet View
Design View lets you fix or change field names/properties
Datasheet View lets you input people names, address, etc.
Open the Members Table. Make sure you are in Datasheet View.
Each business's information will be 1 record. If you had 10 businesses in your database table, you would have 10 records.

• Click in the Business/Organization name box, and start entering text across the table with information for each field as shown in solution. When you start typing in the business name field, Access will automatically number your businesses with a MemberID.

Enter the 15 business names and related information for each field in the table. 
Access saves what you type as you move across the table of fieldnames.

Remember, all the information on one person, place or thing is called a record. 
You will have 15 records (15 Businesses) in this table when you are done. Close the table. Reopen the table. Information should be there. Practice switching between Design View and Datasheet View. (Hint: Right click on the object name to switch between views.)
Notice the navigation bar and bottom of table window, which allows a user to move between records using the arrows.

• Make sure all columns are wide enough to display the data.  You can easily size fields by clicking on the top left square of the table which selects all the fields, then use sizing arrows (double click) on column heading lines to adjust field columns wider so ALL field name displays.  This is like sizing columns in Excel.

Set Primary Key

• Open the Member table and set the Primary key field on Member ID field.  
  This will be a unique number only used once for each member, it cannot be duplicated.

Close All Objects, Close Database File

• Close Table. 
• Close Database file. 
• Submit for Scoring