Applied ict by English School Los Olivos

Applied ICT

PRACTICAL

AS 1

Section 8: software selection Objectives: Know the different software package and their uses.

Generic name

Brand

Text editors

eg notepad, wordpad.

Word processors

eg MS Word

Desktop Publisher

eg MS Publisher

Database package

eg MS Access

Spreadsheet package

eg MS Excel

Charting Package

eg MS Excel

Presentation authoring package Web Browser

eg MS Powerpoint

Graphics/Image editing package Integrated Packages:

eg Internet explorer, Google Chrome, Mozilla Firefox, Safari. eg MS Paint, Photoshop. MS Office, OpenOffice

Word processing and desktop publishing Word processing (WP) applications allow users to produce and edit text. Desktop publishing (DTP) applications allow users to create page layouts using text and pictures. This is what you need to know Standard features All word processing programs allow you to:     

enter and edit text save print cut/copy/paste - you can copy from one part of a document to another check your spelling

Text formatting Text formatting is important to make the text appear as you would like it to look. The normal features are:       

different fonts different text sizes different alignments (left, centre, right or justified) bold print underline italics bullet points

Mail-merge Mail merge is a word processing feature which allows users to 'personalise' letters with names and addresses from a database [database: A database is a collection of information which can be accessed and ordered quickly and easily.]. The four main steps in setting up a mail-merged letter are: 1. Create the database with fields for the names and addresses of the people to

send the letter to. 2. Write the letter using a word processing [word processing: An application

used for entering and manipulating text and images.] package and link the letter to the database. 3. Use a query to find a subset of relevant people and send a targeted letter to them. 4. Using the mail merge wizard, enter codes in the letter where the name and address of the customers should appear. 5. Merge-print, taking the data from the database and inserting it in the letters, producing one letter for each person in the subset of relevant people from the database. 3

Other features Other features that may be expected include find and replace, which replaces one word with another, and the ability to import graphics e.g. from a clip art [clip art : Pre-prepared images which can be used to illustrate a word processing or desk top publishing document. ] library. Headers and footers and page numbering are very useful. Desktop publishing A word processor is not always the best package to use to produce a document. If more control over the layout of a page is required or the document is to include a lot of graphics, then a desktop publishing package such as Microsoft Publisher would be more suitable. Desktop publishers are often used to produce newspapers, magazines and leaflets. Frames The main advantage of desktop publishing is that it is frame based [frame based : A feature of desk top publishing where text and picture frames can be laid out on the page and moved and resized if necessary. ]. Text and picture frames can be laid out on the page, and moved and resized if necessary. Images can be imported from clip-art libraries and other software. The view of the page is WYSIWYG [WYSIWYG: What You See Is What You Get normally used to describe software applications that are user friendly and intuitive to use.](What You See Is What You Get).

Graphics Software The range of pictures, drawings and images which can be produced by a computer are called graphics [graphics:The range of pictures, drawings and images which can be produced by a computer.]. The main types of graphics packages are painting, drawing and cad [CAD: Computer Aided Design - a system which helps the user produce accurate drawings.].

Drawing packages Images produced with drawing packages are made up of lines, shapes and coordinates. Drawing packages are also known as vector drawing [vector drawing: Images produced with a drawing package made up of lines, shapes and coordinates.] packages. Generally a drawing package offers many of the features of a painting package but particular features might be:      

line and shape tools select, group and resize objects rotate and flip options zoom in/out grid and snap to grid cut, copy and paste 4

 

save, print and edit a palette of colours

CAD Computer Aided Design packages are more sophisticated drawing packages. They are used by engineers, architects and designers to produce detailed design plans and technical drawings. CAD software offers all the features of standard drawing software but in addition may also offer:      

