Microsoft access 2013 – an intermediate guide free.Microsoft Access 2013™ An Essential Guide (Level 1)

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

First Name. Last Name. Company Name. Job Title. Please Specify for Other:. Work Phone Number. Company Street Address. Company City. Minor Outlying Islands U. Yes No. Valid email address is required to fulfill your request.

Verify Your Email Address We require that you verify your email address prior to updating your account. After you\’ve verified your email address, you can review and edit your account. It is used mainly to store text and numbers for example, the Library catalogue, which includes the author, title, class number and accession number for each book.

Most modern databases also allow the storage of other types of information such as dates, hyperlinks, pictures and sounds. As well as being able to store data, a database allows you to select information quickly and easily for example, a list of the books written by a particular author or those on a certain subject. Finally, it may allow you to produce printed summaries reports of the information selected.

When setting up your own database, it is important to plan its use in advance. This is particularly important if you are setting one up which will be used by other people. Microsoft Access is a relational database management system which allows you to link together data stored in more than one table. It is fully supported by Information Technology and is available for personal purchase from the Microsoft Store students and Microsoft Home Use Programme staff. Starting Microsoft Access If you are using an IT Services machine, login as usual by entering your username and password.

Then, to start up the program: 1. Open the Windows Start button and choose All Programs 2. You can also do this with any Access file. The Access Screen On entering Access you are presented with a screen showing available templates, which have been designed to help you create your own databases. You can search for further templates at Office. These templates can be quite helpful for particular applications but you nearly always have to tailor the database produced to your own requirements.

You can also either create a new blank database without help or open an existing one. In this course you are going to use an existing database, to see how it is set up and how it can be used. Double click on the folder called Training to open it 4. Click on example The data does not refer to real people. Users are welcome to take a copy of the example file if they want to practice.

You may need to [Enable Content] to use it. This controls navigation within a particular database. A database is made up of several objects, grouped into a single file. This database has been set up to show All Access Objects which currently exist in this database, but there are other types of object as well which do not currently appear.

You will be meeting some of these later in the course. The down arrow at the top of the pane to the right of All Access Objects lets you select specific types of object. Pages, Macros and Modules are not dealt with in this course. As you use the different objects, the tabs on the Ribbon change appropriately.

Click on a double arrow on the right to show or hide the objects in a particular group 2. Click on the single arrow at the top of the Navigation Pane to view further display options Part 1: Using an Existing Table Begin by investigating the table named students. This contains data relating to imaginary students in a fictitious department in the University, but it could equally be members of a club or just information about your friends and relatives.

This method of display known as Datasheet View shows the data in columns and rows, similar to a spreadsheet. There are a number of entries records , one for each student, which each take up one line or row of the table. For each student, various items of data are recorded in columns – each column contains one variable or field.

On the top of the table is a tab, which provides easy access when you have more than one object open. Immediately below the data is a grey horizontal bar, which shows you are positioned at Record 1 of The current record has a slightly darker background, while the column on the far left is yellow-orange the current field has a coloured border.

You can move the indicator down to the next record 2 in this case by clicking on the right arrow on the grey bar. The next button to the right takes you to the end of the table – click on this and you should be at Record Matching buttons on the left take you back a single record and back to Record 1 – try out these too.

You can also move up and down using the arrow keys on the keyboard. The scroll bar down the right edge of the table window moves the display up and down. Another scroll bar is provided at the foot of the window for moving to the left and right when the records extend over more than one screen.

View: or To see exactly what each record contains and how it has been set up: 2. Short text fields are the commonest type of fields and can be used to store any characters letters, punctuation, numbers etc.

Numbers should be stored as text if not being used in calculations. This field is set up to hold up to 10 characters and a Caption is used to expand the field name. This number uniquely identifies each student – the Required property has been set to Yes and Indexed is set to Yes No Duplicates. This field has also been used to set up a Primary Key, which you will learn more about later. Instead, make use of Captions to expand the field name to include any spaces.

Not only do you have less characters to type but it makes manipulation of the data much easier if you find you need to use more advanced database features. Numbers can be stored using different field sizes; here, an integer is used – see the Appendix for a full explanation. The Default Value is set to the current year using built-in Functions.

