Access Basics:
Table Wizard

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


Before you can create relationships between tables, you need to have another table! You already created a table manually so this time you will use a wizard.

Access provides a Table Wizard to walk you through some choices to create one of the sample tables, like lists of addresses and phone numbers, students and their classes, orders, products, invoices, reservations, or tasks.

Rarely will the sample table be exactly what you want! It can still be helpful to choose the fields that most closely match what you do want. You can always make changes in Design View afterwards.

The table wizard offers a list of sample tables in two categories: Business and Personal. The example dialog below walks you through the choices to create a table of Suppliers for the items in the collection of Star Wars items.

Dialog: Table Wizard

Icon: Left click Click to see the wizard steps:
 Step 1  >  Step 2  >  Step 3  > Step 4 > Form View

In step 3 you could choose to create a relationship to an existing table. However, the shared field must already exist in the table that you choose. The SuppliersID field would be the choice for this example.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access ObjectsTo subtopics
    Relationships Arrow: subtopic open
    Icon: StepTable Wizard
    Icon: StepLookup Wizard
    Icon: StepTable Relationships
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Icon Step-by-Step 

Step-by-Step: Table Wizard

 Icon Step-by-Step

What you will learn:

to create a table using the wizard
to add records
to save the table


Start with: mytrips.mdb from previous lesson

Create a Table with the Table Wizard

The Trips table has a memo field TravelAgent for information about the travel agent. It would be useful to have that information in its own table. This will make it easier to store and update the contact information about each travel agent. If you use the same travel agent again, all the information will be there, waiting for you.

You will need fields for a mailing address, phone numbers, email address, as well as the parts of the agent's name.

  1. If necessary, switch to the Database Window.
     
  2. Click on Tables in the list of objects at the left.
     
  3. Link: Create table by using wizardIn the right pane, click on the link Create table by using wizard. The Table Wizard opens to its first step.
     
  4. Table Wizard: Step 1 - choose sample table and fieldsIf necesssary, select Business and then the Sample Table named Contacts.
    You will not need all of the fields from the sample table.
     
  5. For the following fields in the Sample Fields list, click on the field's name and then click the > button Button: Move the selected item to the list to move it to the list of "Fields in my new table":
      ContactID
      FirstName
      LastName
      Address
      City
      StateOrProvince 
      PostalCode
      Region
      Country/Region
      CompanyName
      Title
      WorkPhone
      WorkExtension
      HomePhone
      MobilePhone
      FaxNumber
      EmailName
      Notes
     
  6. Dialog: Rename fieldRename Field:
    Click
    on the field ContactID and then on the button RenameField...
     
  7. Type AgentID as the new name and click on OK.
     
  8. Click on Next to advance to the next step.
     
  9. Dialog: Table Wizard - step 2 - name and primary keyEnter the name Travel Agents for the table.
     
  10. Select "Yes, set a primary key for me".
     
  11. Click on the button Next.

    Dialog: Table Wizard - step 3 - related tablesThe next step in the wizard will let you create a relationship to an existing table. You will have to modify the Trips table before you can create a relationship. Right now you do not have a field that can be shared between the two tables.
     
  12. Click on Next.
     
  13. Dialog: Table Wizard - Step 4 - create a formSelect "Enter data into the table using a form the wizard creates for me." and click on Finish.
    AutoForm created by Table WizardThe new AutoForm opens ready for you to enter data.
     
    Since World Travel Inc. is providing this database to clients, they want all of their agents to be in this table. (We won't take the time to add the full list!)
     
  14. Enter the following records. Many fields will be blank for now.
    (Remember that these are not real people, the companies are not a real companies, and the email addresses are not real either!)
     
    FirstName Jorge
    LastName Martinez
    Country/Region USA
    CompanyName World Travel Inc.
    Title Agent
    WorkPhone 615-555-1234
    EmailName jmartinez@worldtravelinc.net

    TipIcon: KeyboardCopy previous value: You can copy the value "World Travel Inc." into later records by using the key comb CTRL +  ' (the apostrophe) when your cursor is in the field CompanyName. This key combo copies into the current record the value of the field in the previous record.
    FirstName Hector
    LastName Chavez
    Country/Region Argentina
    CompanyName World Travel Inc,
    Title Agent
    WorkPhone 555-9876
    EmailName hchavez@worldtravelinc.net

    FirstName Sheila
    LastName Landers
    Country/Region Australia
    CompanyName World Travel Inc,
    Title Branch Manager
    WorkPhone 5554-8890
    EmailName slanders@worldtravelinc.net

    FirstName William
    LastName Gardner
    Country/Region Argentina
    CompanyName World Travel Inc,
    Title Agent
    EmailName bgardner@worldtravelinc.net
    WorkPhone 555-9876
    Notes Nickname = Bill

     
  15. Message: Do you want to save changes to the design ofform  Form1?Click on the Close button to close the form. A message box appears asking if you want to save changes to the design of the form.
     
  16. Click on Yes. A small dialog window appears for you to enter a name for the form.
     
  17. Accept the default name "Travel Agents" and click on OK. The form is saved and closed.

LessonsWorking with Databases Previous Page Next Page




Teachers: Request permission to use this site with your class
 
Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~


Last updated: 30 Apr 2012