Make a Database Using MS Access

This article shows how to create a database using Microsoft Access.

Steps

  1. Create a blank database. First, launch Access and choose File> New.
  2. Choose a blank database since you will be building it from scratch.
  3. Table 1 will appear on the screen. Click on "Click to Add".
  4. Enter details for the first two fictional employees. Type Mary, press Enter to move to the next column, and type Smith. Press Enter twice to move to the second column of the second row, and type Peter, press Enter,and finally type Johnson.
  5. You need to alter the design at this point, because it does not have any table headers, so that you can use employee ID numbers. To make these changes, click the View drop-down menu on the Home tab of the Ribbon toolbar, and select Design View.
  6. Type Employees as the table name when the Save As box as the table name appears (in the tab beneath the menu bar) and click OK.
  7. Once you've typed some data, you can fine-tune the design by moving to Design View.
  8. Once in Design View mode, type EmployeeNumber in place of the 'ID' field name.
  9. Press the Tab key, and in the Data Type drop-down list, choose Number.
  10. Change 'Field1' to read FirstName and 'Field2' to read LastName.
  11. Return to Datasheet View by clicking View, Datasheet View.
  12. Click Yes when prompted to save the table.
  13. Type the numbers 2011 and 2012 in the first column of the table.
  14. Once the table is complete, save it by right-clicking the Employees tab and clicking Save.
  15. Create a Table to Contain Item Information. To create a second table in which to store information about the electronic devices your employees are using, select the Create tab on the Ribbon toolbar, and click Table.
  16. Click on "Click to Add", type T23, and press Enter. Type iPhone, and press Enter twice. ("T23" and "iPhone" are just examples. You can use whatever number scheme makes sense for your business.)
  17. Each item must be allocated to the employee who is in possession of it. Click the View drop-down menu on the Ribbon toolbar, and click Design View.
  18. Type the table name Electronics, and click OK.
  19. In the Design View, type EmployeeNumber in place of the field name 'ID'.
  20. Press the Tab key; in the Data Type drop-down menu, choose Number.
  21. Type IDcode in place of 'Field1' and Description in place of 'Field2'.
  22. Currently the EmployeeNumber field is set to be a "primary key" field, which prevents you from entering duplicate data in that field. You’ll need to change that setting so that you can enter the same employee number multiple times if that employee has several devices checked out.
  23. Click anywhere in the EmployeeNumber field in the table, and click the Primary Key button on the Ribbon toolbar to remove the Primary Key setting from this field.
  24. Return to Datasheet View by clicking View, Datasheet View on the Ribbon toolbar. When prompted, click Yes to save the table. Type 2011 as the employee number for the first electronics item in the list. Then just continue or you can follow the image below.
  25. Establish a Relationship Between the Tables. Now that you've set up the table design, you can enter data into the table.
  26. First, save and close each table by right-clicking each table's tab and choosing Close (click Yes if prompted).
  27. Next, select the Database Tools tab on the Ribbon toolbar, and click the Relationships button.
  28. When the Show Table dialog box appears, click on each table name in turn, click Add, and then click Close.
  29. Drag the EmployeeNumber field from the Employees box, and drop it on top of the Employee Number in the Electronics box. 
  30. The Edit Relationships dialog box will open when you do this. Select the Enforce Referential Integrity checkbox, and click Create.
  31. The Relationships tool allows you to create the link between the two data tables.You should now see a line between the two tables, with a 1 on the Employees side and the infinity symbol on the Electronics side. This line describes a one-to-many link: One employee can have many devices, but each device can be allocated to only one employee.
  32. Create a Form to Enter and View Data. Now that the two tables are linked, you can create a form that will make it easy to add employees and devices.
  33. On the Ribbon toolbar, click Create, Form Wizard.
  34. When the Form Wizard dialog box appears, select Table: Employee from the Tables/Queries menu, and click the double-arrow button to add all the fields to the Selected Fields list.
  35. Next, in the Table/Queries menu, click the Table: Electronics entry, click IDcode, and click the single arrow.
  36. Finally, click Description, click the single arrow, and then click Next.
  37. When you see the 'How do you want to view your data?' prompt, click by Employees, choose Form with subform(s), and then click Next.
  38. Now, click Datasheet, Next, and Finish to name the forms with the default names and to open the form to view information.
  39. Your form will open on the screen, showing details for the first employee in the Employees table as well as all the electronic devices that have been assigned to that person.
  40. You can move from one employee to the next using the navigation tools at the foot of the screen. Here too is a 'New (Blank) Record' button that you can click to add a new employee.When you add a new electronics item, Access will associate it with the current employee automatically.

You may like

Retrieved from "https://kipkis.com/index.php?title=Make_a_Database_Using_MS_Access&oldid=274947"