Here the values are known there are only certain Halls of Residence so a Validation Rule has been set – up to 15 characters. Note that a Caption is used to expand the field name. The Default Value is set to No. Tip: Note that the student\’s surname is stored separately from the first name similarly each line of the address is in a separate field. Information should always be stored in its component parts. You can then, for example, sort by surname then first name, or reference the students formally i.

Mr X or informally by their first name. You will see later how to combine this data into a single field, if you need to. To close the Table Design pane and return to the top of the datasheet: 4. Click again on the [View] button – note how the icon changes as you move between Design and Datasheet view Searching for a Particular Record Find: To search for a particular record i.

The cursor is already positioned in the Find What: box – type in smith The default options should already be set correctly. The Look In: box shows the search is restricted to the current Surname field alternatively, you can search the whole table.

Search: is set to All records the other options are Up and Down. Match Case lets you distinguish capitals from lower case if you need to. Finally, Search Fields as Formatted is useful for finding data as displayed a date format, for example. Note that you also have access to a Replace tab for editing data. To close both the warning message and Find window: 7. For this reason, it wouldn\’t have been easy to search for Smith simply by scrolling through the records and imagine trying to find a book in the Library if they were all listed by their date of purchase!

If the data is sorted, however, then you can scroll through the records to search for a particular one. First, you have to move to the column on which the sort is to be based: 1.

Note that only the screen display is sorted – the records are still stored in the order in which they were typed, and they always will be. Use the [Remove Sort] button to reset the data to its original unsorted order Changing the Default Display Order If you want to keep the new display order for the next time you open the table, all you have to do is close the table, saving the changes to its design.

Try this next: 1. Move to the field you want sorted e. Surname 2. Click on [Ascending] or [Descending] if you want the data in reverse order 3. Close the table by clicking on its [Close] button immediately above the table 4. Double check you want to save them Sorting in a Query Sorts can also be carried out and stored in a query. Moreover, within a query you must set an explicit sort otherwise the records are displayed in their original order of entry. Queries are particularly useful where you have more than one field you need sorted – a simple quick sort only lets you sort on the one field you can\’t for example sort by surname then firstname.

By using a query you can produce a display sorted on any of the fields and can even create complex sorts within sorts. You will be looking at queries shortly, and carrying out sorts in them, so there is no need to carry out an example here. In a database it is used to speed up searching, sorting and grouping data – one should be set on any field used frequently in these ways.

Access records the sort order in a hidden object so that it doesn\’t need to repeat the sort each time. Indexes also perform a second useful function in that they can be used to guard against duplicate data entry. They are always used when a field is set up as a Primary Key. Try setting up an index on another field: 1. Click on the [View] button to switch back to Design View 2. This field is frequently used for sorting and duplicate values are allowed – as you have already seen with Smith.

Next click on the Userid field and note the index here is set to Yes No Duplicates. The values in this field should be unique and the index will ensure this 5. Unlike most other applications, a database does not make a working copy of the file first.

For this reason, it is essential to keep a back-up copy of your file to which you can always return , just in case you make mistakes when carrying out amendments.

New records are always added at the end of the existing data. As soon as you start to type, Access creates a new empty record marked with an asterisk , while the current record indicator changes from an arrow to a pencil: 1. Repeat step 3 until you have filled out most of the record at least the columns up to userid Note that some fields already have a default value. To change a value in a field you simply type in a new one.

Note also that some fields e. Hall and Option can only accept certain values, others e. StudNo, Surname and FirstName cannot be left blank. Calculated fields, such as email, cannot be edited. The Photograph field can hold a picture. The best way to add one is via the Clipboard i. Copy and Paste. If you right click on the field and choose Insert Object As pictures cannot be displayed in tables anyway, don\’t try filling out this field here.

Note: The best method for entering data is via a form, which you will be meeting later. To delete the current record in this case your own : 5.

If you have several records to delete: 7. Using the mouse, point to the left-hand edge of the first record to delete – you will find that the mouse cursor changes to an arrow 8. Hold down the mouse button – the record is marked – then drag through the records required 9. You will see next how to select a subset of non-contiguous records, which you could then delete.

