Access 2003 Copyright 2009 Steve Copley www.igcseict.info This work is licensed under the Creative Commons Attribution Non-Commercial 3.0 License To view a copy of this license, visit http://creativecommons.org/licenses/by-nc/3.0/
1
2
3
Getting Data Into Your Database 1.1
Creating a Blank Database
4
1.2
Importing a CSV File
6
1.3
Fixing data in Yes/No Fields that are shown as 0 and -1
10
1.4
Showing Evidence of Your Import
12
Creating Queries 2.1
Creating a Simple Query
16
2.2
Query Criteria for Text Fields
19
2.3
Query Criteria for Numeric Fields
23
2.4
Query Criteria for Boolean Fields
26
2.5
Query Criteria for Date Fields
27
2.6
Adding a Calculated Field to a Query
29
2.7
Creating a Summary Query
31
Creating Reports 3.1
Creating a Report
34
3.2
Adding a Summary Field to a Report
42
3.3
Creating Labels
46
Getting Data Into Your Database
Access 2003 - 3
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating a Blank Database This lesson will show you how to create a new, blank database ready to add data to
1. Create a New File Click the File menu and then New...
2. Chose the Database Type Select Blank Database... from the list of options
Access 2003 - 4
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
3. Name and Save Your New Database Navigate to the folder that you want to save your database in, type in a sensible name and click Create
4. Finished! Your new database is now ready for use
Access 2003 - 5
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Importing a CSV File This lesson will show you how to import data from a CSV file into your new database
Start the import wizard In the Tables section of your database, click New and then select Import Table
Go to the CSV file folder The CSV file may not be visible because, by default, Access is looking for Access files only
Change the file type If you change the file type to Text Files, the CSV file should now be visible
Access 2003 - 6
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
First step of the import wizard A CSV file is a Delimited file, so no changes here - just click Next
Second step of the import wizard Your CSV file probably has a first row containing field names - Tick this box. Also, to make sure the fields have the correct data type, click Advanced
Advanced settings Check that the Data Types are set correctly Make sure that all fields have Indexed set to No If any of the fields contain dates, make sure that the Date Order matches the data
Access 2003 - 7
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Third step of the import wizard You want to put the data in a New Table - just click Next
Forth step of the import wizard No changes are needed here (you set all this in the Advanced settings) - just clcik Next
Fifth step of the import wizard For a simple database with just one table, you won't need a primary key Select No Primary Key and click Next
Access 2003 - 8
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Final step of the import wizard Give your new table a name, and clcik Finish
Success! You should see this message if everything went ok.
Your new table If you get an error message, or you have , throw your new table away, run the the wizard again, and follow every step carefully
Open it up and check it looks ok
Access 2003 - 9
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Fixing data in Yes/No Fields that are shown as 0 and -1 When you import data as boolean (Yes/No) from a CSV file, Access shows the data values as -1 and 0. This is correct (-1 means Yes and 0 means No) but it looks wrong. This lesson will show you how to change the format of the data so that you can see 'Yes' and 'No'
The problem: Yes is shown as -1, No is shown as 0 Open the table - you should see the Yes/No data displayed as -1 and 0
Go to Design View Click the View button to enter the table design view
Access 2003 - 10
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Change the format of the Yes/No (Boolean) field Clcik the Yes/No data field, then select Yes/No for the Format menu at the bottom of the window
Back to data view Click the View button again to go back to the table data view
Check everything looks ok Your Yes/No data should now be shown as Yes and No
Access 2003 - 11
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Showing Evidence of Your Import You need to prove tothe examiner that you have imported the CSV data using the correct data types. This lesson will show you how to take and print a screenshot to prove the import was correct.
Open the table in design view Click the table to highlight it, then click Design
Take a screenshot of the table's design view Press the Print Screen key on your keyboard (often labeled PrtScr, PrntScr, or something similar)
Access 2003 - 12
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Paste the screenshot into a word-processor document Paste the screenshot and add your name and details below
Select the screenshot to see the Picture Toolbar Click the screenshot and the Picture Toolbar should appear (if not, right-click and Show picture toolbar)
Select the crop tool
Crop the screenshot You only need to show the field names and data types, so crop off the rest of the screenshot
Access 2003 - 13
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Resize the remaining screenshot Turn off the crop tool then resize the remaining part of the screenshot so that the writing is clear to read You can now print the document
Access 2003 - 14
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating Queries
Access 2003 - 15
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating a Simple Query Create a new query Go to the Queries section and click New
Select Design View Although the wizard is sometimes quicker to use, it can't always do what we need - stick to Design View
Add in the table Click the Add button to add the table into the query builder
Access 2003 - 16
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Select the fields you need Double-click each of the fields that you need you will see them added to the query builder grid (If you add a field by mistake, click the grey bar above the field to highlight it and then press the Delete key)
Add the required criteria for each field Enter the criteria specified for each field. Criteria on the same row, all have to match (AND) Criteria on different rows, any can match (OR)
Hide fields that are not required to be shown Untick the Show boxes for those fields that are part of the query, but should not be shown
Sort the data Select Ascending or Descending sorting for the required fields. Note: Sorting is applied from left-to-right (so here the Countries will be sorted first, and then the Dates within each country)
Run the query Click the View button to go to the data view and run the query
Access 2003 - 17
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Check the results Look at your query's results - do they seem correct?
Go back to design view to make any changes Click the View button again to go back to the query builder's design view
Close and save the query Clcik the red cross to close the query and to save it
Give the query a name (e.g. Q37 if it is part of Question 37) Finished!
Access 2003 - 18
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Query Criteria for Text Fields Queries work because you set the criteria for the records that you want to find. This lesson will show you a range of criteria that can be used with text fields.
Find a specific word Just type the word(s) in. Access will add speech marks around the word(s) if you don't
Only records with the Country 'France' are shown
Find one of several possibilities A number of alternatives can be specified by adding them to different rows
Records with the Country set to either 'France' or 'Germany' are shown
Access 2003 - 19
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Find several different criteria Criteria can be added to more than one field. Note: When criteria are on the same row, both criteria have to match (e.g. 'France' AND 'Paris')
Only records where Country is 'France' AND City is 'Paris' are shown
Text starts with... The symbol * represents 'anything' So the criteria M* means that the data must begin with the letter M, but the rest of the text can be anything
Note: Access converts M* to the correct crieria: Like "M*" (you do not need to type 'Like...' yourself)
Only records with City beginning with 'M' are shown
Text ends with... The criteria *S means that any text will be matched as long as the last letter is S
Access 2003 - 20
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Only records with City ending in 'S' are shown
Text contains... The criteria *AN* means that the text can start with anything, and end with anything, as long as the letters AN are in the text somewhere
Only records with Country containing the letters 'AN' are shown
Word is exactly ... letters long The symbol ? means any single letter So the criteria "?????" means any word as long as it has exactly 5 letters (Note: You need to type the speech markls around this criteria)
Only the records which have City with 5 letters are shown
Access 2003 - 21
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Text is not blank Since * means anything, using it as the criteria for a field will find those records which have non-blank fields
Records where Notes contain some text (not blank) are shown
Text is blank The word Null means 'nothing' Using Null as the criteria will find records which have blank fields
Records where the Notes field is blank (Null) are shown
Access 2003 - 22
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Query Criteria for Numeric Fields Queries work because you set the criteria for the records that you want to find. This lesson will show you a range of criteria that can be used with numeric fields.
Find a specific value Just enter the number as the criteria
Only records where Quantity is exactly 50 are shown
Find data that is less than a value Use the 'less than' symbol (<) before the number
Only records where Quantity is less than 50 are shown
Access 2003 - 23
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Find data that is less than or equal to a value (... or under) Use the 'less than' sign (<), followed by the 'equals' sign (=) to make <=
Only records where the Quantity is 50 or less are shown
Find data that is higher than a value Use the 'greater than' sign (>)
Only records where Quantity is over 50 are shown
Find numbers that are not equal to a value Use the word Not before the value
Access 2003 - 24
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Only records that have a Quantity that is not 50 are shown
Find values in a range (from ... to ...) Write 'Between' the lower value 'And' the upper value
Only records where the Quantity is between 10 and 20 are shown
Access 2003 - 25
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Query Criteria for Boolean Fields Queries work because you set the criteria for the records that you want to find. This lesson will show you a range of criteria that can be used with Boolean fields.
Find values that are Yes / True Enter Yes as the criteria (without speech marks)
Only records that are Urgent are shown
Find values that are No / False Enter No as the criteria
Only records that are not Urgent are shown
Access 2003 - 26
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Query Criteria for Date Fields Queries work because you set the criteria for the records that you want to find. This lesson will show you a range of criteria that can be used with date fields.
Find a specific date Type the date as the criteria
Note: Access will add hash symbols around any dates that you type (you don't need to do this yourself)
Only records with Date of exactly 01/01/09 are shown
Find dates after a given date The 'greater than' sign (>) is placed before the date
Only records with Dates after 01/03/09 are shown
Access 2003 - 27
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Find dates before a given date The 'less than' sign (<) is placed before the date
Only records with Dates before 01/03/09 are shown
Find dates in a given range Type 'Between' the first date 'And' the last date
Only records with Dates between 01/03/09 and 31/03/09 are shown
Access 2003 - 28
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Adding a Calculated Field to a Query New, calculated fields can be added to queries. This lesson will show you how to add a new, calculated field
Type the calculation into a the query builder Simply type in the field names and the operation required (+, -, *, /)
Calculation becomes an expression Access will convert your calculation into a standard expression, called Expr1. Access adds square brackets [ ] around each field name. You don't need to type these yourself unless the field name is more than one word, e.g. [Quantity Ordered]
Change the expression's name Highlight Expr1 and change it to the required field name
The expression is now complete Of course, you could just type this in directly!
Access 2003 - 29
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Alter the field format if needed Right-click the field name and select Properties
Choose the format required Pick the required format and/or the decimal places
Run the query and check the results Finished!
Access 2003 - 30
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating a Summary Query Summary queries are used to summarise the data from many records. This lesson will show you how to create a summary query
Start with a normal query Begin creating a query just as you normally would
Show the Totals row Click the Totals button to show/hide the Total row
The Totals row is now visible The Total row is what makes a summary query different from a normal query
Add fields to the query Notice that the Total row says Group By under each new field
Access 2003 - 31
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Choose the summarisation Under the fields that you want to summarise, pick the how you want the data summarised: Sum, Avg, Min, Max, Count In this example, the query will count the number of Items
Leave one field to Group By One field only must have the Total row set to Group By - this is the field that you are using to group the summary data In this example, the data will be summarised by Country: For each Country the query will show Item count and the sum of the Price
Run the query to see the summarised data The ouput of the query will show the summarised data. In this example, you can see the data has been grouped by country. (You can see that there are 13 items from England, worth a total of £71.55)
Access 2003 - 32
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating Reports
Access 2003 - 33
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating a Report Reports are used to present a collection of data, usually ready for printing. This lesson will show you how to create a well-formatted report
Always start with a query Your query will do all the hard work of getting the data for your report together
Check that your query works Does the data from your query look ok? Check the correct records are shown, the correct fields are visible, and the data is sorted correctly
Create a new report Go to the Reports section and click New
Access 2003 - 34
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Start the Report Wizard Select the Report Wizard and pick the query that you created
Step 1 of the Report Wizard - Select the fields Your query has already chosen fields that you need - you just need to put them into the report. Move all of the fields to the Selected Fields box using the 'Move All' button
All fields are now in the report Click Next
Access 2003 - 35
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Step 2 of the Report Wizard - No Grouping required Click Next
Step 3 of the Report Wizard - Select sorting options Your query should have sorted your data, but sometimes you need to select the options here too
Step 4 of the Report Wizard - Chose the page orientation Pick Portrait or Landscape and then click Next
Access 2003 - 36
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Step 5 of the Report Wizard - Pick a style for the report Select any style (they aren all ugly!) then click Next
Final step of the Report Wizard - Enter a title for the Report The title you enter here will appear at the top of the finished report. Click Finish
Access 2003 - 37
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Preview your report There are still a couple of things to do before you can print the report...
Check the report closely for problems Sometimes data or labels are truncated (cut off). These need to be resized
Go to design view Click the View button to go to the report design view
Access 2003 - 38
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
The report design view
Adjust the relevent labels and/or fields The label (the top one) and the data field (the bottom one) can both be adjusted at the same time... Click on one, hold down the Shift key, and click the second. The label and field can now be resized / moved together
Make all labels and/or data fields visible
Check the report All labels and data should be visible
Make room to add your name to the page footer Make some room in the Page Footer by dragging the bottom edge downwards
Access 2003 - 39
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Select a new Label from the Toolbox For simple items of text, a label is all that is needed. If the Toolbox is not visible, click the View menu, then Toolbox
Write your name Click and drag to create a label box, then type your name into the box Note: Do not type your name into one of the textboxes that are already in the page footer (the date and page number). You can, however, delete these to make more room for your name label if you wish
Access 2003 - 40
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Do a final check of the report If everything is ok, print it!
Access 2003 - 41
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Adding a Summary Field to a Report It is quite common to want to see some kind of summary data at the bottom of a report: a total, an average, etc. This lesson will show you how to add a summary field under a report data column
The Report Footer is the area just below the last record Note: The Report Footer is totally seperate from the Page Footer The Report Footer appears just once, at the end of the report (even if the report is 10 pages long) whereas the Page Footer appears on every page The Report Footer is the perfect place to add summary fields, such as totals or averages
Open up the Report Footer Drag down the bottom edge of the Report Footer to make room for the summary field
Access 2003 - 42
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Select a new textbox from the Toolbox You need to use a textbox for the summary since you will be typing in a formula. Formulas do not work in labels, only in textboxes
Add the textbox under the column you wish to summarise The textbox comes with its own label
Type the text for the label The label text should indicate what the summary field contains
Type the formula into the textbox The formula is similar to a spreadsheet formula: =SUM( field ) to add up the field column =AVG( field ) to average the field column =MIN( field ) to find the smallest value =MAX( field ) to find the biggest value =COUNT( field ) to count the values
Access 2003 - 43
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Alter the format of the summary field Right-click, and select Properties
Select the format and/or decimal places
Access 2003 - 44
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Preview the report to check the summary field is ok
Zoom in to check the field Make sure that the label and data are visible, and the formatting is correct
Access 2003 - 45
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Creating Labels The records from your database can be printed onto sheets of sticky labels This lesson will show you how to create a label report Note: just as with a normal report, you should always create a query first to collect the data you need
Create a new report Go to the Reports section, then click New
Select the Label Wizard Select Label Wizard and pick the query that you created
Access 2003 - 46
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Step 1 of the Label Wizard - Label page layout Choose a layout that gives you the correct number of labels across the page (side-by-side). Then click Next (Stick to the first label in the Avery collection - the labels are a good size)
Step 2 of the Label Wizard - Fonts leave the font on its default settings. Just click Next
Step 3 of the Label Wizard - Layout the label First add details such as headings and your name - simply click on the prototype label and type
Add in the fields in the required positions (normally one per line) When the label has been designed, click Next
Access 2003 - 47
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Step 4 of the Label Wizard - Sorting options Your query should have set the sorting options, but you can do it here too. Click Next
Final step of the Label Wizard - Title You can use any title - it never gets printed. Click Finish
Preview your sheet of labels Check the labels look ok, and that they fit on the page
Access 2003 - 48
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed
Adjust the label if needed Go to the label design view to adjust any of the text / fields. Select a field and change the font / bold / alignment as needed
Final check Preview the labels one last time. If they look ok, print them
Access 2003 - 49
Copyright 2009 Steve Copley (www.igcseict.info) CC Licensed