-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path09-tools.qmd
More file actions
101 lines (60 loc) · 4.97 KB
/
Copy path09-tools.qmd
File metadata and controls
101 lines (60 loc) · 4.97 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
# Tools {#sec-tools}
::: callout-intro
- Analyze tables and detect data inconsistencies\
- Compact and repair databases to improve performance .
:::
## About Tools {#sec-tools-about}
Access provides several tools to help manage and optimize your database. This chapter covers the following:
- Table analysis\
- Compacting and repairing databases
Additional useful tools in Access include:
- Performance Analyzer\
- Database Documenter\
- Encrypt with Password\
- Creating a Switchboard
## Analyze Table {#sec-analyse-tables}
**Goal:** Detecting inconsistencies in tables.
When the same piece of information is stored in multiple places, it's called **redundancy**. This is problematic because if that information changes, you must update it in every location. If you don’t, your database becomes **inconsistent**—meaning the same data exists in contradictory forms.
Access includes an [Analyze Table]{.uicontrol} wizard that helps detect redundancy and split a table into multiple related tables for more efficient data storage. This process is called **normalization**.
You can allow the wizard to decide how to split the data, or you can make those decisions manually. After splitting, the wizard can help you clean up the original data. At the end, you can choose to create a query that merges the normalized tables into a view that resembles the original.
In @fig-tools-table-contacts, you see an example of a table named [Contacts]{.varname} with lots of duplicated data—this table is not normalized. Access can split it into multiple tables so that redundant data is minimized.
{#fig-tools-table-contacts}
:::::: {#prp-tools-analyze}
[Practice file]{.smallcaps}: `tools.accdb`
1. Open the practice file.
2. Select the [Contacts]{.varname} table.
3. Go to [Database Tools \> Analyze Table (group Analyze)]{.uicontrol}. The **Table Analyzer** wizard opens with an introduction explaining redundant data.
4. Click [Next]{.uicontrol}. The next screen provides information on how the wizard can resolve redundancy.
5. Click [Next]{.uicontrol} again. The wizard asks which table contains redundant data.
6. Select the [Contacts]{.varname} table and click [Next]{.uicontrol}. Now you'll choose whether you or the wizard will decide how to separate the data.
7. Select the option to make the decisions yourself, then click [Next]{.uicontrol}.
{#fig-table-analyzer-wizard}
::: {.callout-note title="New tables"}
In this step, you can create new tables and assign fields to them.
:::
8. Select the [ContactName]{.varname} field and drag it outside the current table area. A new table is created containing that field. The wizard prompts you to name the new table.
9. Name the new table [Contactpersons]{.varname} and click [OK]{.uicontrol}.
{#fig-table-generated}
::: {.callout-note title="Relationships"}
- A relationship is automatically created between the original and the new table.\
- You can adjust the table positions and sizes by dragging them or their borders.
:::
10. Drag the fields [CompanyName]{.varname} and [Tel]{.varname} to the [Contactpersons]{.varname} table.
{#fig-tables-splitted}
11. Click [Next]{.uicontrol}. The wizard asks if you want to create a query.
12. Choose not to create a query and click [Finish]{.uicontrol}. Now three tables exist:
- [Contacts]{.varname} (original)\
- [Contactpersons]{.varname} (new)\
- [Tabel1]{.varname} (new)
::: {.callout-warning title="Possible warning"}
You may see a warning message stating that the command [TileHorizontally]{.uicontrol} is unavailable. If so, click [OK]{.uicontrol}.
:::
13. Close and save the tables.
14. Delete the original [Contacts]{.varname} table.
15. Rename [Tabel1]{.varname} to [Contacts]{.varname}.
::::::
## Compact and Repair Databases {#sec-compact-repair}
**Goal:** Defragmenting and repairing database files.
Over time, as you add, edit, and delete records, your database can become fragmented, resulting in slower performance. Deleting records or objects may leave behind unused space. Although Access attempts to repair issues when you open a database, it doesn't always catch every problem. In such cases, you should manually run the **Compact and Repair** utility.
Compacting also affects AutoNumber fields. If records at the end of a table with an AutoNumber field are deleted, the next new record will continue numbering from the last used value.
To manually compact and repair a database, go to [Database Tools \> Compact and Repair Database (group Tools)]{.uicontrol}.