Transcript Access

Access
Review
Creating tables
• Define the tables before start creating in Access
– use the DB design materials
– define the type of fields and length
C
u
s
t
o
m
e
r
P
a
r
t
s
O
r
d
e
r
s
P
(
P
A
R
T
N
O
,
D
E
S
C
R
I
P
T
,
C
O
S
T
,
A
V
A
I
L
A
B
L
E
)
C
(
C
U
S
T
N
O
,
C
U
S
T
N
A
M
E
,
C
U
S
T
T
Y
P
E
)
O
(
C
U
S
T
N
O
,
P
A
R
T
N
O
,
Q
U
A
N
T
I
T
Y
)
• Create each table in Access
– select Tables, new, design view
– enter definition in design view
Entering and editing data
• When you "created" you just created the table
structure
• How to add data to a table?
– After you save the table select datasheet
– and enter the data as in the example
– save frequently
• How to edit?
– Select table, go to datasheet view and add, delete, or
change.
NOTE: select Database Window
tables queries, forms, etc.
to navigate between your
Creating relations
• Close all design window windows before starting
• Use Tools, Relationships or the icon
• Click on each table in the Show Table Window to
place them in the work area
• Click on the field of the one table and paste it on
top of the same field in the many table
– Access will open a relation dialog, be sure to select
enforce referencial integrity
– repeat this step as necessary
– you will see the relations as in this example
Creating forms
• There are six types of forms: single column, tabular,
datasheet, subforms, pivot tables and graphs.
• In the database window, select Forms, New, and select the
table you will create the form for.
– You can use a series of wizards or the default Design View. In your
project you can simplify your work if you use the columnar
autoform.
– If you use the Design View, place the table fields and toolbox in
the work area using their toolbar icons
to make your
work easier, as show in this image.
– Save and name it.
– Use help for more details.
Creating reports
• Simple reports can be created by selecting
Reports, New, selecting the table or query to use,
and Tabular autoreport.
• More complex layouts can be created using
Design View. Again fields and toolbox will be
available to create the report.
• A shortcut to create complex reports is to use
Tabular autoreport and open it in Design View,
edit to add sub-totals, etc.
• Again save it, don’t forget.
• Use help for the details.
Creating queries
• Start from database window and select queries, new and
Design View
– you will see a blank query grid and Show Table window
– select the table(s) to use in the query (double-click) and close the
Show Table
– double-click in the fields you want to place in the query (in the
order you want to see the results)
– You can easily sort the results and create calculated fields.
• You can create a new table with the results of a query
– In Design View click the arrow next to Query Type button on the
toolbar, and then click Make Table.
– The Make Table dialog box appears. In the Table Name box, enter
the name of the table you want to create and select current database
and save it.