greater accuracy (it is possible to work to within greater accuracy (it is possible to work to within 1/72 of an inch) objects and drawings can be created in 2D, 3D, 2D CAD and 3D CAD images can be viewed from any angle powerful scaling, rotation and reflection options libraries of engineering components which can be imported links to packages to calculate costs

Multimedia Multimedia is the use of several types of outputs from a computer - or other device in order to give the user a 'richer' and more interesting experience. Listening to a CD with earphones is not multimedia because you are only experiencing one media sound. 'Video' is a good example in that it uses colour, onscreen motion and sound. What is multimedia? The main ways that the computer can feed the user's senses is through the use of:     

colour motion of areas of the screen as appropriate a range of standard shape tools such as rectangles and circles sound motion through the use of motors

Games on computers first started out without any sound at all, and many people thought that the inclusion of sound to a game would not make much difference. In fact it was soon found that adding even simple sounds enhanced the overall experience of the computer user. The use of multimedia [multimedia: The combined use of different media - text, images, sound, animation and movies.] also allows the possibility of user input to make selections and choices. This is called interactive multimedia, because the user interacts with the material in some way. Examples of multimedia

If you send a CD of digital photos of your party to a friend, and you add some sound clips, you are preparing a multimedia presentation because you are using more than one type of media. Video is a good example of multimedia [multimedia: The combined use of different media text, images, sound, animation and movies.] in that it uses colour, onscreen motion and sound. However on a computer it can also have the user interact with the material e.g.:     

stop the display go to various places on the video answer questions that are posed select from various options that are presented by the interactive package an educational type of package can ask for help or extra information

A virtual reality application - e.g. a cockpit simulator that's used to train pilots on the ground - is an example of multimedia. In a virtual reality (virtual reality: A simulated environment that represents the real or imaginary world and allows the user to immerse themselves as if they are in that world). Simulator the pilot, though not actually in the plane, feels as though he/she is. A wide range of multimedia programmes are available on other formats, for example, educational training packages to learn a foreign language or a CD ROM/DVD encyclopaedia. Multimedia and the Internet The Internet is a massive source of multimedia [multimedia: The combined use of different media - text, images, sound, animation and movies.] content. By this we mean that we can access various sorts of media. These include:  

 

sound - e.g. MP3 files and similar Still digital [digital: Digital representations consist of values measured at discrete intervals. Digital watches are called digital because they go from one value to the next without displaying all intermediate values. ] pictures moving digital pictures or "streaming video" such as DVD downloads to play on the computer Multimedia sites that combine text, graphics and sound to display on your computer such as revision websites with video and audio clips, games and written tests.

What you need for multimedia In order for a computer to cope with multimedia [multimedia: The combined use of different media - text, images, sound, animation and movies. ], it needs to fulfil certain requirements: 

It needs to be powerful enough to display the presentation e.g. a palmtop needs a powerful enough processor [processor: The processor is an area in a computer which controls the running of the computer and carries out instructions.] to display pictures on its screen and be able to play good quality 6

 



sound. A pc needs a reasonable graphics [graphics: The range of pictures, drawings and images which can be produced by a computer.] (video) card. A pc needs a lot of memory - both RAM [RAM: Random Access Memory - a fast temporary type of memory in which programs and data are stored whilst the computer is switched on] and hard drive[hard drive: A magnetic disc fixed inside a computer.] space. A method of producing reasonable sound (sound card plus speakers on a pc) is needed. An output interface [interface: The means of communication and interaction between the hardware, software and the user.] is needed if motion is to be used e.g. force feedback motion on joysticks to make them shake in games. Appropriate software (software: Instructions for a computer. Software tells the hardware what to do)to run the presentation is needed e.g. software that will display the video and sound files properly.

Accessing internet multimedia In order to use the Internet to gain access to multimedia [multimedia: The combined use of different media - text, images, sound, animation and movies. ] files the computer to be used has to be:   

fast enough have enough memory space available have a connection to the Internet [Internet : A global network connecting millions of computers. ] that is fast enough to be useable

If the connection is slow, the files may need to be downloaded onto the computer directly and then played at a later date. If the connection is fast enough however, e.g. a broadband [broadband: A fast digital connection which is 'always on'.] connection, then the files can be run directly as they are received. This is known as 'streaming media'. Interfaces In order that a computer can deliver a multimedia [multimedia: The combined use of different media - text, images, sound, animation and movies. ] presentation, it needs to be able to output its sound, video, motion etc to external devices such as speakers, monitors, joysticks etc. To do this a hardware [hardware: Machinery and equipment such as the monitor, the mouse, the processor, etc.] interface is used. This piece of equipment is different for each output, but each of them converts the signals/commands from the computer into signals/commands that the outside hardware (speakers, monitors etc) can understand. In this way the software can send signals to lots of different devices, and those devices do as they are instructed. In this way the computer and software can present lots of outputs for the user to watch, listen and feel.

The Hardware section gives examples of input and output devices.

Database A database is a collection of data that can be used as information. It becomes information because of the headings and structure used in the database file. For example, the field heading "Surname" gives meaning to a list of words such as "Jones", "Patel", "Smith" and so on which appears under it. There are different types of databases and these can be searched in different ways. There are many facilities offered by search engines on the World Wide Web which allow you to search databases. Find out more in the section on searching. Field-and-record databases These databases have a strong formal structure that is normally easy to see. Take the example below:

The column headings in RED are fields. The rows in BLUE are records, with one record per person. They're useful for storing data that can easily fit into a rigid structure, like a class list or an address book.

Exercise http://www.davidroyall.com/wp/fonts/index.htm

Section 9: Communication Objectives: use e-mail, send, receive, attach files, subject line, cc & bcc, compressed files, using the Internet, searching the Internet, evaluating Internet sources.

Activity 1 - Send an e-mail Send an e-mail to your teacher (paulflovel@live.com) with the subject line ‘e-mail’. Copy this to two other people in your class, informing them that you can now communicate with them via e-mail. Your teacher will send you a reply.

Activity 2 - Receive and reply to an e-mail with an attachment

Read the e-mail reply from your teacher. Reply to this e-mail, sending them the document that they have requested. The document must be complete and ready to send before you start to attach it.

Activity 3 - Send a compressed file as an e-mail attachment

Use the same document that you sent in Activity 9.2. Send it in a compressed format to your teacher and two other students in your class. Your teacher will tell you who these should be. Open the compressed files sent to you by the two people in your class. These should each contain a document on e-mail etiquette. Save these documents in your user area (using different filenames) in both compressed and uncompressed formats. Activity 4 - Advanced Internet-search techniques Search the Internet for information on ‘learn to scuba dive’. This information must be suitable for a complete beginner with little knowledge of the sport.

Activity 5 - Evaluate information downloaded from the Internet

Search the Internet for information on ‘healthy eating’. Th e information will be used to teach a class of children aged ten or eleven. Produce a document containing at least four different pieces of the information found. For each item of information, identify: ·

■ the source of the information (including the URL)

■ whether it is fact or opinion

■ whether it is biased

■ how reliable the source is

■ how accurate the source is

■ how current the material is

· ■ whether it is suitable (including readability) for your audience. Evaluate www.martinlutherking.org

Criteria

Review

Is the author or web source a reliable source? Is the source current or out-ofdate for your purpose?

Is the target audience appropriate. i.e. is it aimed at teachers or students?

Is the information covered fact or opinion?

Does the resource update other 10

sources or add new information?

Are the main points clearly presented?

Do you find the text easy to read?

Are any diagrams clearly presented? Are there any audio visual aids?

Is the information detailed enough to answer any likely exam questions (use the 2008 June papers as guidance)?

Section 10: Document and presentation production Remember there are 4 types of text files; .txt, .rtf, .doc/x, .csv. All can be viewed and edited in word but only a doc/x file is a word document. The other file types may need to be imported. Insert text from file. Objectives:         

Use special characters – symbols, superscript, subscript, auto-text, autodate/time. Import images/charts/tables/media files. (embedding) Import and manipulate an image – text wrap, precision framing, aspect ratio, cropping, compress pictures. Hyperlinks – to web addresses, email, place in existing documents. Document control – protect, editing, track changes, comments, master slide. Page layout – set up, orientation, gutter, margins, columns, styles, tabs, headers and footers, footnotes. House styles (corporate) Widows and orphans Proof-reading and error-correction.

Corporate house style project Create an advert, web page and movie (presentation) for a new product, service or business, using a corporate house style. This should include a constant font size, style, colour, that help create a consistent look to your graphics.. This can be seen in the logo and name of a company or institution used on envelopes, folders, brochures, business cards, advertising, websites, company vehicles and, of course, their products. This identity makes the organization easily recognizable and strengthens its reputation. In the ICT terms you should always adopt a unique style for the work that you produce. The main purpose of house styles is that they ensure a consistent, professional look in documents and publications. They provide guidance in a usable form for staff and reviewers. They provide a core set of guidelines that apply, for all audiences.. Why is a house style that important? 1. Marketing strategies emphasizes the quality of your offering and products and your business attitude. A sloppy presentation can lead to a loss of a business deal 2. A house style needs to be appreciated within your line of business and has to reflect the latest design and business trends. The colors have to match, the design must be original. 3. The house style must reflect your branding strategy and the values of the brand. 4. it increases the companiesÂ´ publicity. examples:

DPP exercises.

Task 1

Enter and Edit Text 1

Open the file ENTER.TXT into Word 2007 and replace the text [table goes here] with the table created from the file CLIENTS.CSV

ii.

All the chemical formulae mol-kg-1 should have the -1 superscripted

iii.

The chemical formula SO4 should have the 4 subscripted

iv.

Move the text Tasks to Perform: so that it becomes the top line of the document

Replace the text [place French phrase here] with Activités de proximité’ m aking sure the accents are placed properly.

vi.

Replace the word Copyright with © which is a proper copyright symbol

Task 2

Import an image and other objects from external sources 2

Open a new word processed document file and import and place within this document an image file, a sound file, a short video clip, a table, part of a small spreadsheet and a graph or chart

ii.

Repeat this exercise with a Desk Top Publishing Package

Task 3

Place and manipulate an image in Word Open the document IMAGE.RTF and place the image BOARDER.JPG into the document so that it is aligned with the top right of the text. Crop the image along the dotted line. Resize the image so that it fills about a quarter of the page.

Save this document as a word document.

Insert your name in the header to the left and an automated filename in the footer. Place and automated Date and Time in the header to the right.

Task 4

Manipulate an image Using a Desk Top Publishing application, open the document IMAGE.RTF and place the image BOARDER.JPG into the document so that it is aligned with the top left of the text. Crop the image along the red line.

Resize the image so that it fills about a quarter of the page margins.

Place the image SLOGAN.JPG at the bottom of the document so that it is resized to fill the full width of the text area. Crop this so that only the top two lines of the slogan are visible and this sits below the text.

Save this document as a word document.

Insert your name in the header to the left and an automated filename in the footer. Place and automated Date and Time in the header to the right.

Task 5

Evaluate information downloaded from the Internet Search the Internet for information on sharks. Your audience will be your class; you can choose the method of delivery, but it must be appropriate to the audience.

Using the information that you have found, insert extracts of the information and annotate them with comments on their sources(including the URLs):

    

are they fact or opinion; are they biased; how reliable is the source; how accurate is the source; how current is the material; is it suitable to your audience

Ensure that all images used within your delivery are suitably placed, resized and cropped if necessary.

Task 6

Create hyperlinks in a document Open the document SHARKS.RTF.

Use the text nurse shark to create a hyperlink to the file NURSE.RTF.

Add a little more information about sharks and the sources you have used to the end of the document and create three more hyperlinks to web pages containing the original information. You may use the information that you gathered in task 5 to help you.

Create a new hyperlink from the word hyperlink that will allow the user to send you an e-mail about sharks.

Save this document as a Word document for later use.

Task 7

Create a multi-path presentation Create a new presentation which contains hyperlinks, including at least two different hyperlink options on the front page.

Use these options to run different paths through the presentation.

Make sure that at least one other slide is common to both paths, and each path has at least one slide that cannot be seen unless that path is selected.

Task 8

Set up the page layout in a word-processing document Set up a new word document with an A4 page size and landscape orientation.

Set the top margin to 5 centimetres, the bottom margin to 3 centimetres and the left and right margins to 2 centimetres.

Set a gutter of 2 centimetres, to the right on the even pages and to the left on the odd pages.

Set the document into three columns with 1 centimetre space between the columns.

Task 9

Set up a word-processed document Create a new document in word to A4 size, landscape orientation and set into a booklet fold. Set the top, bottom, left and right margins to 2 centimetres and the gutter to 2 centimetres, placed on the inside page. The booklet can be set up with four pages. Set the document into a single column. Import the file EVALUATE.RTF into your document and apply the page settings to this document. Now define and apply these styles to the document, so that it has a: (i) body text style with:    

a size of 12 points single line spacing a sans-serif font fully justifi ed text.

(ii) heading style with:

      

a size of 16 points single line spacing a serif font right aligned text spacing before of 6 points spacing aft er of 2 points bold and italic.

Task 10

Set up the page layout in a desk top publishing package Create a new document in word to A4 size, landscape orientation. Set the top margin to 5 centimetres, the bottom margin to 3 centimetres and the left and right margins to 2 centimetres. Set a gutter of 2 centimetres, to the right on the even pages and to the left on the odd pages. Set the document into a three columns with a 1 centimetre space between the columns.

Task 11

Set up the page layout in a desk top publishing package Create a new document in word to A4 size and a portrait orientation. Set the top margin to 6 centimetres and the bottom, left and right margins to 3 centimetres. Set a gutter of 3 centimetres, to the right on the even pages and to the left on the odd pages. Set the document into 2 columns with a 1 centimetre space between the columns. Import the file SIMC.RTF. Set up a master document to be used by the South India Motor Company as follows: It should have the company name in a 22-point bold, sans-serif font across the top of the page. The top of this text should be exactly 3 centimetres from the top of the page. The company name must not extend beyond the left and right margins. Use the SILOGO.JPG file to place a small logo, I centimetre high, in the centre of the page. The top of this image should be exactly 4.5 centimetres from the top of page.

It should look something like this:

Task 12

Set up the slide layout Create a new presentation to be delivered on-screen using a multimedia projector. The presentation will have presenter notes and audience notes, both to be printed on A4 paper with a portrait orientation. The master slide should have ‘South India Motor Company’ in the top left corner, an automated slide number in the top right corner and a small logo containing the image in SILOGO.JPG from the file in the centre at the bottom of the slide. Use the presentation settings that you just saved to set up the presentation with a pale green background, dark green titles and dark blue body text.

Task 13

Set up a document with split page orientation, a section break, headers and footers Create a page size of A4 and landscape orientation. Set all of the page margins to 3 centimetres, but with no gutter. Import the document SIMC.RTF from the CD. Defi ne the styles for the document so that it has a: (i) body text style with:      

a size of 14 points single line spacing a serif font fully justifi ed text no spacing before the text 14 point spacing aft er the text.

(ii) heading style with:  a size of 18 points  single line spacing  a sans-serif font  centre aligned text  no spacing before or aft er the text. Remove any extra carriage returns between paragraphs. Insert the title ‘South India Motor

Company’ in heading style immediately before ‘Production’. Insert a break immediately before ‘Production’ and format all of the text from this point onwards into three columns with a 2 centimetre gap between the columns. Change the page orientation to portrait immediately before the heading ‘Needs’. Format all of the text from this point onwards into two columns with a 2-centimetre gap between the columns. Set a header that contains automated page numbering left aligned, your name centre aligned and the date right aligned to the page margins. Set a footer that contains the fi lename and path centre aligned to the page margins.

Task 14

Create and edit tables In each of your soft ware packages, create a 5-by-6 table like this:

Make sure that all the text fi ts within the table without text wrap. Delete the ‘Hours’ column and the row containing ‘Payroll’. Make the column headings and row headings bold. Include gridlines within the table like this:

Databases and Charts Objectives: Design a database structure: fields, records, files, tables, field size, data types.  Create database structure: relationships, input mask, validation rules, primary keys.  Create database from existing files: import text files, csv, rtf, txt  Joining tables: relationships One to one, one to many.  Checking data entry: validation – range check, look-up check, format check, length check, type check, verification-double entry, visual.  Performing Searches: queries, criteria: like * ? , parameters, >,< ,>=, <=  Sorting Data: ascending/descending  Perform calculations: Numeric Data, calculated field, calculated control.  Output selected data: Report/Page headers & footers, details, labels. Reports – wizards, design, grouping data, crosstab queries, summaries. Duplicate entries.  Exporting Data into different packages: Excel, word.  Producing Graphs/Charts: pie, bar, line, comparative, adding axis, titles, labels, adding data, second series. Data is stored in files made up of records of fields. Database structure: records are stored in tables. The table is made up of fields of data, which consists of data types. Type Text

Memo

Number

Description Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.

notes Field size can be changed to save storage space.

Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered. Use for lengthy text and numbers, such as notes or descriptions. Stores up to 65,536 character Use for data to be included in mathematical calculations, except calculations involving money (use Currency type).

Can be integer, long integer, single or doubles.

Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type.

Date/Time

Use for dates and times.

Format can be short, medium, long.

Stores 8 bytes Currency

Use for currency values and to prevent rounding off during calculations.

Stores 8 bytes. AutoNumber Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added.

Yes/No

Stores 4 bytes; stores 16 bytes for Replication ID (GUID). Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed.

0 is no and -1 is yes.

Stores 1 bit OLE Object

Hyperlink

Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. Stores up to 1 gigabyte (limited by disk space). Use for hyperlinks. A hyperlink can be a UNC path or a URL.

Stores up to 64,000 characters

Testing data: we test for normal, extreme and abnormal data. Examples Type Normal Extreme Abnormal

test Should accept a whole number If age, then 0 or 100 Only enter yes/no .. fish!

Primary keys- to join tables together to create a relational database requires the use of primary keys and foreign fields. They are unique values with no duplicates. Primary keys are unique reference numbers that do not repeat in the table eg PL01. A foreign key will contain the same information in a different table and can be repeated. 22

Tables are joined using the primary key to foreign field. Relationships: one-to-many

Relationships: one-to-one

Validation worksheet

Validation: rules set up in the design of the database to reduce errors.

Input Masks

Importing files into a database .csv or txt files can be imported and formatted into a table. .csv files are delimited by commas, txt files by spaces or tabs. Remember: the data should be stored in columns with the field names as the first row. Date fields should be in the form dd/mm/yy Boolean fields (yes/no) may need to be formatted <advanced> tab during the import.

Searching Access provides two primary ways to create select queries â&#x20AC;&#x201D; the Query Wizard and the Query Designer. Regardless of the tool you use, you follow some common steps when you create a select query: Start by choosing a record source for the query. A record source can be one or more tables, one or more queries, or a combination of the two. The picture shows a table open in the Query Designer. From the record source, select the fields that you want to see in the query. The picture shows fields in the Query Designer, but you do the same thing in the Query Wizard, and you'll use both tools in the practice session. Add any sorting, filtering, or other selection criteria to your queries. For example, if you use the criteria shown in the picture, the query will only return data for assets purchased after May First of 2010. You can also use criteria that make a query ask you for input before it runs, and you'll see that later in this course. After you finish adding fields and any selection criteria, run your query to see if it gives you the correct results. You can refine your query by adding or removing fields, or by changing your selection criteria until you have just the data you need, and then you can put the query to work. That's next. The Query Designer gives you the most control over a select query. It also makes it easy to create a query that uses a single table as a recordsource. For example, this type of query makes it easy to list your assets and their purchase dates. On the Create tab, in the Other group, click Query Design. The designer starts and displays the Show Table dialog box. In the dialog box, select your record source. You can use a combination of tables and queries, and the ones you select appear in the upper section of the designer. In your record source, double-click the fields that you want to see in your recordset, or results. Your choices appear in the bottom section of the designer. When you finish adding fields, go to the Design tab on the Ribbon, and in the Results group, click Run.

Your record set appears as a datasheet. You'll have a chance to use the designer in the practice at the end of this course.

Sorting Data: ascending/descending order

Calculations Use functions and calculated fields in your queries So, let's say you need to calculate how much you've spent on freight for the past month. If your data resides in a single field, you can do that job by using one of the built-in math functions in a totals query. Don't let the name fool you. A totals query can also average, count, and perform other math operations on your data. Open your query in Design View, and on the Design tab, in the Show/Hide group, click Totals. The Total row appears in the query design grid. Click the Total row in the field you want to calculate, and select the function you want to use. The one in the picture sums a column of numbers. Those built-in functions are also called aggregate functions, because they calculate a single value from a group of values. You can also use the aggregate functions in Datasheet view, but if you do, someone can turn them off or hide them. That's less likely to happen if you use a totals query. Also, a totals query can group data, something you can't do with the aggregate functions in a datasheet.

The aggregate functions have a couple limitations â&#x20AC;&#x201D; they only work on a single field, and you're limited to the functions that Access provides. So what if you need something different? For example, what if you need to raise prices by 10 percent? You can use a calculated field. A calculated field displays the results of an expression instead of the data in your tables. 31

Because they use expressions, calculated fields are pretty versatile. For example, they can combine first and last names, manipulate dates and times, or perform complex math operations. Let's look at the syntax of a calculated field. With your query open in Design View, select a blank field in the Design Grid and enter a name, like you would for any field in your database. Put a colon at the end of the name. Enter your equation or expression after the colon. The picture shows a typical example. The calculated field multiplies the values in the UnitPrice field by 1.1, which increases the value by 10 percent, and then displays the result as a calculated field called "NewPrice." That calculated field doesn't exist in your database tables. It only appears when you run your query.

Reports: wizards, labels, grouped data. What can you do with a report? A report is a database object that comes in handy when you want to present the information in your database for any of the following uses: Display or distribute a summary of data. Archive snapshots of the data. Provide details about individual records. Create labels.

Parts of a report While it is possible to create “unbound” reports that do not display data, but for the purposes of this article, we’ll assume that a report is bound to a data source such as a table or query. The design of a report is divided into sections that you can view in the Design view. Understanding how each section works can helps you create better reports. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses: SECTION

HOW THE SECTION IS DISPLAYED WHEN PRINTED

WHERE THE SECTION CAN BE USED

Report Header

At the beginning of the report.

Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report

header, the sum calculated is for the entire report. The report header is printed before the page header. Page Header

At the top of every page.

Use a page header to repeat the report title on every page.

Group Header

At the beginning of each new group of records.

Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group. You can have multiple group header sections on a report, depending on how many grouping levels you have added. For more information about creating group headers and footers, see the section Add grouping, sorting, or totals.

Detail

Appears once for every row in the record source.

This is where you place the controls that make up the main body of the report.

Group Footer

At the end of each group of records.

Use a group footer to print summary information for a group. You can have multiple group footer sections on a report, depending on how many grouping levels you have added.

Page Footer

At the end of every page.

Use a page footer to print page numbers or per-page information.

Report Footer

At the end of the report. NOTE In Design view, the report footer appears below the page footer. However, in all other views (Layout view, for example, or when the report is printed or previewed), the report footer appears above the page footer, just after the last group footer or detail line on the final page.

Use the report footer to print report totals or other summary information for the entire report.

Create a quick grouped or sorted report Even if youâ&#x20AC;&#x2122;re new to grouped reports, you can quickly create a simple one by using the following procedure: 1. In the Navigation Pane, select a table or query that contains the records you want on your report. 2. On the Create tab, click Report. Access creates a simple tabular report and then displays it in Layout View. If there are many fields in the report, it will probably extend across more than one page. Before applying any grouping or sorting, you might want to resize columns (and delete unwanted columns) so that the report fits on one page width. To delete a column, rightclick it and then click Delete Column. 3. Right click a column on which you want to group or sort, and then click Group On[field name] or click one of the Sort options. For example, to group on the Priority column, right-click the Priority column and then click Group On Priority. When applying grouping, Access moves the grouping field to the leftmost column, and groups the remaining columns based on that column. In some cases, Access also adds a grand total to the Report Footer section. 4. Optionally, view and fine-tune your grouping and sorting options by following the procedures in the section, Add or modify grouping and sorting in an existing report. Build a new grouped report by using the Report Wizard The Report Wizard presents you with a series of questions, and then generates a report based on your answers. Among those questions is one that asks for the field or fields to use to group your report. After the report is created, you can use it as-is or modify it to better suit your needs. Before you begin with the Report Wizard, you need to decide upon a data source. Start the Report Wizard 1. On the Create tab, in the Reports group, click Report Wizard. Access starts the Report Wizard. 2. Click the Tables/Queries drop-down list and choose the table or query that contains the fields you want on your report. 3. Double-click fields in the Available Fields list to choose them. Access moves them to the Selected Fields list. Alternatively, you can click the 34

buttons located between the Available Fields box and the Selected Fields box to add or remove the selected field or to add all or remove all of the fields. 4. If there are fields in another table or query that you also want to put on your report, click the Tables/Queries drop-down list again and choose the other table or query, and continue to add fields. 5. After you've finished adding fields, click Next. Group records in the Report Wizard Grouping lets you organize and arrange records by group, such as by Region or Sales Person. Groups can be nested so that you can easily identify the relationships among the groups and find the information you want quickly. You can also use grouping to calculate summary information, such as totals and percentages. When you include more than one table in a report, the wizard examines the relationships between the tables and determines how you might want to view the information. 1. On the page of the Report Wizard that asks Do you want to add any grouping levels?, click one of the field names in the list, and then click Next. 2. To add grouping levels, double-click any of the field names in the list to add them to your report. You can also remove a grouping level by double-clicking it in the page display on the right side of the dialog box. Use the arrow buttons to add and remove grouping levels, and adjust the priority of a grouping level by selecting it and clicking the up or down priority buttons. Access adds each grouping level and shows it nested within its parent grouping level.

3. Click Grouping Options to display the Grouping Intervals dialog box.

4. Optionally, for each group-level field, choose a grouping interval. The grouping interval lets you customize how records are grouped. In the previous illustration, records are grouped on the Shipped Date field, which is a Date/Time data type. The Report Wizard offers choices appropriate to the field type in the Grouping intervals list. Thus, because Shipped Date is a Date/Time type, you can choose to group by actual value. , Year, Quarter, Month, Week, Day, Hour and Minute. If the field were a Text data type, you could choose to group by the entire field (Normal), or perhaps by the first one to five characters. For a numeric data type, you can choose to group by value (Normal), or by range in selected increments. After selecting a grouping interval, click OK. 5. Click Next to navigate to the next page of the wizard. Sort and summarize records You can sort records by up to four fields, in either ascending or descending order. 1. Click the first drop-down list and choose a field on which to sort. You can click the button to the right of the list to toggle between ascending and descending order (Ascending is the default). Optionally, click the second, third, and fourth drop-down lists to choose additional sort fields.

2. Click Summary Options if you want to summarize any of the numeric fields. Note that the Summary Options button will only be visible if you have one or more numeric fields in the Detail section your report. The wizard displays the available numeric fields. 36

3. Select the check box under your choice of Sum, Avg, Min or Max to include those calculations in the group footer. You can also choose to show the details and summary or the summary only. In the latter case, totals for each ShippedDate value are shown (if you selected the check box for Sum, for example), but the order detail is omitted. You can also choose to show percent of total calculations for sums. 4. Click OK. 5. Follow the directions on the remaining pages of the Report Wizard. On the last page, you can edit the title of the report. This title will be displayed on the first page of the report, and Access will also save the report, using the title as the document name. You can edit both the title and the document name later. 6. Click Finish. Access automatically saves the report and displays it in Print Preview, which shows you the report as it will look when printed. You can use the navigation buttons at the bottom of the preview pane to view the pages of the report sequentially or jump to any page in the report. Click one of the navigation buttons or type the page number that you want to see in the page number box, and then press ENTER. In Print Preview, you can zoom in to see details or zoom out to see how well data is positioned on the page. With the mouse pointer positioned over the report, click once. To reverse the effect of the zoom, click again. You can also use the zoom control in the status bar. Add or modify grouping and sorting in an existing report If you have an existing report and you want to add sorting or grouping to it, or if you want to modify the report's existing sorting or grouping, this section helps you get started. Add grouping, sorting, and totals You can perform simple sorting, grouping and totaling operations by right-clicking fields in Layout view and then choosing the operation you want from the shortcut menu. To switch to Layout view, right-click the report in the Navigation Pane and then clickLayout view. On the Design tab, in the Grouping & Totals group, click Group & Sort. 37

Sort on a single field  

Right-click any value in the field on which you want to sort. On the shortcut menu, click the sort option you want. For example, to sort a text field in ascending order, click Sort A to Z. To sort a numeric field in descending order, clickSort Largest to Smallest. Access sorts the report as you specified. If the Group, Sort, and Total pane is open, you can see that a new Sort by line for the field has been added. Sort on multiple fields When you apply sorting by right-clicking a field in Layout view, you can only sort one field at a time. Applying sorting to another field removes the sorting on the first field. This differs from the sorting behavior in forms, where multiple sort orders can be established by right-clicking each field in turn and the choosing the sort order you want. Group on a field

 

Right-click any value in the field on which you want to group. On the shortcut menu, click Group On. Access adds the grouping level and creates a group header for it. If the Group, Sort, and Total pane is open, you can see that a new Group on line for the field is added. Add a total to a field This option lets you calculate a sum, average, count, or other aggregate for a field. A grand total is added to the end of the report, and group totals are added to any groups that exist on the report.

  

Right-click any value in the field that you want to total. Click Total. Click the operation you would like to perform: Sum, Average, Count Records (to count all records), Count Values (to count only the records with a value in this field),Max, Min, Standard Deviation, or Variance. Access adds a calculated text box control to the report footer, which creates a grand total. Also, if your report has any grouping levels, Access adds group footers (if not already present) and places the total in each footer. You can also add totals by clicking the field that you want totaled and then, on the Design tab, in the Grouping & Totals group, click Totals. Add grouping, sorting, and totals by using the Group, Sort, and Total pane

Working in the Group, Sort, and Total pane gives you the most flexibility when you want to add or modify groups, sort orders, or totals options on a report. Again, Layout view is the preferred view in which to work because it is much easier to see how your changes affect the display of the data. Display the Group, Sort, and Total pane ď&#x201A;ˇ

On the Design tab, in the Grouping & Totals group, click Group & Sort. Access displays the Group, Sort, and Total pane.

To add a new sorting or grouping level, click Add a group or Add a sort. A new line is added to the Group, Sort, and Total pane, and a list of available fields is displayed.

You can click one of these field names or you can click expression below the list of fields to enter an expression. Once you choose a field or enter an expression, Access adds the grouping level to the report. In Layout view, the display changes immediately to show the grouping or sort order. If there are already several sorting or grouping levels defined, you may need to scroll down in the Group, Sort, and Total pane before you can see the Add a group andAdd a sort buttons. ď&#x201A;ˇ

You can define up to 10 grouping and sorting levels in a report. Change grouping options Each sorting or grouping level has a number of options that can be set to obtain the results you want.



To display all the options for a grouping or sorting level, click More on the level that you want to change.



To hide the options, click Less. Sort order You can change the sort order by clicking the sort order drop-down list, then clicking the option you want. Group interval This setting determines how the records are grouped together. For example, you can group on the first character of a text field so that all that start with "A" are grouped together, all that start with "B" are grouped together, and so on. For a date field, you can group by day, week, month, quarter, or you can enter a custom interval. Totals To add totals, click this option. You can add totals on multiple fields, and you can do multiple types of totals on the same field.

    

Click the Total On drop-down arrow and select the field you want to have summarized. Click the Type drop-down arrow and select the type of calculation to perform. Select Show Grand Total to add a grand total to the end of the report (in the report footer). Select Show group totals as % of Grand Total to add a control to the group footer that calculates the percentage of the grand total for each group. Select Show in group header or Show in group footer to display the total in the desired location. 40

Once all the options have been chosen for a field, you can repeat the process and summarize another field by selecting the other field from the Total On drop-down list. Otherwise, click outside the Totals pop-up window to close it. Title This allows you to change the title of the field being summarized. This is used for the column heading and for labeling summary fields in headers and footers. To add or modify the title: 

Click the blue text following with title. The Zoom dialog box appears.



Type the new title in the dialog box, and then click OK. With/without a header section Use this setting to add or remove the header section that precedes each group. When adding a header section, Access moves the grouping field to the header for you. When you remove a header section that contains controls other than the grouping field, Access asks for confirmation to delete the controls. With/without a footer section Use this setting to add or remove the footer section that follows each group. When you remove a footer section that contains controls, Access asks for confirmation to delete the controls. Keep group together This setting determines how groups are laid out on the page when the report is printed. You may want to keep groups together as much as possible to reduce the amount of page turning that is needed to see the entire group. However, this usually increases the amount of paper needed to print the report, because most pages will have some blank space at the bottom.



Do not keep group together on one page Use this option if you are not concerned about groups being broken up by page breaks. For example, a group of 30 items may have 10 items on the bottom of one page and the remaining 20 items at the top of the next page. Keep whole group together on one page This option helps minimize the number of page breaks in a group. If a group cannot fit in the remaining space on a page, Access leaves that space blank and begins the group on the next page instead. Large groups may still span multiple pages, but this option minimizes the number of page breaks within the group as much as possible. Keep header and first record together on one page For groups with group headers, this ensures that the group header will not print by itself at the bottom of a page. If Access determines that there is not enough room for at least one row of data to be printed after the header, the group begins on the following page. Change the priority of grouping and sorting levels

To change the priority of a grouping or sorting level, click the row in the Group, Sort, and Total pane and then click the up arrow or the down arrow on the right side of the row. Delete grouping and sorting levels To delete a grouping or sorting level, click the row you want to delete in the Group, Sort, and Total pane, and then press DELETE or click the Delete button on the right side of the row. When you delete a grouping level, if the grouping field was in the group header or footer, Access moves it to the report's Detail section. Any other controls that were in the group header or group footer are deleted. Create a summary report (without record details) If you want to show only totals (that is, just the information in header and footer rows), on the Design tab, in the Grouping & Totals group, click Hide Details. Doing this hides the records at the next lower level of grouping, resulting in a much more compact presentation of the summary data. Although the records are hidden, the controls in the hidden section are not deleted. Click Hide Details again to restore the Detail rows to the report. Crosstab Queries A crosstab query is a type of select query. When you run a crosstab query, the results display in a datasheet that has a different structure from other types of datasheets. The structure of a crosstab query can make it easier to read than a simple select query that displays the same data, as shown in the following illustration.

This select query groups summary data vertically by employee and category. A crosstab query can display the same data, but groups the data both horizontally and vertically so that the datasheet can be more compact and easier to read.

Creating crosstab queries When you create a crosstab query, you specify which fields contain row headings, which field contains column headings, and which field contains values to summarize. You can use only one field each when you specify column headings and values to summarize. You can use as many as three fields when you specify row headings. TIP You can also use an expression to produce row headings, column headings, or values to summarize.

One, two, or three columns on this side contain row headings. The names of the fields that you use as row headings appear in the top row of these columns. The row headings appear here. The number of rows in the crosstab datasheet can grow quickly when you use more than one row heading field, because each combination of row headings is displayed. The columns on this side contain column headings and summary values. Note that the name of the column heading field does not appear on the datasheet. Summary values appear here.

Methods for creating your crosstab query Using the Crosstab Query Wizard The Crosstab Query Wizard is usually the fastest and easiest way to create a crosstab query. It does most of the work for you, but there are a few options that the wizard does not offer. The wizard has these benefits: ď&#x201A;ˇ ď&#x201A;ˇ

It is easy to use. To use it, you start the wizard, and then answer a series of guided questions. It can automatically group dates into intervals. If you use a field that contains date/time data for column headings, the wizard also helps you group the dates into intervals, such as months or quarters. TIP If you want to use values from a Date/Time field for column headings, but want to group the dates into intervals that the wizard does not provide, such as fiscal year

or biennium, do not use the wizard to create your query. Instead, create the crosstab query in Design view, and use an expression to create the intervals. 

It can be used as a starting point. You can use the wizard to create the basic crosstab query that you want, and then fine-tune the query's design by using Design view. However, by using the wizard, you cannot:

   

Use more than one table or query as a record source. Use an expression to create fields. Add a parameter prompt. Specify a list of fixed values to use as column headings. NOTE At the last step of the wizard, you can choose to modify the query in Design view. This allows you to add query design elements that the wizard does not support, such as additional record sources. Working in Design view Design view allows you more control over your query design. It supports the features that are not available in the wizard. Consider using Design view to create your crosstab query if you want to:

     

Have more control over the process. The wizard makes some decisions for you. Use more than one table or query as a record source. Add a parameter prompt to your query. Use expressions as fields in your query. Specify a list of fixed values to use as column headings. Practice using the design grid. Exporting Data

Export Access data to Word 1. Open the source database. 2. In the Navigation Pane, select the object that contains the data you want to export. You can export a table, query, form, or report. 3. Review the source data to ensure that it does not contain error indicators (green triangles) or error values, such as #Num. Any unresolved value error in the data source, is replaced with a null value in the Word document. 4. If you want to export only some of the data from an object, select just the records you want to export. 5. On the External Data tab, in the Export group, click More, and then click Word. 44

The Export commands are available only when a database is open and an object is selected. 6. In the Export Wizard, specify the name of the destination file. 7. The wizard always exports formatted data. If you want to view the Word document after the export operation is complete, select the Open the destination file after the export operation is complete check box. 8. If you selected the records that you want to export before you started the export operation, you can select the Export only the selected records check box. However, if you want to export all the records in the view, leave the check box cleared. This check box appears unavailable (dimmed) if no records are selected. 9. Click OK. 10. If the destination document exists, you are prompted to click Yes to overwrite the file. Click No to change the name of the destination file, and then click OK again. Access exports the data and opens the destination document in Word, depending on the export options that you specified in the wizard. Access also displays the status of the operation on the final page of the wizard. Go to the next steps if you want to save your import settings for reuse later.

Export data to Excel You can copy data from a Microsoft Office Access 2007 database into a worksheet by exporting a database object to a Microsoft Office Excel 2007 workbook. You do this by using the Export Wizard in Office Access 2007. 1. Open the source database. 2. In the Navigation Pane, select the object that contains the data that you want to export. You can export a table, a query, a report, or a form. 3. Review the source data to make sure that it does not contain any error indicators or error values. If there are any errors, you must resolve them before you export the data to Excel. Otherwise, errors can occur during the export operation, and null values might be inserted into fields. 4. If the source object is a table or a query, decide whether you want to export the data with or without its formatting.

This decision affects two aspects of the resulting workbook â&#x20AC;&#x201D; the amount of data that is exported and the display format of the data. The following table describes EXPORT SOURCE OBJECT

FIELDS AND RECORDS

FORMATTING

Without formatting

Table or query NOTE Forms and reports cannot be exported without their formatting.

All fields and records in the underlying object are exported.

The Format property settings are ignored during the operation. For lookup fields, only the lookup ID values are exported. For hyperlink fields, the contents are exported as a text column that displays the links in the formatdisplaytext#address#.

With formatting

Table, query, form, or report

Only fields and records that are displayed in the current view or object are exported. Filtered records, hidden columns in a datasheet, and fields not displayed on a form or report are not exported.

The wizard respects theFormat property settings. For lookup fields, the lookup values are exported. For hyperlink fields, the values are exported as hyperlinks. For rich text fields, the text is exported but the formatting is not.

the outcome of exporting formatted and unformatted data. 5. Choose the destination workbook and file format. During the export operation, Access prompts you to specify the name of the destination workbook. The following table summarizes when a workbook is created (if it does not already exist) and when IF THE DESTINATION WORKBOOK

AND THE SOURCE OBJECT IS

AND YOU WANT TO EXPORT

THEN

Does not exist

A table, query, form, or report

The data, with or without the formatting

The workbook is created during the export operation.

Already exists

A table or query

The data, but not the formatting

The workbook is not overwritten. A new worksheet is added to the workbook, and is given the name of the object from which the data is being exported. If a worksheet having that name already exists in the workbook, Access prompts you to either replace the contents of the corresponding worksheet or specify another name for the new sheet.

Already exists

A table, query, form, or report

The data, including the formatting

The workbook is overwritten by the exported data. All existing worksheets are removed, and a new worksheet having the same name as the exported object is created. The data in the Excel worksheet inherits the format settings of the source object.

it is overwritten (if it does already exist).

The data is always added in a new worksheet. You cannot append the data to any existing worksheet or named range. 46

Run the export operation 1. If the destination Excel workbook is open, close it before you continue. 2. In the Navigation Pane of the source database, select the object that you want to export. 3. On the External Data tab, in the Export group, click Excel. 4. In the Export - Excel Spreadsheet dialog box, review the suggested file name for the Excel workbook (Access uses the name of the source object). If you want, you can modify the file name. 5. In the File Format box, select the file format that you want. 6. If you are exporting a table or a query, and you want to export formatted data, select Export data with formatting and layout. 7. To view the destination Excel workbook after the export operation is complete, select the Open the destination file after the export operation is completecheck box. If the source object is open, and if you selected one or more records in the view before starting the export operation, you can select Export only the selected records. To export all the records displayed in the view, leave this check box cleared. This check box remains unavailable (dimmed) if no records are selected. 8. Click OK. If the export operation fails because of an error, Access displays a message that describes the cause of the error. Otherwise, in accordance with the export options specified in the wizard, Access exports the data and either does or does not open the destination workbook in Excel. Access then displays a dialog box in which you can create a specification that uses the details from the export operation. Save the export specification 1. Click Yes to save the details of the export operation for future use. Saving the details helps you repeat the same export operation in the future without having to step through the wizard each time. 2. In the Save as box, type a name for the export specification. Optionally, type a description in the Description box. 3. If you want to perform the operation at fixed intervals (such as weekly or monthly), select the Create Outlook Task check box. Doing this creates a Microsoft Office Outlook 2007 task that lets you run the specification by clicking a button. 4. Click Save Export.

Using Wildcards A wildcard is a special character that can stand for either a single character or a string of text. Wildcards are useful when you want the query to look for a range of different possible values, and also when you are not certain exactly what you are looking for but can give the query some clues to work with. The two wildcards we commonly use are the asterisk or star (*) and the question mark (?).The asterisk (*) represents any string of text from nothing up to an entire paragraph or more. The question mark (?) represents a single character only (although you could use, for example, two question marks to represent two unknown characters).For example:   

Yor* would find York, Yorkshire and Yorktown but not New York. Mar? would find Mark but not Mario, Martin or Omar. F*d would find Fred and Ferdinand but not Frederick. Like "Text*" To match text starting with a particular letter or string type the letter or string of text followed by an asterisk. Access will add the expression "Like" and place quotes around your typing. This example will display all records that have an entry starting with S in the Company field. Like "*Text" To match text ending with a particular letter or string type an asterisk followed by a letter or string of text. This example will display all records that have an entry ending with Plc in the Company field.

Like "[Letter-Letter]*" To match text starting with letters within a certain range you must type the entire expression as shown (this one is too complicated for Access to work out what you want. This example will display all the records with entries starting with the letters A - D in the Company field. You can often get the same results by using mathematical operators such as greater than (>) and less than (<). These are normally used for specifying numbers and dates but can also be used for text. For example:  

<"N" would find all entries beginning with a letter lower than the letter N in the alphabet. In other words, all entries starting with the letters A - M. >"F" And <"H" would find all entries beginning with the letters F and G.

Working with Numbers When working with numbers we normally use the mathematical operators to define the range of numbers from which we want to select. For example, where X represents a number:    

<X finds values less than X. >X finds vales greater than X >=X finds values greater than or equal to X <>X finds vales not equal to X

It is important that your field type is correctly defined as a Number field for numerical queries to work properly. Here are some examples… X To match a number simply type the number that you want the query to find. This example will display the record(s) with the entry 385 in the CustomerNumber field.

<X To find values less than a certain number type a less than sign (<) followed by the number. This example will display all records with an entry less than 1000 in the CustomerNumber field. Between X And Y To find values in a range of numbers type the expression shown where X and Y represent the numbers at opposite ends of the range. This example will display all records with entries falling within the range 500-700 in the CustomerNumber field.

Working with Dates Dates behave the same way as numbers, so you can use some of the same techniques when constructing your date query or filter. Remember, for dates to be treated properly by Access it is important that your field type has been correctly defined as a Date/Time field. It doesn't matter how you enter the date, as long as you use a recognised format. The date will be displayed in the resulting dynaset in whatever format you chose when you created the table. When you enter a date in the criteria cell you can use any standard date format, but each date must be enclosed by hash marks (#). For example:   

<#1/1/98# finds dates earlier than 1 January 1998 =#27-Sep-50# finds dates equal to 27 September 1950 Between #5/7/98# And #10/7/98# finds dates no earlier than 5 July 1998 and no later than 10 July 1998

Here are some more examples… =#Date# To match a particular date type the date enclosed by hash marks (#). This example will display all the records with entries for 27 September 1998 in the Invoice Date field.

=Date() To match today's date type the expression shown. Date() means "today". This example will display all the records with entries for the current date in the Invoice Date field.

Year([Fieldname])=Year(Now()) To match the current year type the expression shown, entering the name of the current field in square brackets where indicated. This example will display all the records with entries for the current year in the Invoice Date field. Year([Fieldname])=Year To match a particular year type the expression shown, entering the name of the current field in square brackets where indicated and the required year in place of Year. This example will display all the records with a date in 1998 in the Invoice Date field.

<Date()-30 To match a particular calculated date range you will need to use a combination of expressions. This expression employs a calculation that subtracts 30 from the current date and also includes the less than operator. This example will display all the records with a date more than 30 days old in the Invoice Date field.

Excluding Things Sometimes you want to specifically exclude criteria from your search. This is done with the expression Not. This expression can be used on its own or in combination with other expressions. For example: ď&#x201A;ˇ ď&#x201A;ˇ

Not "text" finds all records except those matching the specified text. Not Like "X*" finds all records except those starting with the specified letter (or string of text).

Here are some more examples: Not "Text" To exclude specific records from the search use the expression Not followed by the text which matches those records you want left out. The text needs to be between quotes as shown here - Access will normally do that for you. This example will find all records for contacts in towns other than London.

Not Like "Text*" You can use wildcards with the Not expression, which then becomes Not Like followed by your wildcard criteria. Here is just one example. This example will find all records for contacts in towns starting will letters other than L.

And Not "Text" The Not expression can be used in combination with other expressions, when it becomes And Not followed by the text you want to exclude from your search. This example will find all records for contacts in towns starting with the letter L but will exclude those in London.

Finding Empty Fields A query can be used to find records where specific fields are empty. To do this you use the expression Is Null. Conversely, to find records for which specific fields are not empty you use the expression Is Not Null. The expression Null simply means "nothing". Is Null To find empty fields use the Is Null expression. This looks for fields that contain no data. This example will find all records for contacts whose fax number has not been recorded. Is Not Null To find fields that are not empty use the Is Not Null expression. This looks for fields that contain data. If there is something in the field the record will be shown. Note that Is Not Null will find fields containing zero length entries. (If you want to leave them out try excluding them with the And Not expression.) This example finds all records for contacts whose fax number has been recorded. "" To find zero length entries use "" expression. This looks for zero length entries in the specified field. This example would find, depending on why you had made use of the zero length entry feature, all records for contacts who did not have a fax.

Using Parameter Queries Entering a Parameter Instead of typing a value or expression into the criteria cell, type some text enclosed in square brackets ([ ]). The text you type will appear as a prompt on a dialog box, so you might want it to be in the form of a question to the user. In this example the user will be prompted to type the name of a town when they run the query. The text that the user types will be used as the criteria for that particular field. The dialog box looks like thisâ&#x20AC;Ś

If the user were to type London then this query would display all the records with the entry London in the Town field. Using Multiple Parameters You can enter a parameter almost anywhere you would place a piece of text, number or date in a regular criterion. For example, supposing you wanted the query to prompt the user for two dates to define a date range...

Instead of typing the actual beginning and end dates into the criteria cell, type a prompt in square brackets. The user will see two separate dialog boxes, each asking for a date. After entering dates the query proceeds, inserting the dates into the appropriate places in the criteria expression. In this example the query would display all the record which contained dates in the range 1 November 1998 - 30 November 1998 in the Invoice Date field...

You can use as many parameters as you want, in as many fields as necessary. The dialog boxes appear in the same order as they do on the QBE grid. Combining Parameters with Wildcards… A useful feature of the query is its ability to accept wildcards (i.e. an asterisk "*" representing any string of characters; one or more question marks "?", each representing a single character). Wildcards allow you a degree of flexibility when specifying criteria. When you don't know exactly what you are looking for you can use wildcards to give the query a "clue". This method can also be applied to parameter queries, but you need to do a bit more than just add an asterisk or question mark. The correct syntax is as follows… For a single wildcard: Like [type prompt here] & "*" For two wildcards: Like "*" & [type prompt here] & "*" When using a single wildcard it can be placed before or after the prompt. You can use asterisks or question marks, or a combination of both. Example 1. Using a single wildcard In this example a single wildcard has been used, an asterisk.

The parameter… Like [Which Last Name] & "*" …creates a prompt in which the user can enter the first letter or string of letters of the names they want to see.

The user has entered the text "gr", causing the query to select records with entries in the LastName field of any length starting with the letters "gr". Here's the result...

Example 2. Using two wildcards In this example a two wildcards have been used, both asterisks.

The parameterâ&#x20AC;Ś Like "*" & [Which Last Name] & "*" â&#x20AC;Ścreates a prompt in which the user can enter a letter or string of letter that should occur anywhere in the names they want to see.

The user has entered the text "en", causing the query to select records with entries in the LastName field of any length containing with the letters "en" together. Here's the result... Get Creative! You can enter a parameter almost anywhere you would normally enter a specific piece of data in your query criteria. Sometimes the syntax (how you write it out) can be a bit tricky, but persevere until you get the result you need. Here are a few examples...

Finding records for a specific year (or month) from a collection of dates Supposing you have a field called InvoiceDate containing a range of dates covering several years. Use the following criteria... Year([InvoiceDate])=[Choose a year] ...will create a prompt in which the user can type a year number (e.g. 1998) to see all the records for that year. I you would rather see records for specific months use... Month([InvoiceDate])=[Choose a month from 1-12]) Note that the prompt tells the user to enter a number for the month. Access doesn't understand month names. Finding records for a specific month and year from a collection of dates If you want to be more specific and call for a particular month and year, the criteria... Month([InvoiceDate])=[Choose a month from 1-12] And Year([InvoiceDate])=[Choose a year] ...will present the user with two dialog boxes, the first asking for a month and the second asking for a year. Creating a list of records with dates in the last so many days You may want to view all the invoices generated in a recent period, such as the last 30 days. The criteria... >Date()-[The last how many days?] ...means "today minus how many days". The user enters a number (e.g. 30) to see a list of dates since that many days before today. The Date() part creates the current date so this query is always up-to-date. What about variable calculations? You can even include parameters as part of the definition of a new calculated field. (If you want to learn about calculating in Access check out the tutorial Calculating in Access Queries) For example, you have a list of invoices in which there is a field called TotalGoods and you need to calculate the discount (or tax or whatever!), but this changes from time to time. You need to create a new calculated field to work out the new figures. Instead of... Discount: [TotalGoods]*25/100

...which would always calculate a discount of 25% (note: unlike Excel, Access doesn't understand the % sign). You could substitute the fixed figure with a parameter... Discount: [TotalGoods]*[What discount rate - percent]/100 ...which would prompt the user to enter a figure representing the required discount. Asking the Questions in the Right Order When you create a query using more than one parameter, the user sees the prompts in the order that the fields are arranged in the design view of the query, reading from left to right. You normally arrange the fields in the way in which you want to see the results displayed. But what if you want the prompts to appear in a different order? Get to know the Query Parameters Window... Using the Query Parameters windowâ&#x20AC;Ś To control the order in which the prompts appear when running a parameter query containing more than one parameter, you can specify the desired order in the Query Parameters window. Here's how... 1. In the query design view choose Query > Parametersâ&#x20AC;Ś to open the Query Parameters window. 2. In the Parameter column, type the prompt for each parameter exactly as it was typed in the QBE grid. 3. In the Data Type column specify the kind of data (as defined in the table properties). Pick a type from the list. The default type is Text. 4. List the parameters in the order in which you want the dialog boxes to appear when the user runs the query. Click OK to accept your entries and close the window. Here is an example of a query containing two parameters... If you didn't specify otherwise, the prompts would appear in the order that the parameters are arranged in the QBE grid reading from left to right. The user would be asked for a Name first and then a Department. If, however, you make use of the Query Parameters window you can choose the order of the prompts. In this example the parameters have been arranged in a different order so that the user is asked for a Department first and then a Name.

There is no need to make entries in the Query Parameters window if you are happy with the way the query runs, unless you are creating a Crosstab Query containing parameters, in which case you must enter the details of the parameters to make the query run correctly.

Calculating Dates in Access Queries Access has a number of powerful tools to enable specific dates and date ranges to be specified in criteria. Many tasks can be achieved with simple calculations, and there are a number of date functions to help in performing more complex jobs.

Simple Date Calculations When you enter a date into an Access table, Access recognises it as a date, and checks it against the calendar to make sure it is a possible date. You'll get an error message if you try to enter an impossible date such 31st September or 29th February in a non leap year. Then it stores the date as a number known as the date serial. PCs use the 1900 System to store dates. 1st January 1900 was day 1, 2nd January 1900 was day 2 etc. You don't need to know the serial number of your dates, but you can make use of it in mathematical calculations. Here are a few examples…

To add or subtract days from a date... Create a new field with an expression that adds (+) or subtracts (-) the required number from the field containing the original date. For example… Due Date: [Invoice Date]+60

Due Date: creates a new field called Due Date [Invoice Date]+60 takes the date it finds in the Invoice Date field and adds 60 to its serial number. The result is automatically displayed as a date.*[see note below] To calculate the number of days between two dates Create a new field with an expression that subtracts the field containing the earlier date from the field containing the later date. For example… Stay: [Departure Date]-[Arrival Date]

Stay: creates a new field called Stay [Departure Date]-[Arrival Date] subtracts the date found in the field Arrival Date from the date found in the field Departure Date. The result is automatically displayed as a number.*[see note below]

Note: If the result fails to display as a date, or displays a date in the wrong format, switch to design view and click in the new field column. On the menu choose View > Properties (or right-click the field and choose Properties from the shortcut menu). Click in the Format section on the General tab of the Field Properties dialog box. Click the down-arrow and choose an appropriate format from the list. Close the dialog box and run the query again to see your dates displayed correctly. Using Date Functions There are four basic date functions which extract part of a date so that it may be displayed on its own, in a new field (further refined with criteria id required), or with additional information as part of the criteria of the field in which the date itself occurs. These functions are…    

Year([Fieldname]) returns the year from a date e.g. 20/8/99 would return 1999 Month([Fieldname]) returns the month from a date e.g. 20/8/99 would return 8 Day([Fieldname]) returns the day of the month from a date e.g. 20/8/99 would return 20 Weekday([Fieldname]) returns the day of the week form a date e.g. 20/8/99 would return 6 representing Friday. The weekdays numbering from 1 to 7 starting with Sunday.

Use these functions in a new field if you want to display the extracted data in addition to the original date. Remember to type the name of the new field first followed by a colon (:). When you do this you can further refine the query by entering criteria in the new field's criteria row…

Year( ) This function is used to extract the year from particular date. In this example, the function is used simply to display the year in a new field… Year : Year([Date]) creates a new field called Year containing data calculated from the field [Date].

If no criteria are defined then all the records are displayed. The usual criteria for defining numbers can be used to display specific years or ranges. For example… 1996 displays records for dates in the year 1996 only. 1996 Or 1997 displays records for dates in 1996 or 1997. >1997 displays records for dates from 1998 onwards. Between 1996 and 1999 displays records for dates in the years 1996, 1997, 1998 and 1999. If you don't need to see the extracted data separately, you can enter the function as part of the criteria of the original date field... The Year( ) function does not have to be used in a separate field. It can form part of the criteria definition. For example… Year([Date])=1997 displays records for dates in 1997 Year([Date])>1995 displays records for dates from 1995 onwards. Remember that you still have to include the name of the field (in this case the field is called [Date]) within the function, even though the criteria are typed in that field's column. The same applies to the Month( ), Day( ) and Weekday( ) functions. For example… 62

Month([Date])=9 displays records for dates in September Month([Date]) Between 4 and 8 displays records for dates in April, May, June, July and August Day([Date])=15 displays dates which are the 15th day of the month. Weekday([Date])=4 displays dates which are a Wednesday.

Advanced Date Functions Access contains a number of more sophisticated date functions for when you can't get the result you need using a simple calculation or one of the basic date functions. Here's an example... DateAdd(interval, number, [Fieldname]) Use this function to add (or subtract) a specific amount of time to a date. The interval part of the function refers to the type of time unit you want to add and requires you to enter a code…     

yyyy for year q for quarter (i.e. 3 months) m for month d for day ww for week

The number part of the function refers to how many of those units you want to add. You can use a minus number if you want to subtract from the date. Finally you need to supply the name of the field containing the original date. Adding or subtracting days or weeks can be performed with simple mathematical calculations as demonstrated above. The DateAdd function makes it easy to add years, months or quarters to a date. For example…

Date Due: DateAdd("m",2,[Date]) This expression creates a new field called Date Due into which it enters dates from the Date field to which it adds 2 calendar months.

This query could be further refined to display records whose calculated dates fall in a specific range by entering criteria in the new field's criteria row. For example, if these were due dates for invoices where the payment terms were one calendar month, entering <#15/8/99# in the criteria would display only those invoices for which payment was due before 15th August 1999.

Spreadsheets What is modelling? Explore answers, discover rules. Creating a spreadsheet model: import data, insert/delete columns & rows. Checking data entry: verification, validation, cell ranges, names, protection, panes. Manipulate strings: concatenation, left, right, mid, transpose, Using formulae: +-/*, absolute($) & relative referencing, indices. Using functions: (see table) Adjusting page layout: page set up, margins, headers/footers, display row & column headings, styles, format cells. Using display features: formatting, alignment, text wrap, conditional formatting, patterns,borders, merge cells, display formulae, column row/width. Perform searches: auto-filter, operators (AND, OR, NOT), filters. Sorting data: sort – ascending/descending. Output selected data: printing, screenshots, export.

Spreadsheets These activities have all been adapted from chapter 14 of the text book Applied ICT by Brian Sargent Graham Brown You will need to download the files. The are stored in compressed format. Extract the files to your own computer: Each activity relates directly to the syllabus set out in the Applied ICT from Cambridge.

Activity 1 - Create a spreadsheet model using external files

Open the file CARS2.CSV from the CD in a new workbook and place the fi le COLOUR.CSV on a new sheet within the workbook. Name this sheet ‘Colour’. Place the file CARCODE.CSV on a new sheet within the workbook, naming this sheet ‘Code’. Save your spreadsheet.

Activity 2 - Insert and delete rows and columns

Using the workbook that you saved in Activity 1:

(i) insert a new column between the CarCode and Model columns in the sheet Cars2 with the heading ‘Make’ (ii) delete the row containing CarID 31 (iii) insert a new row between CarID 9 and 11.

Save your spreadsheet.

Activity 3 - Data entry, validation and verifi cation

Using the workbook that you saved in Activity 2, enter the following data into a new row 13 in the sheet Cars2:

Prepare the cells B1, C1 and D1 so that only a number between 1.2 and 2 can be entered. In cell B1, enter the value 1.6. In cell C1, enter the value 1.45. In cell D1, enter the value 1.4. Check your data entry for errors.

Show evidence of your validation rule and the test data that you selected and used to make sure that they function correctly. Show evidence of the error message. Save your spreadsheet.

Activity 4 - Using named cells and ranges

Open the workbook that you saved in Activity 3. In the worksheet Cars2, name cell B1 Normal’, cell

C1 ‘Ford’ and cell D1 ‘VW’. These cells will be used to perform calculations. Save your spreadsheet.

Activity – 5 Extract data from strings

Open the workbook that you saved in Activity 4. In the worksheet Cars2, enter a function in the CarCode column to extract the left two characters of the Code. Enter a function in the CCode column to extract the third character of the Code. Replicate these functions for all cars. Save your spreadsheet.

Activity – 6 Using formulae with absolute and relative referencing

Open the workbook that you saved in Activity 5. In the worksheet Cars2, enter a formula in cell G4 (in the Sale column) to calculate the Cost of the car multiplied by the contents of the cell named ‘Normal’. Replicate this formula for all the cars. Save your spreadsheet.

Activity – 7 Using indices

Create a spreadsheet model to calculate xy for any given values. Use this to calculate:

(i) (ii) (iii) (iv)

(i)

45 X4 where x = 2.9 0.7−3 246 xy where x = 5 and y = 3.

Activity - 8 Using functions

Open the workbook that you saved in Activity 6.

Enter a function in the Make column to look up the Make, using the CarCode for the lookup value and the sheet Code which contains the fi le CARCODE.CSV. Make sure that you use both absolute and relative referencing within your function. (ii) Enter a function in the Colour column to look up the Colour of the car, using the CCode for the lookup value and the sheet Colour. Make sure that you use both absolute and relative referencing within your function. (iii) Replicate these functions so that the Make and Colour of each CarID is shown. (iv) Save your spreadsheet. (i)

Activity – 9 Using nested functions (i)

(ii)

Using the workbook that you saved in Activity 8, change the formulae in the Sale column so that it becomes a function that will calculate the selling price of the car, using the Make of the car. If this is:  Ford – multiply the Cost by the named cell Ford  VW – multiply the Cost by the named cell VW  neither of these – multiply the Cost by the named cell Normal. Replicate this function for each car. Save your spreadsheet.

Activity – 10 Performing searches

Open the file BOAT2.XLS from the CD in a new workbook. Select from all the data:

(i) (ii) (iii)

all the boats with a Sale Price of less than or equal to £5350 all the boats with a Sale Price of between £30 000 and £50 000 all the boats containing the word SeaRay or Bavaria in the Boat Name.

Activity – 11 Performing searches on two columns

Open the file JANSALES.CSV and, for each order, show the day that the order was placed.

Now select from all the data:

(i)

all the orders on 9th or 22nd January with an order number ending in 9

(ii)

all the orders on 16th, 17th or 18th January with a customer number between 299 and 400.

Activity â&#x20AC;&#x201C; 12 Performing searches

Open the file BUSES.CSV and, for each journey, if the bus was late, calculate the number of minutes it was late along with the total number of passenger minutes it was late. The number of passenger minutes is the number of passengers on the journey multiplied by the number of minutes that the bus was late.

Now select all the buses that were 5 minutes late with 40 or more passengers on the bus. Save this activity for later use.

Activity â&#x20AC;&#x201C; 13 Sorting data

Open the file that you saved in Activity 12. Select all of the buses, then sort the data into ascending order of the minutes late and then descending order of the number of passengers.

Copy/Paste Special How to Use Paste Special in Excel 2010 Microsoft Excel 2010 normally copies all the information in the range of cells you select when you paste the data. Use Excel's Paste Special command to specify other options, such as pasting only the cell contents (without the formatting) or only the formatting (without the cell contents). To paste particular parts of a cell selection, click the Paste button arrow on the Ribbon's Home tab. Then, click Paste Special on its drop-down menu to open the Paste Special dialog box.

Paste only some of a copied or cut cell's properties with Paste Special. You can specify which parts of the current cell selection to use by selecting the appropriate Paste Special options: 

All to paste all the stuff in the cell selection (formulas, formatting, you name it). This is what happens when you paste normally.



Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.



Values to convert formulas in the current cell selection to their calculated values.



Formats to paste only the formatting from the current cell selection, without the cell entries.



Comments to paste only the notes that you attach to their cells (kinda like electronic self-stick notes).



Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command.



All Using Source Theme to paste all the information plus the cell styles applied to the cells.



All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.



Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.



Formulas and Number Formats to include the number formats assigned to the pasted values and formulas. 70



Values and Number Formats to convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.



All Merging Conditional Formats to paste conditional formatting into the cell range.

When you paste, you can also perform some simple math calculations based on the value(s) in the copied or cut cell(s) and the value in the target cell(s): 

None: Excel performs no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste. This is the default setting.



Add: Excel adds the values you cut or copy to the Clipboard to the values in the cell range where you paste.



Subtract: Excel subtracts the values you cut or copy to the Clipboard from the values in the cell range where you paste.



Multiply: Excel multiplies the values you cut or copy to the Clipboard by the values in the cell range where you paste.



Divide: Excel divides the values you cut or copy to the Clipboard by the values in the cell range where you paste.

Finally, at the bottom of the Paste Special dialog box, you have a few other options: 

Skip Blanks: Select this check box when you want Excel to paste only from the cells that aren't empty.



Transpose: Select this check box when you want Excel to change the orientation of the pasted entries. For example, if the original cells' entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row.



Paste Link: Click this button when you want to establish a link between the copies you're pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.

Examples functions:

Applied ICT- functions Function

Syntax

Example/use

Average

=average(cell1, cell2)

=average(a1:b10) Works out the average number for a range of cells.

Concatenate

=concatenate(text1,text2)

=concatenate(a1,a2) adds 2 or more strings together.

Count

=count(range of cells)

=count(A1:A6) Counts the number of cells that contains numbers.

Counta

=counta(range of cells)

=counta(a1:a6) counts the number of cells that contains values.

Countif

=countif(range, criteria)

=countif(a1:a6,”Paul”) counts the number of cells in a range that contains “Paul”

Day

=day(date)

=day(A1) would return the day part of a date. Eg 15/03/2010 would return 15.

Hlookup

=HLookup( value, table_array, index_number, not_exact_match )

=HLookup(10251, $A$1:$K$3, 2, FALSE)

Hour

=hour(time)

=hour(10:23) would return the value of 10.

=If( condition, value_if_true, value_if_false )

=if(A2>10, “excellent”, “rubbish”) would display the word excellent if the contents of cell A2 are greater than 10.

Int

=int(expression)

=int(a1) would return the integer value of cell a1. Eg 10.4 would become 10

Left/right

=left/right(text,number of characters)

=left(a1,5) if A1 contained the word alphabet would return the text alpha.

LEN

=len(text)

=len(a1) returns the length of the string in cell A1

Lookup

=Lookup( value, lookup_range, result_range )

=Lookup(10251, A1:A21, B1:B21) would return nd "Tofu" returns the text Tofu from the 2 range after st identifying 10251 in the 1 range.

Mid

=Mid( text, start_position, number_of_characters )

=Mid(A1, 5, 4) would return "abet" from the text alphabet.

Min/max

=minx/max(Max( number1,

=min/max(A1:A10) returns the lowest/highest values

Would return the 2 matched 10251.

row in the range of cells that

number2, ... number_n )

in the range of cells a1 to a10.

Minute

=minute(A1)

=minute(A1) returns the minutes value in the cell A1 that contains a time.

Month

=Month( date_value )

=month(A1) would return the number of the month from a Date cell.

Now

=Now()

Returns the current system time and date.

Round

=round(number,digits)

=round(A1,2) rounds the number in cell A1 to 2 decimal places.

second

=second(time)

=second(A1) returns the seconds value of a time cell.

Subtotal

=Subtotal( method, range1, range2, ... range_n )

Subtotal function returns the subtotal of the numbers in a column in a list or database. method is type of subtotal that you'd like to create. It can be one of the following values: Value

Explanation

AVERAGE

COUNT

COUNTA

MAX

MIN

PRODUCT

STDEV

STDEVP

SUM

VAR

VARP

Based on the Excel spreadsheet above: =Subtotal(1, D2:D5)

would return 22.3925

=Subtotal(2, D2:D5)

would return 4

=Subtotal(3, D2:D5)

would return 4

=Subtotal(4, D2:D5)

would return 35.88

=Subtotal(5, D2:D5)

would return 7

=Subtotal(6, D2:D5)

would return 136191.51

=Subtotal(7, D2:D5)

would return 11.91825316

=Subtotal(8, D2:D5)

would return 10.32151

=Subtotal(9, D2:D5)

would return 89.57

=Subtotal(10, D2:D5)

would return 142.0447583

sum

=Sum ( cell1:cell2 )

=Subtotal(11, D2:D5) would return 106.5335688 =Sum(B2:B6) would add up 231.2 (the contents of cells b2 to b6.

Sumif

=SumIf( range, criteria, sum_range )

=SumIf(A2:A6, D2, C2:C6) would add up the values in C2 to C6 that are equal to the value in cell D2.

Text

=Text( value, format )

=Text(A2, "0.0") would return "123.7" returns the number in A2 as a number with decimal place.

Transpose

=Transpose( range )

=TRANSPOSE(A1:A3) moves the cells from A1 to A3 into a new range of cells eg c1 to E1

Value

=Value( text )

Converts a text value into a number.

Vlookup

=VLookup( value, table_array, index_number, not_exact_match )

=VLookup(10251, A1:B21, 2, FALSE) would return nd "Tofu" â&#x20AC;&#x201C; the value in the 2 column of A1 to B21 that equals the value in 10251

Weekday

=Weekday( serial_number, return_value )

=Weekday(A1) returns the number of the day in the cell A1 that is a date.

Year

=Year( date_value )

=Year(A1) returns the year of the cell A1 that is a date.