Access exercise

1
Access exercise

• HSPM J13

2
Start Access
3
Choose Blank Database
4
Select where database is stored

• On your own computer, default is My Documents or
  Documents
• In lab, click folder icon to go to your storage
  device

5
(No Transcript)
6
Pick a name for the table

• Or use the default name something like Table1

7
Switch to design view
8
Add fields

• Let the ID field stay. Its an index field.
  That makes the database run better.

9
How to change the data type
10
At least 5 fields with 4 different data types
11
Switch to datasheet viewby clicking on View
12
Type a first name

• The ID field fills in automatically

13
Add six made-up entriesInclude yourself as one
14
Make a form

• Click on Table1table on the left
• Click Create tab
• Click Form

15
It gives you a basic form

• Make a change, such as to the color. Use
  auto-format to change the color scheme.

16
Move to the last record

• Then insert a new (blank) record

17
Switch to Form View

• And then add a new made-up record

18
A select query

• Create Tab, then Query Wizard

19
Choose Simple Query

• Then move all fields to the right box
• This will make all fields appear in your results

20
At the last step, click Modify Query

• This lets us filter the results
• At the bottom of the screen, type in one or two
  criteria

21
Click ! (upper left) to run the query

• This creates a new table in your database

22
Create a new table

• Click create tab, choose table, then Design view

23
Add fields
24
Switch to datasheet view.Add some records

• For customer number, use numbers from 1 to 7

25
Create another query

• Add all fields from Table2
• Then add all fields from Table1

26
System relationships

• If you didnt get the error message, this wont
  come up automatically. In that case, click on
  the Database Tools tab near the top of the
  screen. Then click on the Relationships box.

27
Add table1 and then table2
28
Drag Customer Number from table2 to ID in table1.

• In the Edit Relationships box, change the fields
  under Table 2 and Table 1 so they look like this
  (if they dont look like this already)

29
Relationship is shown
30
Create query

• Add all fields from Table 1, then all fields from
  Table 2. Then modify query design.

31
Uncheck fields you dont wantand type a last
name to match
32
Run the query

• Youll see the books that this person ordered
• Save all your tables, forms, and queries, and
  submit the file on Blackboard.

33
For additional credit

• Do an update query that raises all the prices by
  some percentage.
• Use Access Help, topic Update Query, to see how
  to do it

34
Using Help to get instructions for an update query

• Click the little question mark in the upper right
  corner.
• Search for update query.
• The second item on the list is best.
• Follow each step carefully

35
Update query for additional credit

• To raise each books price by 50, use this
  formula Price1.5
• The Help will show you where to put it
• Scroll down to here ? for instructions you
  should follow carefully
• Again, save your queries and tables and submit
  your file on Blackboard.