Introduction to Access 2003: Creating Tables and Establishing Relationships

1. Introduction to Access 2003

2. Click on the Access desktop icon or go to Start/Programs/Microsoft Office/Microsoft Office Access 2003Click on Create a new file …

3. Click on Blank database …

4. In the File New Database dialog box, use the Save in drop down box to select a location and the File name textbox to name your database and Click the Create button

5. Double click on Create table in Design view

6. Start entering fields for a Customer table. Start with a CustomerID which will serve as a primary key.

7. Data Types Help

8. Data Types Help (Cont.)

9. Some Choices • We have to select a Data Type for this field. • We will select Text. • Another possibility is AutoNumber – this choice guarantees uniqueness and is generated automatically, but it is also out of one’s control and can lead to problems/confusions.

10. Click in the Data Type textbox and use the drop-down list to select the type. Then enter a description.

11. Right click in the gray area at the beginning of the row and select Primary key from the context menu.

12. Next change the Field Size to 7 and the Required to Yes and Allow Zero Length to No

13. Enter other customer fields as shown

14. To require or not to require • One would certainly not require CustomerStreetAddress2, but one might consider setting the Require property of the other fields to Yes. • Requiring a field can help ensure that the data is complete, but it can lead to some very unforgiving data entry scenarios.

15. When adding a field for the customer’s phone number, we might want to use an Input Mask. Click in the Input Mask textbox and click the ellipsis (…) button that appears. You will be prompted to save the table.

16. Enter a name for the table and click OK.

17. Select the Phone Number choice from the Input Mask list of the Input Mask Wizard and click the Next button.

18. The parentheses, space and hyphen appear automatically, the 9’s means that the area code is optional, the zero mean the rest of the numbers are required.

19. Message I got because I did not supply all of the required numbers when entering an example in the Try it box.

20. Decide how number will be stored.

21. Finished mask from Wizard.

22. Oops forgot the zipcode! Place the cursor in the PhoneNumber row and go to Insert on the menu and choose Rows.

23. Go through Input Mask Wizard to obtain Input Mask for a zipcode.

24. Add a CustomerEmail field and click Save

25. If we wanted to start entering Customer data, we would go over to Datasheet view found under the View button.

26. Datasheet view of Customer table

27. Instead start designing an Order table, the first field is an OrderID which will serve as a primary key for the table.

28. Foreign keys • Next we establish the relationship “customer places order.” • This is a “one-to-many” relationship. One customer may place many orders, but an order is placed by just one customer. • This is done by making the next field on the Order table the ID of the Customer who has made the order. • In the Customer table, CustomerID is a primary key, but here in the Order table it is known as a foreign key.

29. Use the drop-down box in the Data Type area to select the Lookup Wizard.

30. One can use the second option to provide a set of acceptable values for a field, but in this case we want to get values from the Customer table.

31. Later on we can look up the results of queries but at this time we only have tables.

32. Use the arrow button to send over the CustomerID as well as CustomerLastName and CustomerFirstName

33. While the data entry person is looking up a CustomerID, they will be looking at customer names, so let us put them in alphabetic order. Use the drop-down boxes to choose the appropriate fields.

34. Note the Hide key column checkbox, the data entry person will see the customer name (and not the key) although they are looking up the key.

35. Select a label for column in Order table.Click Finish.

36. Using a Lookup is establishing a relationship and that the table involved in that relationship has to be saved before the relationship can be established. Click Yes.

37. Add OrderShippingCost and OrderTotalCost fields. They should be of the Currency type.

38. Calculated Fields? • It may be that the OrderShippingCost or the OrderTotalCost can be calculated from other information stored about the order. • And you might read somewhere that you should not store “calculated fields.” But there are at least two reasons not to follow that advice here. • Sheer convenience. These are important quantities that one wants to view readily without looking up all of the information required for the calculation. • The calculation might change but the customer pays based on the calculation made at the time of the order.

39. Add OrderPaymentMethod field (could make should list of possibilities using Lookup Wizard) and OrderShipped field which will be of a Yes/No (Boolean) type.

40. Shipping Address • One could add fields for the shipping address and a yes/no field asking whether the shipping and customer addresses are the same. • But a customer may place many orders all with the same shipping address which is different from the customer’s address. If the shipping address is in the Order table, the same address may have to be entered over and over. • This is known as data redundancy and database design tries to minimize it. • For now we will pretend the customer address and shipping address are the same until we are ready to address this issue.

41. Add fields for when the order was placed and when it was shipped, these should be of the Date/Time type.

42. Save the Order table and start designing an Item table. Note the ItemDescription is of the Memo type.

43. The OrderLine or ItemLine or ItemInOrder Table • An order may contain many different items, an item may be part of many different orders. Thus the order-item relationship is “many-to-many.” • The many-to-many relationship is realized by having a separate table. (Our previous tables corresponded to entities.) • If you had an order form, the data in this table would correspond to a single line on that form. Thus the table is often called the OrderLine or ItemLine table. If you want the table named after the relationship it is establishing, you might call it the ItemInOrder table.

44. Start the new table, the first field in the table is a foreign key establishing the relationship to the Order table.

45. Unlike the customer lookup where we show the customer name but look up the ID, with order we will use just the ID. To finish off the Lookup Wizard, our new table must be saved. We get a prompt here because we have not yet established a primary key for this table. Let us put this off, so click No here.

46. The second field is the ItemID, again use the Lookup Wizard – this time one can select both the ID and name fields.

47. Multiple-field primary key help.

48. How to select a multiple-field primary key

49. Primary Key button

50. Add ItemPrice and QuantityOrdered fields of Currency and Number types respectively. Because of sales, discounts, and changing prices, we place price in this table instead of in the Item table.