Homework answers / question archive / CP 1100 Access 2 Spring Please read each of the following instructions carefully,  before proceeding: 1

CP 1100 Access 2 Spring Please read each of the following instructions carefully,  before proceeding: 1

MS Access

Share With

---

CP 1100 Access 2 Spring

Please read each of the following instructions carefully,  before proceeding:

1. Click: on MS-Access - Open - Select Drive – Double Click:  Train1

2. Click: Create – Query Design – Client – Add – Close (x)

3. a) To Maximize the Query1 Windows

b) Drag Lower Panes of the Windows Down

c) Enlarge Down Client Box Until all Fields are Visible  4. Double Click: Client Number - Name - Tech Number  5. a) Double Click ! (From the Tool Bar to Run the Query)

b) To PrintPreview 

c) File – Print – Print Preview

e) Close – Yes – Query1 – Ok 

 6. a) To Add All Fields:

b Create – Query Design – Client – Add - Close

c) Double Click on * :: Or You can Double Click on each Field d) Click on Run ! 

e) File - Print

f) Print Preview – Close - Yes

g) Save As 

h) Query2 

j) OK

7

7. a) Click: Create – Query Design – Client – Add - Close   b) Double Click: Client Number – Name – Billed – Paid  c) In Criteria Box: Under Client Number – Type in: DE76 d) Run - Close - Yes

e) – Save As: Query3

f) Click: Create – Query Design – Client – Add - Close g) Double Click: Client Number – Name – Billed – Paid  h) In the Criteria Box: Under Name - Type in: Like Gr* i) Run - Close - Yes

j) Save As: Query4

 8. a) Click: Create – Query Design – Client – Add - Close  b) Double Click: Client Number – Name – Address – Billed  – City 

c) Criteria Box: Under City – Type in: Grattan 

d) Run – Close - Yes

e) – Save As: Query5

 9. a) Click: Create – Query Design – Client – Add - Close  b) Double Click: Client Number – Name – Billed – Paid  c) Criteria Box: Under Paid – Type in: 0

 d) Run – Close - Yes 

e) – Save As: Query6

 10. a) Click: Create – Query Design – Client – Add - Close  b) Double Click: Client Number – Name – Billed – Paid  c) Criteria Box: Under Billed – Type in: >300  d) Run – Close - Yes

e) – Save As: Query7

8

 11. a) Click: Create – Query Design – Client – Add - Close  b) Double Click: Client Number – Name – Billed – Paid   – Tech Number 

 c) Criteria Box: Under Billed – Type in: >300

– Under Tech Number – Type in: 12 

 d) Run – Close - Yes

e) – Save As: Query8

 12. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Client Number – Name – Billed – Paid   – Tech Number 

 b) Under Tech Number - Click on: OR Box: Type in: 12  c) Run – Close - Yes

d) – Save As: Query9

 

13. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: City

 c) In Sort Box: Click: Drop Down Arrow - Ascending  d) Run – Close - Yes

 e) – Save As: Query10

14. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Client Number – Name – Tech Number   - Billed 

c) Under Tech Number – Click: Sort Box - Drop Down Arrow  – Ascending

d) Under Billed – Click: Sort Box – Drop Down Arrow - Descending

 e) Run – Close - Yes

f) – Save As: Query11

 

9

 15. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: City

 c) Sort Box: Under City – Drop Down Arrow – Ascending  d) Click the Next Blank Field on the Right

e) Right Click – Properties – Unique Values – Drop Down  Arrow – Yes

 f) Close this Properties Menu

 g) Run – Close - Yes

 h) – Save As: Query12

10

Join Tables 

 

 16. a) Click: Create – Query Design – Client – Add – Close  b) Right Click at Any Open Area in the Query Pane  c) Click: Show Table – Technician - Add

 

 d) Close 

 e) Extend the size of the Field List to Display All Fields   f) Client: Double Click: Client Number – Name – Tech Number  g) Technician: Double Click: Last Name – First Name   h) Run – Close - Yes

i) – Save As: Query13

 

Restrict the Records in a Join 

 17. a) Click: Create – Query Design – Client – Add – Close  Double Click: Paid 

 b) Criteria Box: Under Paid – Type in: >250

 c) Run – Close - Yes

 d) Save As: Query14

11

Using Calculated Field 

 18. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Client Number – Name – Bill – Paid   c) To the next Blank Field Space: Right Click - Click: Zoom  d) Type: Total Amount:[Billed]+[Paid]

 e) Click: OK

 f) Run – Close - Yes

 g) Save As: Query15

 

 Calculate Statistics

 19. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Client Number – Name – Billed – Paid   – Tech Number 

 c) Under Client Number – Click in the Sort Space - Right Click   d) Click: Totals - Billed

 e) Under Billed – Click: Group by

 f) Click: Drop Down Arrow – Select Avg

 j) Run – Close - Yes

 h) – Save As: Query16

12

 

Use Criteria in Calculating Statistics

 

 20. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Tech Number – Name – Address – Zip Code  c) Right Click on Tech Number – Select Totals

 b) Click: Under Tech Number - Total Row – Group by  c) Go to the right - in the Empty Field – Right Click – Zoom  d) Type: Total – Ok 

 in the Total Row – Click: Drop Down Arrow – Select Where  e) Click on Show Box - Under Tech Number

 f) In Criteria Box: Type 11

 g) Run – Close - Yes

 h) – Save As: Query17

13

 Use Grouping

 21. a) Click: Create – Query Design – Client – Add – Close  b) Double Click: Tech Number – State

 c) Under State - In the Criteria Box: Type: MA  c) Right Click on Tech Number – Select Totals  d) Click on Show Box - Under Tech Number  e) Run – Close - Yes

 f) – Save As: Query18

Create a Report using a Query file

22. a) Under Objects Menu: Click: Query13

 b) Click: Create - Report Wizard - Select all Fields –  c) Click: Next – Next – Next – Next – Next 

 d) Type in: Report On Join Tables - Client and Technician  e) Click: Finish

 f) Print

 g) Close

 h) Under Objects Menu

 i) Click on: Report On Joint Tables - Client and Technician  j) Repeat Steps a) to f) for Query11

 k) Name the Report – Query 11 Report