Microsoft Access Tables Relationships
Shared by: nsr11162
Microsoft Access: Tables & Relationships This workshop requires completion of "Access: Basics". Topics include importing and linking tables, data types, formats, input masks, data entry, table analyzer, lookup values, copying tables, primary keys and multiple primary keys, relationships and AutoForms. Worksheets: Access Tables, Access Relationships Topics: - Relationship Presentation - Junction Tables - Primary Keys - Foreign Keys - Creating a New Database - Linking a table - Changing Data in Both Sources (Access & Excel) - Deleting a table - Importing a table - Design View - Field Types, Sizes and Formats - Input Masks vs Formats - Sorting Fields - Table Analyzer - Look-up Values - Relationships - Enforcing referential integrity - Copying Tables - Multiple Primary Keys - AutoForm Microsoft Access – Tables Page 1 There are several ways to create a new table: entering data (Datasheet View), creating the fields (Design View), using a Table Wizard, Importing a Table and Linking a Table. Importing and Linking are used when you have a data table already created in another file, such as Access, Excel, comma and delimitated text files, or any other data file that is organized and ready to import or link. Either option will take you through a wizard to set up your data. Linking will place a copy of the table into your database but it will still be connected to the original source of the data. If the original is changed it will change the table in Access; if the table is changed in Access, it will change the original. You will not be able to change many of the options of a linked table (such as field size or setting primary keys) because Access cannot enforce these rules on data outside of the program. If you need to change these options you should Import instead of Link. Importing a table will place a copy of the data as a Table in your database, entirely separate from the original. Create Table by Table Wizard The Table Wizard has several preset lists that allow you to choose fields for your table, based on Microsoft Access sample tables. In this wizard, you can choose which type of table, Business (such as Contacts) or Personal (such as Recipes). Then you can choose the individual fields you wish to use. Select a sample field from the list and use the button to move the selected field to the Fields in my new table: list. The double arrow will move all the fields to the right. The arrows going in the opposite direction allow you to remove selected fields from the list. Sometimes it is easier to move all the fields to the right and then move the few fields you don't want back to the original Sample Fields:. Once you have set up the fields, click the Next > button. Microsoft Access – Tables Page 2 This next screen allows you to choose a name for your table and decide if the wizard will set the primary key or if you will set one at a later time (or possibly not at all). As it says in this window, the primary key is the special field that uniquely identifies each record within your table. A primary key is not required, but it is a good idea to maintain the uniqueness of each record. Click Next > to continue. As this third screen says, this is all the information that Access needs to create the table, only one choice remains to be made, what do you want to do with this table when Access finishes creating it? Your choices are to Modify the table design (view the design view of this table), Enter data directly into the table (see the datasheet view of this table) or Enter data into the table using a form the wizard creates for me (create the table and a data entry form). If you check the help box, Access will launch the office assistant to help you through setting up the rest of your table. Microsoft Access – Tables Page 3 Create Table by Entering Data When you choose this option, Access opens the table in the datasheet view: Access gives you 20 blank records and 10 blank fields. To change the name of the fields, double click on the words in the field title (Field1). This will highlight the words and allow you to enter field name you desire. Type the name you want and press Enter. Once you have the field names set up, you can enter data into the table. When you change to Design view, Access will ask you to save the table. In the Design view you will see the fields you created and Access will set data types based on the data you entered. On this screen you can add new field names, change the data types and set a Primary Key. When you return to the datasheet view you will see only the fields and records that you created. Microsoft Access – Tables Page 4 Create Table in Design View When you choose this method to create a table you will see a blank design view. Notice at the bottom of the screen, the blue text tells you what you are filling in. In this case, the field name, Access is telling you that the name can be up to 64 characters long, counting the spaces. It also tells us that F1 will bring up the help file for field names. The help feature can actually be a very useful resource in Access. Below is an excerpt from the help file. It is a set of object-naming rules that apply to the field name, as well as a few tips to be aware of. Object-naming rules A set of specific rules for naming Microsoft Access objects. In Microsoft Access, names can be up to 64 characters long and can include any combination of letters, numbers, spaces, and special characters, except a period (.), an exclamation point (!), an accent grave ('), and brackets([ ]). Note that you also can't use leading spaces or control characters (ASCII values 0 to 31). For information on Visual Basic naming conventions, search the Help index for "naming conventions." Tips • Avoid including spaces in object names if you'll frequently refer to the objects in expressions or Visual basic code. • Avoid using extremely long names because they are difficult to remember and refer to. Once you have typed your field a name, you can choose a data type. By default the fields will be text. Here is a list of the different data types. Each will have different field properties that will appear across the bottom of the screen. There is more about each data type on the next worksheet. Microsoft Access – Tables Page 5 These are the general properties for a text field. Access provides details for each of these properties in blue text on the on the right side of the window. FIELD TYPES Text - Text can contain any alphanumeric characters (letters, numbers, symbols, punctuation). The maximum field size (the number of characters including spaces) is 255, and the minimum is 1. Access will hold the data to this limitation, for example for a State field you may limit the field size to 2, so that the user will only enter the abbreviations, "FL" instead of "Florida". There are only a few formats that can be used for text fields, ones that you may find useful are: < formats all characters to lowercase, and > formats all characters to uppercase. Input masks have to be text or date fields. In the Format or Input Mask you can use L for letters, 0 for optional numbers, 9 for required numbers, and A for numbers or letters. Memo - This type of field is very similar to the Text field, except it has no field length property. The memo field takes up only the amount of spaced used per record. A memo field is limited to 65,535 characters, including spaces. You can also use the < or > signs to control the capitalization of the characters. Number - A Number field is limited to only numeric input (numbers, decimal points, +/- signs). This is a field that can be used for mathematical purposes. There are several field sizes available for number fields - shown here on the left (see the next page for more information). There are several options for the format as well, shown here on the below. You can also set up your own format using zeros. Currency - This field type is a specialized Numeric type, which always has the $ format. Microsoft Access – Tables Page 6 Number Field Size: (From Access 2000 Help File) Storage Setting Description Size Byte Stores numbers from 0 to 255 (no fractions). 1 byte Decimal Stores numbers from -10^38 -1 through 10^38 -1 (.adp) 12 bytes Stores numbers from-10^28 -1 through 10^28 -1 (.mdb) Integer Stores numbers from –32,768 to 32,767 (no fractions). 2 bytes Long (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). 4 bytes Integer Single Stores numbers from 4 bytes –3.402823E38 to–1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values. Double Stores numbers from 8 bytes –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values. Date/Time - Date/Time allows you to enter Dates and/or Times. Access will store these dates and times similarly to Excel, such that you will be able to add and subtract from these dates. The preset formats for the date are shown on the left. If you wish to make your own date format use the table below as a guide. Dates for Friday, February 1, 2002 Day Month Year d 1 m 2 y 02 dd 01 mm 02 yy 02 ddd Fri mmm Feb yyy 2002 dddd Friday mmmm February yyyy 2002 To get the format, “Friday, February 1, 2002” you would choose the Long Date or type in “dddd, mmmm d, yyyy”. In time formats you can use the H for hours, M for minutes and S for seconds. AutoNumber - This field is most often used as a primary key, because it places a unique sequential number for each record. If a record is deleted, the table is not renumbered. You can add a format to this using letters and zeros. AutoNum Name AutoNum Name AutoNum Name 1 Jack 1 Jack 1 Jack 2 Jill 2 Jill 3 John 3 John 3 John Microsoft Access – Tables Page 7 Yes/No - This is a binary field, its format can be set to Yes/No, True/False, or On/Off. In each of these cases, there are only two options. This will usually show up in your table as a check box. If the box is checked, then the value is Yes, True or On, if the box is not checked then the value is No, False, or Off. OLE Object - OLE stands for Object Linked or Embedded. This is the option you would use if you needed to embed a file to this record, for example: a picture of employee to that employee record. When you reach the OLE field, go to the Insert Menu, and choose Object or right click and choose Insert Object. If you wish to Link instead of Embed a file, be sure to click on the Link checkbox. You will only see the file type when looking at the datasheet view (tables/queries). You should be able to see the actual contents of the file on forms and reports. Hyperlink - A hyperlink data type would be used for email addresses or web pages. Access makes any information in these fields active links, including the blue and underlined format we associate with hyperlinks. Lookup Wizard - The lookup wizard allows you to link the field to another table, in order to set up a drop down arrow to choose your data from the other table or query. This will automatically link the two tables (but will not enforce referential integrity). When using this field, be sure to check the Look Up Properties at the bottom of the window. MS Access – Relationships (from Access Online Help) About relationships in a database After you've set up different tables for each subject in your database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from five tables: How do relationships work? In the previous example, the fields in five tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields, usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the Employees table and the Orders table using the EmployeeID fields. MS Access – Relationships (from Access Online Help) DEFINITIONS Relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to many. Primary Key: One or more fields whose value or values uniquely identify each record in a table. In a relationship, a primary key is used to refer to specific records in one table from another table. A primary key is called a foreign key when it is referred to from another table. Foreign Key: One or more table fields to the primary key field or fields in another table. A foreign key indicates how the tables are related – the data in the foreign key and primary key fields must match. A one-to-many relationship A one-to-many relationship is the most common type of relationship. In a one- to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. A many-to-many relationship In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields, the foreign keys from both Tables A and B. A many-to- many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to- many relationship that's defined by creating two one-to-many relationships to the Order Details table. MS Access – Relationships (from Access Online Help) A one-to-one relationship In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Defining relationships You define a relationship by adding the tables you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table. The kind of relationship that Microsoft Access creates depends on how the related fields are defined: A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index. A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes. A many-to-many relationship is really two one-to- many relationships with a third table whose primary key consists of two fields – the foreign keys from the two other tables. Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.