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.
Step 2: Click on Click to Add, type T23.
Step 3: Press Enter and type iPad. Thenpress Enter twice.
“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).
Figure 1: Click the View drop-down menu on the Ribbon toolbar, and click Design View.
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’.
Step 6: Press the Tab key and in the Data Type drop-down menu, select Number.
Step 7: Type IDcode in place of ‘Field1’ (Figure 1) and Description in place of ‘Field2’ (Figure 2).
Figure 1: Type IDcode in place of ‘Field1’
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.
Step 9: Go back to Datasheet View by clicking View, Datasheet View on the Ribbon toolbar. Whenever prompted, click Yes to save the table.
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:
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.
Step 2: Choose the Database Tools tab on the Ribbon toolbar, and tap the Relationships button.
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.
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.
Step 5: Choose the Enforce Referential Integrity checkbox, and click Create.
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.
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.
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.
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).
Figure 1: Select the Table: Electronics from the Tables/Queries menu, click ID code, and click the single arrow
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.
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.
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.
Tags: access 2016 database ebook, access database table design, access database tables, Access database tutorial, ebooks on access database, microsoft access database, ms access ebooks, Your Access Database Expert