Building Your First Database with Microsoft Access- Part 2

Creating a Table to Contain Item Information

To view Part-1 of this series, (by Ben Beitler ), Click here >>

To create a second table in which to store information about the electronic devices your workers are using, follow these steps:

Step 1: Select the Create tab on the Ribbon toolbar, and click Table.accessdatabasetutorial.com

Step 2: Click on Click to Add, type T23.

image2

Step 3: Press Enter and type iPad. Thenpress Enter twice.

image3

“T23” and “iPad” are just for the purposes of illustration, of course; utilise whatever number plan bodes well for your business.

Step 4: Every item must be apportioned to the worker who is in control of it, so to change the table design to accommodate this, click the View drop-down menu on the Ribbon toolbar, and click Design View(Figure 1). Type the table name Electronics, and clickOK(Figure 2).image4Figure 1: Click the View drop-down menu on the Ribbon toolbar, and click Design View.

 

image5                            Figure 2: Type the table name Electronics, and click OK.

 

Step 5: In the Design View, type Worker Number in place of the field name ‘ID’.image6

 

Step 6: Press the Tab key and in the Data Type drop-down menu, select Number.image7

 

Step 7: Type IDcode in place of ‘Field1’ (Figure 1) and Description in place of ‘Field2’ (Figure 2).image8                                     Figure 1: Type IDcode in place of ‘Field1’

 

image9                                  Figure 2: Type Description in place of ‘Field2’

As of now the Worker Number field is set to be a “primary key” field, which keeps you from entering duplicate data in that field. You will have to change that so that you can enter a similar worker number multiple times if that worker has several gadgets checked out.

Step 8: Click any where in the Worker Number field in the table, and click the Primary Key button on the Ribbon toolbar to remove the Primary Key setting from this field.

image10

 

Step 9: Go back to Datasheet View by clicking View, Datasheet View on the Ribbon toolbar. Whenever prompted, click Yes to save the table.

image11

Now, the Electronics table no longer has a primary key’ field, so it will enable you to enter a worker number several times.

Step 10: Type 2011 as the worker number for the first electronics item in the list.

Proceed and type this data into the table:image12

Establishing a Relationship Between the Tables

At this point, the database contains two tables of related data. The next thing to do is link the tables together.

Step 1: Right-click each table’s tab and choose Close. Click Yes if prompted.image13

Step 2: Choose the Database Tools tab on the Ribbon toolbar, and tap the Relationships button.image14

Step 3: When the Show Table dialog box shows up, click the table name and click the Add button. Do this for both table names and click Close.image15

Step 4: Drag the Worker Number field from the Workers box, and drop it over the Worker Number in the Electronics box. The Edit Relationships dialog box will open.

image16

Step 5: Choose the Enforce Referential Integrity checkbox, and click Create.image17

A line between the two tables will show, with a 1 on the Workers side and the infinity symbol on the Electronics side. This line depicts a one-to-many connection: One worker can have numerous gadgets, but every gadget can be apportioned to just a single worker.image18

The Relationships tool enables you to make the connection between the two data tables.

Creating a Form to Enter and View Data

Since the two tables are linked, you can make a form that will make it simple to include workers and gadgets. This is the time when you will see the true power of Access compared with Excel.

Step 1: On the Ribbon toolbar, click Create, then click Form Wizard.image19

The Form Wizard is used to create a form that lets you work with both tables at once.

Step 2: When the Form Wizard appears, chooseTable: Workers from the Tables/Queries menu. Then, click the double-arrow button to add all the fields to the Selected Fields list.image20

Step 3: Select the Table: Electronics from the Tables/Queries menu, click ID code, and click the single arrow(Figure 1). Then, click Description, click the single arrow, and click Next(Figure 2).image21Figure 1: Select the Table: Electronics from the Tables/Queries menu, click ID code, and click the single arrow

image22

Figure 2: Click Description, click the single arrow, and click Next

Step 4: When you see the How do you want to view your data?‘ prompt, select by Workers, choose Form with subform(s), and click Next.image23

Step 5: Select Datasheet, click Next, and click Finish to name the forms with the default names and to open the form to view information.image24

 

Kind regards,

Ben Beitler – “Your Access Database Expert!

Over the many years I’ve been teaching, my friends and acquaintances constantly hassled, encouraged, and cajoled me to write a definitive guide about Microsoft Access databases, so keeping with tradition and bringing it up to date with the latest version – Microsoft Access 2016 Database eBook.

 

Leave a Reply

Your email address will not be published. Required fields are marked *