Building a table and defining relationships between tables.

1. Learn the guidelines for designing databases and setting field properties
2. View and modify field data types and formatting.
3. Create a table in design view.
4. Define fields and specify a table's primary key
5. Modify the structure of a table.
6. Import data from an Excel worksheet.
7. Create a table by importing an existing table structure.
8. Delete, rename, move, and add fields
9. Add data to a table by importing a text file.
10. Define a relationship between two tables.

Important Points.
1. Rules of Data Normalization: 1st Rule - Eliminate Repeating Groups - Make a separate table for each set of related attributes(fields), and give each table a primary key.
2. When creating a database the first question to ask is what is the output going to be.
3. Identify all of the fields needed to produce the required information.
3a. Decompose the fields into their smallest useful part.
4. Group related fields into tables.
5. Determine each table's primary key.
6. When more than one field is used as a primary key it is called a composite key.
7. Include a common field in related tables.
8. Avoid data redundancy.
9. Rules for naming fields.

a. A name can be up to 64 characters.
b. A name can contain letters, numbers, spaces, and special characters.
c. A name cannot start with a space.
d. A table or query name must be unique with a database. A field name must be unique with a table, but can be used in other tables.
e. Capitalize the first letter of each word in the name.
f. Avoid extremely long names.
g. Use standard abbreviations.
h. Do not use spaces in field names or object names.

10a. Determine the properties of each field.
10. The data type determines what field value you can enter for the field and what other properties the field will have.
11. The Field Size property defines a field value's maximum storage size for test, number, and Auto Number field only.

Table 1
Access Field Types

Field Types
Auto Number A number that is assigned automatically and never changes again.
Currency Dollar amounts
Date/Time Dates and times
Hyper Link Hyper Link address HTTP, FTP, E-mail, .doc, .etc..
Lookup Values that come from another table, a query, or a list of values you supply.
Memo Can contain numbers or letters up to 64,000 including spaces
Number Byte (0 to 255), Integer(-32768 to 32767), Long Integer(-2,147,483,648 to 2147483647), Single precision( 7 decimal places, 4 bytes), Double precision(15 decimal places, 8 bytes). Decimal(28 decimal places, 12 bytes),Replication ID.(16 bytes)
OLE - object Picture, sound, or video
Text Can contain numbers or characters up to 255 bytes including spaces.
Yes/NO Can contain Boolean values (yes/no, true/false, on/off) Can contain only one of two values

12. The structure of the table can be modified at any time. Fields can be added, deleted, and properties changed.
12a. Changing the data type of a field in datasheet view: Open the table in datasheet view, select the field, in the data type & Formatting group on the datasheet tab, click data type arrow, click the new data type you want.
12b. Changing the format in datasheet view- Open the table in datasheet view, select the field you want to change, in the data type & formatting group, click the format arrow, select the format you want.
12c. Creating table in design view - click create tab on the ribbon, in tables group click table design button. In design view you define or modify a table structure or the properties of the field in a table.
12d. Defining a field - in the field name box type the name for the field, press tab, select the data type, use the file properties pane to type or select other fields properties as appropriate.

13. You can copy and paste records from a table in the same database or in a different database only if the tables have the same structure.
14. When you import a table from one Access database to another, you place a copy of the table in the database into which you import it.
15. Updating, or maintaining, a database is the process of adding, changing, and deleting records in database tables.
16. Primary Field - data for each record is unique, can never be null, and is 1st index of the table.
17. Specify a Primary key - select the field you want, in the tools group on the table tools design tab click the primary key button.
18. Add records to a new table- Open table in datasheet view, click on first field, enter data, tab to next field until record is completed.
19. Import data from Excel- Click External Data tab on the ribbon, in the import group on the external data tab, click excel button, Click browse, select file, click Append a copy of the records to the table, select table to add data to, click Ok, click next, click next, click finish.

20. Create a table by importing an existing table structure- click external data tab on the ribbon , click access button, browse, select database, make sure the import tables, queries, forms,reports, macros, and modules into the current database option button is selected, click ok, click options, select table, click definition only button, click ok. click close.
21. Delete a field in datasheet view- select table, click datasheet, click field column heading,in the fields and columns group on the datasheet tab click delete.
22. Delete field in design view- click view, click field name, click delete rows.
23. Rename field in design view: - click on the name and change it.
24. Importing a text file.- click external data tab on the ribbon , in the import group click text file, click browse, select text file to be imported, click append a copy of the records to the table, select the table to be added to, click ok, make sure delimited option button is selected., make sure comma option button is selected, click next, click finish.
25. A join is the process of relating tables together with a common field.
26. A one to many relationship exists between two tables when one record in the primary table(the one) matches zero or many records in the related table(the many).
27. Referential integrity - you can not enter a record in the common field of the related table unless it already exist in the primary field of the primary table. This prevents orphaned records in the related table.
28. Cascade update permits the change in the value of the primary key and changes the appropriate foreign key values in the related tables.
29. Cascade deletes- deletes record in the primary table and deletes all records in related tales. Do not check this one.
30. The relationship window illustrates the relationships among the tables. Relationships can be created, modified, or deleted from here.
31. To define a relationship: Click the database tools tab on the ribbon, in the show/hide group on the database tools tab click the relationships button, double click the tables you want to create a relationship with, click close on the show table dialog box, use the pointer to drag the primary key in the primary table to the common key (foreign key) in the related table(s)., click enforce referential integrity, click cascade update related fields, click create.

Best Practice: When setting up a database in a company, make sure that you discuss the project with everyone who will be using the database. The executive director will request different data then will one of his subordinates. The key person is the data input clerk. Data integrity must be insured and the data input clerk is the one putting the data into the program. If they are unclear as to their data entry requirements data error will be the rule.