Selecting Records Databases offer you the facility of extracting sub-sets of records according to some pre-set conditions. In the Library, for example, you can search for the books written by a particular author or those dealing with a given subject. Access offers you two methods for selection, Quick Select using a Filter and Selection using a Query.

Quick Select Selection: Toggle Filter: Filter: Simple selections can be made directly on the table itself, using a filter. Access provides two mechanisms for this, as you will see: 1.

Move across to the Hall field – to find all the students living in a particular hall 3. To turn off the filter: 5. Click on the highlighted [Toggle Filter] button below [Selection] You can also filter on part of a field – for example, you might want all students with a May birthday: 6. Double click on any occurrence of May in the DOB field or drag through the word to select it 7. Click on the small arrow at the top of the Year of Entry field or click on the [Filter] button 9.

If you wanted the July birthday students instead, you use the Date Filters option followed by All Dates in Period in the Filter button window. Note that you can also open the filter selection by clicking on the filter icon shown on the right at the top of each column.

For example, you might want to delete these records – even though they are not next to each other in the full dataset, you could drag through them here and delete them as before but don\’t do so here. Another thing you might want to do is to print off the data. However, you probably wouldn\’t want all of the fields, so you\’ll see next how to hide unwanted columns. Changing the Fields Displayed Tables often contain a lot of data, only some of which may be required. You can control which fields are shown and which hidden.

Here, you may want just the student name and hall: 1. Right click on the StudNo column heading and choose Hide Fields To hide several adjacent columns in one go: 2.

Click on the Userid column heading 3. Right click on the selected column headings and again choose Hide Fields If you want to change the order of the fields on the screen, you can either use cut and paste or, more simply, drag and drop.

Both these techniques should be familiar to the Microsoft Office user. To list the students starting with their full name including title in the correct order: 5. Click on the Surname column heading to select the column 6. Move the mouse cursor back into the column heading, hold down the mouse button then with the button still depressed drag the column to the right to a position immediately before the Year field 7.

Release the mouse button to drop the field in its new position Note: you are only changing the screen display – the data is still stored in its original order. Finally, you might want to print your list. First, it\’s a good idea to preview it: 8. Normally you would now print your list, but here: 9. Click on [Close Print Preview] on the far right of the new Print Preview tab to turn off the preview To redisplay any of the hidden fields: Right click on any column heading and choose UnHide Fields You can in fact save a filter as a query by turning on the advanced filter option.

This can be a useful aid in designing a query. As an introduction to queries, save the current filter students born in May who came in a particular year : 1. A Filter Design pane appears. It looks complicated but Access has done the hard work for you. This is very similar to the Query Design pane, which you will be using next. Examine how the criteria have been set up.

Click on the [Save] button — the Save As Query dialog box appears 3. Close the students table – don\’t save the changes to the design click on [No] Tip: The simplest way to redisplay all the fields if some are hidden is to close the table without saving the changes to its design. When you reopen it, it will appear in its original format. End by closing the query – click on its [Close] button Once a filter has been saved as a query, it\’s easy to modify its design if necessary. Using a filter in this way is straightforward but a little limited.

To do more complicated selections you have to use a Query. Queries also offer various other facilities, including sorts within sorts. In fact you may always want to view your data through a query — here, for example, you might want to see the students listed alphabetically by Surname then by FirstName.

The Select Query pane may look a little confusing, but in fact it\’s very simple to use. The cursor should be flashing in the Field: row in the lower part of the screen waiting for you to define the fields to be displayed.

Here in a query, you can: 4. Click on the list arrow on the right of the Field: cell and choose students. If you were to run the query as it stands, you would see the data in its original unsorted format i. Click on the list arrow in the Field: cell in the second column and choose Surname 6.

Repeat step 5 in the third column but choose FirstName 7. Move down to the Sort: third row and type a for Ascending in both columns 2 and 3 8. In the Show: fourth row, untick the check boxes in both columns 2 and 3 you can click anywhere in the cell – if you don\’t, the names will appear twice as they are already included in students. To carry out the query, click on the [Run] button or you can use [View] to move from Design View to Datasheet View You should find that the students are now listed in their correct order look at the Smiths and that this query should be used whenever you want to look at the complete set of data.

