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: In older versions select Database Window
navigate between your tables queries, forms, etc.
to
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 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
– select 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.
Creating reports
• Simple reports can be created by selecting Reports, New, selecting the
table or query to use, and clicking on the Basic Report icon .
• More complex layouts can be created using the Report Wizard. Click on
the Report Wizard icon and you will see the fields to choose from. The
order you choose the fields will be the one shown in the report, click
next, ignore grouping (click next again), now select a sort order (e.g.
customer, product or order), click next, select Tabular, next, next and
give a Title to the report, and click Finish.
• At this point you have your report formatted, but you have no total and
sub-totals.
• Select the basic report in the left pane, right-click and choose design
view. Now add totals to the report: right-click in the design view, select
Sorting and grouping and a toolbar will be added at the bottom to do so.
• Click on the toolbar to group by a field (e.g. customer number) and
choose the field to create totals as in this example.
• Save everything. You are mostly done. You can now embellish the report
at your hearts content. Use help for more details.
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.