H Admin ICT Workbooks

If you wish to practice your skills at home, you can download the booklets from here:

Calculations in Queries

Calculations in Reports

IF Statements

Complex Formulae in Excel

Advanced Word Booklet

Filtering in Excel

Create Charts in Queries

Using Comments in Excel

The WP Task

This is worth a lot of marks.

Make sure you have enough time to complete it as best you can and hand it in.

You may have to do further queries and copy and paste the information in.

You will also have to copy and paste in spreadsheet information, most probably a chart.

You will also have to insert either a company letterhead or logo.

Memorandum Layout

MEMORANDUM

To:

From:

Date:

Subject:

VLOOKUP & HLOOKUP

These two functions are used to reference one table with another.

For example in one sheet called DATA we have:

A B C
1 Grade A B
2 Commission 5% 7%

This would need an HLOOKUP because the info is Horizontal.

On the other sheet called SALES we have:

A B C
1 Grade Surname Firstname
2 A McGowan M
3 A Alexander R
4 B Shute G


The formula =HLOOKUP(A2, Data!$A$1:$C$2,2)

This would return the value of 5%.

Looking at the above formula in detail:

=HLOOKUP means that is the function you need
A2 is the cell which is the reference in the sheet you are going to enter the new data
Data! is the name of the other sheet
$A$1:$C$2 is the range of the table (in this case a Horizontal table)
,2 means we want the values in the second row to be returned.

The same is true for VLOOKUP. The only difference is the table is arranged in Vertical format.

Calculating Gross Salaries

Remember to work this out you would:

add together the Basic Salary and any Bonus earned.

Plus you would also add any Commission that is available. (You may have to work this out!)

SumOf and CountOf

If you are asked to total (add) up something in Access, then in the appropriate query you select the icon that allows formulas (it looks the same as Autosum) and then you select SUM.

It is the same if you are asked to count the number of a certain thing (could be branches, number of sales made etc) then you use the query and then click on the Autosum and then COUNT.

Creating Labels in Access

Create mailing and other types of labels

1. 
   In the Database window, click Reports
2. 
   Click the New button on the Database window toolbar.
3. 
   In the New Report dialog box, click Label Wizard.
4. 
   Click the table or query that contains the data for the labels, and then click OK.
5. 
   Follow the directions in the wizard dialog boxes.
6. 
   If the resulting label report doesn't look the way you want, you can delete the report and run the Label Wizard again.

Reports in Access

Remember to locate the Query and use that as the basis for any report.

When it comes to grouping, make sure you add the fields in that order saving you to mess about with the report.

The secret is to read the question in full and highlight important information.

Reading is it seems underused these days. You can take in information and process it or else you are not Higher candidates. Read the question!!!

Make sure you know how to insert pictures or logos.

Make sure you know how to type in a relevant heading!!!

Queries in a Database

Remember a good tip is to add ALL the tables into the query and then you look for the fields required.

The Wildcard *

Remember the 2006 Exam example. In searching for G postcodes you would use G* in the Criteria box.

Query using OR

If you are looking for two items in the same field use OR. For example, if you wish to look for Branches in London and Birmingham, then typing in the Criteria box London OR Birmingham would return the correct results.

Query using AND

If you need items in different fields, then Access does it automatically for you.

Case Study Questions

For the Case Study focus revision on:

Types of Decisions:

• Strategic
• Tactical
• Operational

Types of Working Practices

• Flexitime
• Job-Share
• Part time
• Fixed Contracts etc

Benefits of Customer Service to the Firm

• increases market share (due to rising sales of satisfied customers)
• less customer complaints (they are satisfied)
• increase in customer loyalty (they will come back)
• good word of mouth increases sales
• less time spent over dealing with unhappy customers and more time spent on producing the products or providing the service
• good customer service may be a competitive advantage (think about why you fly a certain airline, or why you go to a certain supermarket or cinema... is it always just the cheapest you use?)

What laws affect workers?

• Health & Safety at Work Act (1974)
• RIDDOR (1995)
• HAS First Aid Regulations (1981)
• HAS Display Screen Regulations (1992)