This time your new query will be making use of the fact that you already have the students sorted by name a query can be based either on a table or another query : 1.

Set the Field: in the first column to Surname and that in the second column to FirstName You now need the Hall field in a separate column to set up the selection criteria. Another way to fill up a field is to double click on it in the field list in the top half of the Select Query window. You can try this next: 5. Double click on the Hall — it should be added to the next empty column in the query 6. Move down to Criteria: in column 3 and type the name of the required hall – e.

Wessex 7. To carry out the query, click on the [Run] button on the far left of the Ribbon or use [View] to move from Design View to Datasheet View or right click on the Query design and choose Datasheet View One difference between a query and a filter is that you can save it directly for future use. Click on the query\’s [Close] button 9. Now try re-running the query: With a query, however, you can change the criteria each time you run it by making it a parameter query. The design is very similar to what you have already seen except that instead of setting a fixed criteria, Access asks for the information at run time.

Modify the Hall query to do this: 1. With the Hall query still open, click on the [View] button to change to the Design View 2. Type in a new criteria saying: [Which Hall? Click on the [Run] button or on [View] to switch to Datasheet View 5. When asked the question Which Hall? Wantage 6. Here, however, to run the query again: 7.

Click on the [View] button to change to the Design View 8. Click on the [Run] or [View] button again 9. Type in the name of a different hall – e. This mechanism is used when you look up a book in the Library, for example.

More Complex Queries Next, try some more complicated queries. What if you want to have an alternative criteria in a parameter query? For example, you might want a list of students living in either one hall or another. To do this, you have to set up criteria on two different lines. Click on the [View] button to change to Query Design 2. In the second line of the Criteria: in column 3, type: [or? Click on the [Run] button or on [View] to switch to Datasheet View 4.

When asked Which Hall? When asked or?

 
 

[PDF] Access to queries free tutorial for Intermediate –

 

Они вступили в опасную зону: Хейл может быть где угодно. Вдали, за корпусом «ТРАНСТЕКСТА», находилась их цель – Третий узел.

Сьюзан молила Бога, чтобы Хейл по-прежнему был там, на полу, катаясь от боли, как побитая собака.

 

Microsoft access 2013 – an intermediate guide free. ms access 2013 tutorial pdf free download PDF,Doc ,Images

 
This document is an introduction to Microsoft Access , running under Microsoft Windows 7. For further information see Microsoft Access – An. in Exam Microsoft Access Who this book is for. MOS Study Guide for Microsoft Access is designed for experienced computer users.

 
 

Microsoft access 2013 – an intermediate guide free

 
 

Get Started Welcome Back,. Please Correct the Highlighted Fields Below:. Complete the form below: Work Email Address. First Name. Last Name.

Company Name. Job Title. Please Specify for Other:. Work Phone Number. Company Street Address. Company City. Minor Outlying Islands U. Yes No. Enter the email address you signed up with and we\’ll email you a reset link. Need an account? Click here to sign up. Download Free PDF. Djoko Chris. A short summary of this paper.

PDF Pack. Download Download PDF. Translate PDF. Introduction A database is a computer program for storing information in an easily retrievable form. It is used mainly to store text and numbers for example, the Library catalogue, which includes the author, title, class number and accession number for each book. Most modern databases also allow the storage of other types of information such as dates, hyperlinks, pictures and sounds.

As well as being able to store data, a database allows you to select information quickly and easily for example, a list of the books written by a particular author or those on a certain subject. Finally, it may allow you to produce printed summaries reports of the information selected.

When setting up your own database, it is important to plan its use in advance. This is particularly important if you are setting one up which will be used by other people.

Microsoft Access is a relational database management system which allows you to link together data stored in more than one table. It is fully supported by Information Technology and is available for personal purchase from the Microsoft Store students and Microsoft Home Use Programme staff.

Starting Microsoft Access If you are using an IT Services machine, login as usual by entering your username and password. Then, to start up the program: 1. Open the Windows Start button and choose All Programs 2. You can also do this with any Access file. The Access Screen On entering Access you are presented with a screen showing available templates, which have been designed to help you create your own databases.

You can search for further templates at Office. These templates can be quite helpful for particular applications but you nearly always have to tailor the database produced to your own requirements. You can also either create a new blank database without help or open an existing one. In this course you are going to use an existing database, to see how it is set up and how it can be used.

Double click on the folder called Training to open it 4. Click on example The data does not refer to real people. Users are welcome to take a copy of the example file if they want to practice. You may need to [Enable Content] to use it. This controls navigation within a particular database. A database is made up of several objects, grouped into a single file. This database has been set up to show All Access Objects which currently exist in this database, but there are other types of object as well which do not currently appear.

You will be meeting some of these later in the course. The down arrow at the top of the pane to the right of All Access Objects lets you select specific types of object. Pages, Macros and Modules are not dealt with in this course. As you use the different objects, the tabs on the Ribbon change appropriately. Click on a double arrow on the right to show or hide the objects in a particular group 2. Click on the single arrow at the top of the Navigation Pane to view further display options Part 1: Using an Existing Table Begin by investigating the table named students.

This contains data relating to imaginary students in a fictitious department in the University, but it could equally be members of a club or just information about your friends and relatives. This method of display known as Datasheet View shows the data in columns and rows, similar to a spreadsheet.

There are a number of entries records , one for each student, which each take up one line or row of the table. For each student, various items of data are recorded in columns – each column contains one variable or field. On the top of the table is a tab, which provides easy access when you have more than one object open. Immediately below the data is a grey horizontal bar, which shows you are positioned at Record 1 of The current record has a slightly darker background, while the column on the far left is yellow-orange the current field has a coloured border.

You can move the indicator down to the next record 2 in this case by clicking on the right arrow on the grey bar. The next button to the right takes you to the end of the table – click on this and you should be at Record Matching buttons on the left take you back a single record and back to Record 1 – try out these too. You can also move up and down using the arrow keys on the keyboard. The scroll bar down the right edge of the table window moves the display up and down.

Another scroll bar is provided at the foot of the window for moving to the left and right when the records extend over more than one screen. View: or To see exactly what each record contains and how it has been set up: 2.

Short text fields are the commonest type of fields and can be used to store any characters letters, punctuation, numbers etc. Numbers should be stored as text if not being used in calculations. This field is set up to hold up to 10 characters and a Caption is used to expand the field name. This number uniquely identifies each student – the Required property has been set to Yes and Indexed is set to Yes No Duplicates.

This field has also been used to set up a Primary Key, which you will learn more about later. Instead, make use of Captions to expand the field name to include any spaces. Not only do you have less characters to type but it makes manipulation of the data much easier if you find you need to use more advanced database features. Numbers can be stored using different field sizes; here, an integer is used – see the Appendix for a full explanation.

The Default Value is set to the current year using built-in Functions. Here the values are known there are only certain Halls of Residence so a Validation Rule has been set – up to 15 characters. Note that a Caption is used to expand the field name. The Default Value is set to No. Tip: Note that the student\’s surname is stored separately from the first name similarly each line of the address is in a separate field.

Information should always be stored in its component parts. You can then, for example, sort by surname then first name, or reference the students formally i. Mr X or informally by their first name. You will see later how to combine this data into a single field, if you need to.

To close the Table Design pane and return to the top of the datasheet: 4. Click again on the [View] button – note how the icon changes as you move between Design and Datasheet view Searching for a Particular Record Find: To search for a particular record i. The cursor is already positioned in the Find What: box – type in smith The default options should already be set correctly. The Look In: box shows the search is restricted to the current Surname field alternatively, you can search the whole table.

Search: is set to All records the other options are Up and Down. Match Case lets you distinguish capitals from lower case if you need to. Finally, Search Fields as Formatted is useful for finding data as displayed a date format, for example. Note that you also have access to a Replace tab for editing data. To close both the warning message and Find window: 7. For this reason, it wouldn\’t have been easy to search for Smith simply by scrolling through the records and imagine trying to find a book in the Library if they were all listed by their date of purchase!

If the data is sorted, however, then you can scroll through the records to search for a particular one. First, you have to move to the column on which the sort is to be based: 1. Note that only the screen display is sorted – the records are still stored in the order in which they were typed, and they always will be.

Use the [Remove Sort] button to reset the data to its original unsorted order Changing the Default Display Order If you want to keep the new display order for the next time you open the table, all you have to do is close the table, saving the changes to its design.

Try this next: 1. Move to the field you want sorted e. Surname 2. Click on [Ascending] or [Descending] if you want the data in reverse order 3.

Close the table by clicking on its [Close] button immediately above the table 4. Double check you want to save them Sorting in a Query Sorts can also be carried out and stored in a query. Moreover, within a query you must set an explicit sort otherwise the records are displayed in their original order of entry.

Queries are particularly useful where you have more than one field you need sorted – a simple quick sort only lets you sort on the one field you can\’t for example sort by surname then firstname. By using a query you can produce a display sorted on any of the fields and can even create complex sorts within sorts. You will be looking at queries shortly, and carrying out sorts in them, so there is no need to carry out an example here. In a database it is used to speed up searching, sorting and grouping data – one should be set on any field used frequently in these ways.

Access records the sort order in a hidden object so that it doesn\’t need to repeat the sort each time. Indexes also perform a second useful function in that they can be used to guard against duplicate data entry. They are always used when a field is set up as a Primary Key.

Try setting up an index on another field: 1. Click on the [View] button to switch back to Design View 2. This field is frequently used for sorting and duplicate values are allowed – as you have already seen with Smith.

Next click on the Userid field and note the index here is set to Yes No Duplicates. The values in this field should be unique and the index will ensure this 5. Unlike most other applications, a database does not make a working copy of the file first.

For this reason, it is essential to keep a back-up copy of your file to which you can always return , just in case you make mistakes when carrying out amendments. New records are always added at the end of the existing data.

As soon as you start to type, Access creates a new empty record marked with an asterisk , while the current record indicator changes from an arrow to a pencil: 1. Repeat step 3 until you have filled out most of the record at least the columns up to userid Note that some fields already have a default value. To change a value in a field you simply type in a new one. Note also that some fields e.

Hall and Option can only accept certain values, others e. StudNo, Surname and FirstName cannot be left blank. Calculated fields, such as email, cannot be edited.

The Photograph field can hold a picture. The best way to add one is via the Clipboard i. Copy and Paste. If you right click on the field and choose Insert Object As pictures cannot be displayed in tables anyway, don\’t try filling out this field here. Note: The best method for entering data is via a form, which you will be meeting later. To delete the current record in this case your own : 5. If you have several records to delete: 7.

Using the mouse, point to the left-hand edge of the first record to delete – you will find that the mouse cursor changes to an arrow 8. Hold down the mouse button – the record is marked – then drag through the records required 9. You will see next how to select a subset of non-contiguous records, which you could then delete. Selecting Records Databases offer you the facility of extracting sub-sets of records according to some pre-set conditions.

In the Library, for example, you can search for the books written by a particular author or those dealing with a given subject. Access offers you two methods for selection, Quick Select using a Filter and Selection using a Query. Quick Select Selection: Toggle Filter: Filter: Simple selections can be made directly on the table itself, using a filter. Access provides two mechanisms for this, as you will see: 1. Move across to the Hall field – to find all the students living in a particular hall 3.

To turn off the filter: 5. Click on the highlighted [Toggle Filter] button below [Selection] You can also filter on part of a field – for example, you might want all students with a May birthday: 6.

Double click on any occurrence of May in the DOB field or drag through the word to select it 7. Click on the small arrow at the top of the Year of Entry field or click on the [Filter] button 9. If you wanted the July birthday students instead, you use the Date Filters option followed by All Dates in Period in the Filter button window.

Note that you can also open the filter selection by clicking on the filter icon shown on the right at the top of each column. For example, you might want to delete these records – even though they are not next to each other in the full dataset, you could drag through them here and delete them as before but don\’t do so here. Another thing you might want to do is to print off the data. However, you probably wouldn\’t want all of the fields, so you\’ll see next how to hide unwanted columns.

Changing the Fields Displayed Tables often contain a lot of data, only some of which may be required. You can control which fields are shown and which hidden. Here, you may want just the student name and hall: 1. Right click on the StudNo column heading and choose Hide Fields To hide several adjacent columns in one go: 2. Click on the Userid column heading 3.

Right click on the selected column headings and again choose Hide Fields If you want to change the order of the fields on the screen, you can either use cut and paste or, more simply, drag and drop.

Both these techniques should be familiar to the Microsoft Office user. To list the students starting with their full name including title in the correct order: 5. Click on the Surname column heading to select the column 6. Move the mouse cursor back into the column heading, hold down the mouse button then with the button still depressed drag the column to the right to a position immediately before the Year field 7. Release the mouse button to drop the field in its new position Note: you are only changing the screen display – the data is still stored in its original order.

Finally, you might want to print your list. First, it\’s a good idea to preview it: 8. Normally you would now print your list, but here: 9. Click on [Close Print Preview] on the far right of the new Print Preview tab to turn off the preview To redisplay any of the hidden fields: Right click on any column heading and choose UnHide Fields You can in fact save a filter as a query by turning on the advanced filter option.

This can be a useful aid in designing a query. As an introduction to queries, save the current filter students born in May who came in a particular year : 1. A Filter Design pane appears. It looks complicated but Access has done the hard work for you.

This is very similar to the Query Design pane, which you will be using next. Examine how the criteria have been set up. Click on the [Save] button — the Save As Query dialog box appears 3. Close the students table – don\’t save the changes to the design click on [No] Tip: The simplest way to redisplay all the fields if some are hidden is to close the table without saving the changes to its design. When you reopen it, it will appear in its original format. End by closing the query – click on its [Close] button Once a filter has been saved as a query, it\’s easy to modify its design if necessary.

Using a filter in this way is straightforward but a little limited. To do more complicated selections you have to use a Query. Queries also offer various other facilities, including sorts within sorts.

In fact you may always want to view your data through a query — here, for example, you might want to see the students listed alphabetically by Surname then by FirstName. The Select Query pane may look a little confusing, but in fact it\’s very simple to use. The cursor should be flashing in the Field: row in the lower part of the screen waiting for you to define the fields to be displayed. Here in a query, you can: 4.

Click on the list arrow on the right of the Field: cell and choose students. If you were to run the query as it stands, you would see the data in its original unsorted format i.

Click on the list arrow in the Field: cell in the second column and choose Surname 6. Repeat step 5 in the third column but choose FirstName 7. Move down to the Sort: third row and type a for Ascending in both columns 2 and 3 8. In the Show: fourth row, untick the check boxes in both columns 2 and 3 you can click anywhere in the cell – if you don\’t, the names will appear twice as they are already included in students.

To carry out the query, click on the [Run] button or you can use [View] to move from Design View to Datasheet View You should find that the students are now listed in their correct order look at the Smiths and that this query should be used whenever you want to look at the complete set of data. This time your new query will be making use of the fact that you already have the students sorted by name a query can be based either on a table or another query : 1.

Set the Field: in the first column to Surname and that in the second column to FirstName You now need the Hall field in a separate column to set up the selection criteria. Another way to fill up a field is to double click on it in the field list in the top half of the Select Query window. You can try this next: 5.

Double click on the Hall — it should be added to the next empty column in the query 6. Move down to Criteria: in column 3 and type the name of the required hall – e. Wessex 7. To carry out the query, click on the [Run] button on the far left of the Ribbon or use [View] to move from Design View to Datasheet View or right click on the Query design and choose Datasheet View One difference between a query and a filter is that you can save it directly for future use.

Click on the query\’s [Close] button 9. Now try re-running the query: With a query, however, you can change the criteria each time you run it by making it a parameter query. The design is very similar to what you have already seen except that instead of setting a fixed criteria, Access asks for the information at run time.

Modify the Hall query to do this: 1. With the Hall query still open, click on the [View] button to change to the Design View 2. Type in a new criteria saying: [Which Hall?

Click on the [Run] button or on [View] to switch to Datasheet View 5. When asked the question Which Hall? Wantage 6. Here, however, to run the query again: 7. Click on the [View] button to change to the Design View 8. Click on the [Run] or [View] button again 9. Type in the name of a different hall – e.