Home

Access 2000 Basic User Manual

Contents

1. Mailing label reports are created inthe Report tab C amp GSi Design View Click on the New button to create a new BN Report Ward report AutoReport Tabular This wizard creates a report Scag ra The New Report dialog box will open up eas lait Select Label Wizard es In the Choose a Table or Query drop eee a he from down list select the table or query that contains the names and addresses for the labels Click OK You must now tell Access what labe S yo u are us j n g This wizard creates standard labels or custom labels What label size would you like If yo u are us j n g Ave ry labe S f Product number Dimensions Number across YOU Can 5096 ie ve S 5097 1 1 2 x 4 A 5160 1 x25 3 e Select Avery in the 5161 x4 2 gt Fi Iter by M an ufactu rer Unit of Measure Label Type is t English Metric Sheetfeed Continuous Filter by manufacturer avery bd Select the appropriate Customize Fa ener eee Bees Avery label code ancel Bac ints If the right code is not showing ee you may need to switch between English and Metric unit of measure If you are using Dudley labels Click on the Customize button lf you clicked on the Customise Label Name Dimensions Number Across Close button you will now have to select an existing cu
2. EXPORTING A REPORT TO WORD OR EXCEL EXPORTING A REPORT TO WORD EXPORTING A REPORT TO EXCEL USING THE REPORT WIZARD TO RENAME A REPORT MAIL MERGE USING WORD TO MAIL MERGE WITH AN ACCESS DATABASE CREATING MAILING LABELS IN ACCESS To Print The Report To Close The Report CONVERTING A DATABASE To Convert A Database To Access 2000 BASIC ACCESS 2000 SEPTEMBER 1999 42 42 42 43 46 47 47 48 50 51 51 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 INTRODUCTION Access is a database program created by Microsoft The Trust has now started to use Access 2000 in the Windows NT operating system which has replaced Access 2 0 running in Windows 3 1 What Is A Database Databases are used to store information in a structured way Computerised databases enable you to store large amounts of information You can then search for any piece of information or sort the information by any criteria You can cross reference the data easily and extract data to be viewed on screen or printed out in a variety of different formats For example if you have a database of staff working at a company you can extract the names of all staff who work at a certain location and have been with the company for over 5 years You can then sort the extracted list alohabetically by surname and print it out Multiple Users Unlike Word documents and Excel spreadsheets an Access database can be used by several people at the same time The changes ma
3. box enter the text you wish to find Eind ent Look In Lastname ing Match whole Field More gt The Look In field specifies which field to search through This will display the last field you clicked in But you can change it to select the whole table The Match control allows you choose how the find text must match the contents of the field Whole Field looks for fields that contain exactly the text entered in the Find What box Any Part of Field looks for fields that contain at least the text entered in the Find What box Start of Field looks for fields that start with the text entered in the Find What box e Select the appropriate options Click on the Find Next button to find the next instance of the search text starting from the cell you last clicked in Access will find and select the field containing this text in your table Click on the Find Next button to find each subsequent instance of the search text If you keep clicking a message will appear that Access has finished searching Click OK to get rid of the message Click on the Cancel button to close down the Find dialog box 18 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 FIND AND REPLACE Find and Replace is used to find all the occurrences of a particular word or number or date and to replace it with something else To Find And Replace Data lf you Know which field contains the d
4. 20 100 00 24 250 00 21 750 00 The results of the query will appear 22 000 D0 16 000 00 20 000 00 Record 14 4 1 r of 11 BASIC ACCESS 2000 31 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 Examples EBIT SOLUTIONS LIMITED Here are some examples of queries that perform calculations eS Field Cae Table Staff List Staff List a Total GroupBy Avg Sort Show Criteria or This calculates the average salary for each ethnic category th Field Deparment fhainne _ Table tbl Staff List 7 Staff List Total Group By s Count Sort Show Criteria or This counts the number of people in each department OTIP USING AN ALIAS IN A QUERY Instead of having a heading such as AvgOfSalary or SumOfLastname in your query results you can create a different heading This is done in the Field row in the query grid o Enter the name you wish to appear followed by a colon a space and then the field name eg Average Salary Salary When you run the query the name before the colon will appear in the query datasheet as the column heading 32 Field Table Total Sort S Ao Criteria or This calculates the total salary for all staff Field Postcode Lastname T able lat List tol Staff List Total GroupBy Count ee ape Sort Show Oo M Criteria o
5. Option buttons are another way of presenting Status you with a list of options out of which you can Permanent only choose one Part Time Click on the appropriate button to select the i Contract option Check Boxes A Check Box is a yes no option E T Click on the box to add a tick yes Click again to remove the tick no BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 A MAIN SUBFORM A Main Subform is used to display and edit data from two separate tables that are linked together The following picture shows a main subform containing staff training details A Staff Training Me E UE riiin Firstname enny Lastname Jones Extension 4967 Main Course _ Date Attend Late windows O O O 1970341998 Yes D Form M wodBasice OPO VISOR Yes a Word Intermediate 02706 1998 Yes U Excel Basic 2070841998 Yes 0 a ee ee ee Subform Record I 4 i ol erie of 4 An Record uall 2 H AK of 26 aJo o La Main Form Subform Record Selector Record Selector The main form contains data about each staff member The subform contains data about the training courses attended by the staff member selected in the main form displayed in Datasheet view Because there are two forms displayed there could be two separate record selectors to navigate through the records in each form The main form record selector buttons move you through the records of each
6. new one BASIC ACCESS 2000 51 IT Support London Free Microsoft Office Training Manuals
7. Repeat these steps for each table you wish to add Click on Close a Staff2000 Database Open Design gp New x 2a Te EE E gry Dates of Birth EH Tables gry Number of Staff at Each Office qry Staff Trained on Powerpoint as Queries EE Forms 15 x 47 i Reports Pages Macros a Modules Hew Query ki Ei Simple Query Wizard Find Unmatched Query Wizard Create a new query without mery using 4 wizard OK Cancel Show Table E x Tables Queries Both BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 The Query window will appear containing a Field List for each table you selected and the QBE grid Query By Example Each Field List contains the field headings for each column of data in the selected table The name of the table is shown at the top of the Field List box You can use the vertical scroll bar on the right of the box to view all the field names Field List QBE Grid l Ei Query Select Query tbl Statt List r _ ee T Field Row Feel Lastname Dob LC O Table tbl Staff Lst tbl Staff List tbl Staf Lia Oo o Table Row E So 4 Show A o A O A S Sort Row M Show Row A Criteria Row The QBE grid is used to choose the data that is extracted from the database Field Row is used to choose which fields of data are extracted Table Row is used to specify which table
8. Sorts dates from most recent to earliest To Sort The Table Click in the column you wish to sort by Click on the Sort Ascending or the Sort Descending bution In Access 2000 the sort order of records is now saved when you save the layout of a table so that the records will appear in the saved order when you next open the table 20 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 QUERIES Queries are the questions that you ask your database about the information stored in it To run a query is to extract a particular set of data from the database Queries allow you to view particular data analyse the data and even make edits to the data You can view the data from just one table or you can view data from several tables For example in our Staff2000 database you could run a simple query to ask the database to show you the first name surname and date of birth of all staff The results of a query are called a Dynaset A dynaset is a datasheet that looks like a table but is not It is a dynamic view of data that is actually stored in one or more tables It is dynamic because changes made to the query dynaset will affect the data tables CHANGES TO THE QUERY DATA WILL ALSO CHANGE THE UNDERLYING TABLE DATA Queries are usually based on tables but you can base a query on another query Sorting When you create a query it is often easier to read the data if the records are sorted into some alphabetical or nu
9. Source Tray 3 7 Click O K m Printer For tbl Staff List Default Printer C Use Specific Printer OK Cancel BASIC ACCESS 2000 15 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED PRINT PREVIEW Print Preview can be used to see how a table will look when it is printed out e Click on the Print Preview button Al The Print Preview screen will appear showing how the table fits on the A4 page Microsoft Access tbl Staff List Table E Fie Edit View Insert Format Records Tools window Help D 52000 lt o E E a0 A You can click on the page to zoom in to see the page in more detail Then click again to Zoom out These are the main buttons you will need to use on the Print Preview toolbar Print Zoom Close E 80 0mm Fr r Cose WE Print prints the whole table out Zoom zooms you in and out on the page Close closes Print Preview and returns you to your table You can use the page navigation buttons at the First Previous Next Last bottom of the window to see how many pages Page Page Page Page the data will print out on Me J v4 Page Ji 1 FI To Close Print Preview Cl Click on the Close button on the Print Preview toolbar mii 16 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 COPYING DATA TO WORD OR EXCEL You can copy all or part
10. The Record Selector at the bottom of the form is used to move between the records in the form This works in exactly the same way as for a table Total First Previous Current Next Last New Number of Record Record Record Record Record Record Records NILI Lee Record 1 k of 24 First Record Returns you to record 1 Previous Record Moves you back one record Current Record Displays the record currently selected You can click in this area and type the record number you would like to move to then press Return Next Record Moves you forward one record Last Record Moves you to the last record New Record Creates a new blank record Total Number of Displays the total number of records Records 34 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 To Move Between Fields There are some keyboard navigation commands that can be used to move around the form Tab ShittTab Home Ena Home uefa tetPage Up tl Page Down owe To Enter Data The method of entering data into each field can vary slightly depending on the type of field control used The different types of field controls are listed on the next page o Enter text or select the appropriate option To Close A Form In the File menu select Close BASIC ACCESS 2000 35 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED FIELD CONTROLS There are several differen
11. that contain grouping sorting or calculations Because it is so versatile it is difficult to document this wizard The steps that appear depend on the number of tables that you base the report on and the type of fields you choose to include ie text or numerical data Here is an example report to display a breakdown of salaries by department from the Staff List table New reports are created in the Report tab in the Database window Click on the Report tab Click on the New button T tf anssen toron i Mem The New Report dialog box will open up The New Report dialog box is used to zik choose which type of report you wish to create 4uUboRepork Columnar Select Report Wizard nae are This wizard automatically Err i creates your report based You must now choose which table or S query you wish to base the report on In the Choose a Table or Query drop down list select Choose the table or query where TE the object s daka comes From the table or query you wish to base the report on Click OK A dialog box will appear which is used to select which fields are shown to be on the report The box on the left displays all the fields in the table or query you are building the Available Fields Selected Fields report from The box on the right will contain only the fields you actually wish to include in the report e Click in the Available Fields list to s
12. tim Tiny 06 09 1975 E Michelle Green 07071960 Record 14 4 1 r gt oF 24 You may now want to print the query You may also want to save the query To Print The Query e Click on the Print button The query will be sent straight to the printer To Save The Query e Click on the Save button The Save As dialog box will appear Save As cu ne Cancel gry Dates of Birth Query names can be longer than 8 characters and they can have spaces in them It is good practice to save all queries with a name beginning with the letters qry e Enter a name for the query Click OK DA BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 To Close A Query e In the File menu select Close The query will then be visible in the Query tab in the Database window g Staff2000 Database Iof x Objects gry Dates of Birth Tables FAS gry Mumber of Staff at Each Office Fee gry Staff Trained on Powerpoint Ez ai Queries ZE Forms Reports j Fages Macros res Modules Groups To Open A Query Each time you open a query it displays the most up to date information from the underlying tables Select the Query tab in the Database window to view the existing queries e Select the query you wish to open Click on the Open button Changing A Query If you wish to redefine the fields or criteria used in an existing query you will need to ope
13. to navigate around the table To Hide A Column s e Select the column s In the Format menu select Hide Columns The selected columns will now be hidden To Unhide Hidden Columns In the Format menu select Unhide Columns The Unhide Columns dialog box will appear Unhide Columns zix This contains a list of all your column Column Staff ID Firstname headings The column headings without a tick by them are not showing in the table al Kl Lastname M Department e Click on the box next to the Column M office Heading to add a tick Dok O o Repeat for each column you wish to Fath unhide then click on the Close en ress button i ral Town To Resize A Column Columns are resized using the right side of the column selectors at the top of the column pSt D Eirstnam Lasiname Deparment 1 Mlartin Smith JA 7 2 Jenny Jones TER Service Kensington i 3 Betty Bond Housing Service Kensington i A Jeannie Jones IT Grove a 5 James Brow Marketing Grove Position the cursor at the right side of the column selector for the column you wish to resize Click and drag to the right to increase the width or to the left to decrease the width To Move A Column You can change the order of the columns in a table Click on the column selector to select the column and then release the mouse button e Click and drag on the column select
14. to the right of the flashing line You can also use the left and right arrow keys to move left and right one character at a time through the text Type in the appropriate changes The changes made to any row are automatically saved when you move out of that row When you edit a row the symbol appears in the record selector for that row This symbol means that the row is being edited When you move out of the row the edit symbol disappears and the changes to that row are saved Creating A New Record e Click on the New button i You will be moved to the empty row at the bottom of the table You can use the Tab key to move through the fields in the row Type in the new data TI P You can use the Ctrl command to automatically enter the same text into a field that appears in the field directly above Drop Down Lists A new feature in Access 2000 allows you to create a drop down list in a table If a drop down list has been Staff ID Firstnam Lastname Department Office _ Extensioy created for a field an arrow will 1 Martin Smith IT 215110 appear when you click in that field e Click on the arrow to Staff ID Firstnam Lastname Department Office Extension 1 Martin Smith IT bring Up the full list 2 Jenny Jones Housing flat 3 Betty Bond Housing flat E Click in the list to select 4 Jeannie Jones IT kensington the appropriate value 5 James Brown Marketing Longfield 6 Paul Mason Finance Newha
15. 63 F E 13 Tim Tiny Housing Services Windmill 5093 06 09 1975 M Record Record J 7 _ gt on fv of 28 gt Horizontal Selector P gt Datasheet view rf T_T Nmf Scroll Bar Title Bar The blue Title Bar contains the name of the application Microsoft Access and the name of the table currently open Menu Bar The Menu Bar allows you to access all the commands that are used to make things happen in your database Toolbar The Toolbar contains buttons that are shortcuts for actions that can otherwise be performed through the menu commands BASIC ACCESS 2000 7 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED Maximise Minimise amp Close Buttons the Maximise Minimise and Close buttons are used to control the size of the Access window The Minimise button is used to minimise the window down to a small button on the Taskbar at the bottom of the screen Click on the button to open the window up again The Maximise button is used to maximise the size of the Access window to make it fill the screen The button will then change to the Restore button Click on the Restore button to return the window to its previous size The Close button is used to exit out of Access Row Each row in the table represents one Record of information For example in our Staff List table each record contains information about one member of staff Column
16. Access 2000 Basic User Manual Ebit Solutions Limited september 1999 Ebit Solutions Limited www ebitsolutions net IT Support London Free Microsoft Office Training Manuals EBIT SOLUTIONS LIMITED TABLE OF CONTENTS INTRODUCTION What Is A Database Multiple Users This Manual NEW FEATURES IN ACCESS 2000 STARTING ACCESS 2000 To Close A Database To Open Another Database To Exit Out Of Access THE DATABASE WINDOW TABLES To Open A Table Title Bar Menu Bar Toolbar Maximise Minimise amp Close Buttons Row Column Vertical Scroll Bar Horizontal Scroll Bar VIEWING THE DATA Navigation Commands SUBDATASHEETS To View The Subdatasheet To Hide The Subdatasheet SELECTING DATA ENTERING DATA To Edit An Existing Record Creating A New Record Drop Down Lists Tick Boxes To Delete A Record HIDING SHOWING AND RESIZING COLUMNS To Hide A Column s To Unhide Hidden Columns TO RESIZE A COLUMN To Move A Column PRINTING TABLES To Print The Whole Table To Print Part Of The Table PAGE SETUP OPTIONS To Set Landscape Or Portrait Paper BASIC ACCESS 2000 SEPTEMBER 1999 ona A N a m m a CODWOWAOONNNNN o gt o MAY 1999 PRINT PREVIEW To Close Print Preview COPYING DATA TO WORD OR EXCEL Copying Data To Word Copying Data To Excel FINDING DATA To Find Data FIND AND REPLACE To Find And Replace Data SORTING THE TABLE To Sort The Table QUERIES Sorting Criteria Calculations CR
17. Data to Word or Excel These are the techniques for selecting data One Record Click on the record selector at the beginning of the record Several Records Click and drag down across the record selectors Every Record In the Edit menu select Select All Records Or use the Ctrl A command One Column Click on the Column Selector containing the field heading at the top of the column Several Columns Click and drag across the field headings at the top of the columns One Field Position the cursor at the very beginning of the field so that the cursor changes to a white cross and click Several Fields You can use the Excel method of dragging across the fields to select them but only if you position at the very beginning of the first field so that the cursor changes to a white cross Then click and drag across all the fields you wish to select Alternatively you can click in the first field then hold down the Shift key and click in the last field BASIC ACCESS 2000 11 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED ENTERING DATA You can create new records at the bottom of the table or edit existing records To Edit An Existing Record Click in the field you wish to edit to insert the flashing Text Insertion Point You can then use the Backspace key to delete text to the left of the flashing line or the Delete key to delete text
18. EATING A QUERY To Create A New Query To Print The Query To Save The Query To Close A Query To Open A Query Changing A Query Switching Between The Query Datasheet View And Design View USING CRITERIA IN A QUERY To Use Criteria In A Query CRITERIA EXAMPLES Operators Wildcard Characters TEXT EXAMPLES NUMBER EXAMPLES DATE EXAMPLES THE SHOW BOX To Hide Fields In A Query SPECIFYING MULTIPLE CRITERIA SEVERAL CRITERIA IN SEVERAL FIELDS SEVERAL CRITERIA IN ONE FIELD Examples Of Several Criteria In One Field SORTING A QUERY To Sort A Query SORTING ON SEVERAL FIELDS PERFORMING CALCULATIONS IN A QUERY To Perform A Calculation In A Query Examples USING AN ALIAS IN A QUERY EBIT SOLUTIONS LIMITED 16 16 17 17 18 18 19 19 29 29 29 29 30 30 30 31 32 32 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED FORMS To Open A Form USING A FORM To Move Between Records To Move Between Fields To Enter Data To Close A Form FIELD CONTROLS Text Box Drop Down Menu Combo Box Scrolling Menu List Box Option Buttons Check Boxes A MAIN SUBFORM To Move Between Records On The Main Form To Move Between Records On The Subform To Close The Form REPORTS TYPES OF REPORT Columnar AutoReport Tabular AutoReport Report Wizard Label Wizard Chart Wizard CREATING AN AUTOREPORT SAVING CLOSING OPENING AND PRINTING REPORTS To Save A Report To Close A Report To Open A Report To Print A Report
19. ESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 PERFORMING CALCULATIONS IN A QUERY You can use a query to calculate totals averages or to count the number of records that satisfy certain criteria You cannot create a query that lists all the data AND performs a calculation at the same time To perform a calculation in a query you must decide which field you wish to group the information by eg by office department or sex etc and which field contains the data you wish to total average or count If you wish to total everything you should put only the field containing the values you wish to total in the query To Perform A Calculation In A Query e Create the query in the normal way Field and add the field you wish to group Table the data by and the field you wish ies Awg to perform the calculation on aaa Criteria Click on the Totals button or The Total row will appear in the query grid The Group By function will appear in this row for each field Click in the Total row for the field you wish to perform the calculation on A drop down arrow will appear Group E Click on the drop down arrow A list of all the available calculations functions will appear Sum calculates a total Avg calculates an average Count counts the number of records Select a function Click on the Datasheet View button to view the query results 22 000 00 20 000 00 19 Os ff 16 000 00
20. Each column in the table contains Fields The information in the fields in one column all relate to one fact or type of information For example in our Staff List table each field in the third column contains the surname of each member of staff At the top of each column is a Field Heading describing the type of data stored in the column Fields B amp H tbl Staff List Table ON x _ Staff ID Firstname Lastname Department Office Extension DoB DODE 1 Martin Smith IT Grove 5110 12 04 1970 2 Jenny Jones Housing Services Kensington 4967 04 08 1972 3 Betty Bond Housing Services Kensington 5104 05 07 1965 4 Jeannie Jones IT Grove 5103 21 03 1962 5 James Brown Marketing Grove 4611 17 05 1960 Records 6 Paul Mason Finance Grove 4599 16 01 1957 7 James Dean Furniture Windmill 4563 03 03 1963 6 Jane Parker Communications Grove 5387 09 01 1963 9 Julie Newman Finance Grove 5993 20 01 1971 10 Robert Stroppendrop Fundraising Grove 5231 05 05 1959 11 Cassiana Calamari Admin Grove 5400 16 04 1970 12 Candice Berqdort Housing Services Ealing 5722 18 04 1963 gt Record 14 4 1 gt gt gt of 28 4 ONOHOHNOHOHOHOHOHNOHNG For example in our Staff database the Staff List table contains information about the people working at a company each row in the table contains data relating to one person and each column will contain data relating to different facts about each person such as Surname Department Date
21. HD W5 BUF labels using the standard Avery label sizes In Access 2000 you can also 17 Favier Road Dobbin House 18 Dibney Road ow Road create customised labels in case sri are e BY 9HJ BE iTR you are not using Avery labels See section on Creating Mailing Labels C h a rt W i Za rd Average Salary by Department 25 000 4 The Chart Wizard creates a chart based on some numerical data 20 000 15 000 Creating a Chart Report is not ewan covered in this manual aoe lt takes a lot of tweaking to get a chart to look good so would Suggest exporting the data to Excel and creating the chart in Excel see section on Exporting a Report to Word or Excel Fundralsing Hours Ing Serice Human Re sources T Marketing Tem porary Housing Training Communications New and existing reports are accessed through the Report tab in the Database window d Staff2000 Database O x E preview BE Desian Wy New x By Se EE E Objects E rpt Staff Phone List Tables E rptStaff Trained on PowerPoint ss rpt Staff Trained on Word Basic az Queries amp 3 Forms Reports A Pages Macros ei Modules Groups BASIC ACCESS 2000 39 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED CREATING AN AUTOREPORT New and existing reports are accessed through the Report tab in the Database window r Click o
22. a web form allowing people to input data into an Access database via the Internet or Intranet using a web browser such as Microsoft Explorer Z pge Staff List Microsoft Internet Explorer iof x l Fie Edit View Favorites Tools Help os 2 29 2 amp 2 2 2 a 9S e j Back Forward Stop Refresh Home Search Favorites History Mail Print Address E M SHARVEYSTRASHOniine Staff Details htm v Go Links gt Staff Details Lastname Firstname Department Office Extension fadamsp o Keine SS tCS S S raining Grove 5637 Ethnic Salary Address Town County white E20 000 00 Flat 2 Waverley Place London Inwe 3JN Telephone 0181 457 9887 WO tbl Staff List 1 of 24 gt Aay BL ZLYY B Sex F Dob 29 03 1969 Postcode ke 4 Local intranet BASIC ACCESS 2000 3 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED STARTING ACCESS 2000 Access 2000 can be started up using the Start button Click on the Start button select Programs and then select Microsoft Access The Microsoft Access window will open up Microsoft Access Of I File Edit View Insert Tools Window Help Ci tar Wel Se Bs oe Ea of at e ea Hp and then a dialog box will appear asking you if wish to open an existing database or create a new one This manual does no
23. an one table create a query that extracts the relevant data first then base the new report on that query 40 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 SAVING CLOSING OPENING AND PRINTING REPORTS The finished report appears in its P p i i B File Edit View Tools Window Help own Print Preview window x 5 SOMOS cose P ae You can use the controls at the bottom of the screen to move through the pages in the report staff Phone List Previous Page Next Page POT EC EPG T PEPE EE ea F i F TT ERT ep Tie rie ijira PTT ERE cee Page Li t Vike AS First Page Last Page TT ET i TE Ready J jl 4 If you want to see part of the page in more detail you can click on the page to zoom in Click again on the page to zoom out lf the report has just been created then it will need to be saved To Save A Report o In the File menu select Save The Save As dialog box will appear It is good practice to start all your report names with the letters rpt eg rpt Staff Phone List Enter a name for your report Save As mE o Click OK Report Name a Cancel The report is now saved with that name and is visible in the Report tab in the Database window To Close A Report In the File menu select Close To Open A Report In the Report tab in the Database Window Click to select the report and then
24. ase this is the name the report is saved as not a title Enter a name for the report Click Finish The label report will open in a Print Preview window You can now print and close the report To Print The Report e Click on the Print button To Close The Report 50 Click on the Close button Close Label Wizard Label Wizard EBIT SOLUTIONS LIMITED You can sort your labels by one or more fields in your database You might want to sor by more than one field such as last name then first name or by just one field such as postal code Which fields would you like to sort by Available fields Sort by ea ES aei Sex Ethnic x Cancel lt Back Finish What name would you like for your report ret Staff Address Labels That s all the information the wizard needs to create your labels What do you want to do c Modiy the label design T Display Help on working with labels Cancel lt Back A Microsoft Access rpt Staff Address Labels Report B File Edit View Tools Window Help X 6 COBB Close F 0a 0 Page id lt 1 i ey oc es Fe J 4 The new label report will be displayed in the Report tab in the Database window BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 CONVERTING A DATABASE You can open an Access 2 or an Access 97 database in Access 2000 But the database will op
25. ata you wish to find and replace and you wish to search the whole column from the top Click in the first cell the column you wish to search Click on the Find button 4 The Find dialog box will appear Select the Find and Replace Replace tab Find Replace In the Find What Find What Grove box enter the text you wish to find Cancel Replace With rove House z Replace e Inthe Replace With z emee box enter the text book Ini office ed _ Replace Al you wish to replace Match whole Field More gt gt it with e Click on the Find Next button to find the next instance of the Find What text Click on the Replace button to replace it with the contents of the Replace With box T pP You can click on the Replace All button to replace every occurrence of the text Click on the Cancel button to close the dialog box BASIC ACCESS 2000 19 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED SORTING THE TABLE The Sort facility allows you to sort your data into alphabetical or numerical order There are two sort buttons on the Table Toolbar Sort Sort Ascending Descending TEAN Sort Ascending Sorts text alphabetically from A to Z Sorts numbers from lowest to highest Sorts dates from earliest to most recent Sort Descending Sorts text from Z to A Sorts numbers from highest to lowest
26. ature If two tables are linked together in a one to many relationship you can actually view the data from the sub table in the main table BH tbl Staff List Table Of x MD Firsmame Lastmame Department _ __office Extension BoB SexfEthnige 1 Martin Smith IT Grove 5110 1204 1970 M Black 2 Jenny Jones Housing Services Kensington 4967 04 08 1972 F White Bond Housing Services Kensington 5104 05 07 1965 F White Attendance ID Course 14 01 1998 Yes 10 Windows 11 Word Basic 15 01 1998 No 0 Sick 12 Word Basic 24 03 1998 Yes 0 13 Word Intermediate 02 06 1998 Yes 15 14 Word Advanced 27 10 1998 Yes 15 AutoNumber _ 0 4 Jeannie Jones IT Grove 5103 21 03 1962 F Other Marketing Grove 4611 17 05 1960 M Black Subdatasheets in Tables 5 James Brown Printing The Print button now prints out the whole of the current object ie table query form or report without opening the Print dialog box Page Setup Print Setup is now called Page Setup in the File menu This is where you go to change the page orientation size and margins Print Relationships You can now print out the relationships between the tables in your database Converting Old You can covert old Access databases to Access 2000 You can also convert an Access 2000 database down to Access 97 if you need to share it with an Access 97 user Databases Access Pages Pages are a new type of Access object like tables queries and forms A Page is like
27. click on the Open button To Print A Report e When the report is open click on the Print button on the toolbar The whole report will be sent to the printer If you only want to print part of the report you should use the Print command in the File menu to bring up the Print dialog box and then specify the appropriate print range BASIC ACCESS 2000 41 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED EXPORTING A REPORT TO WORD OR EXCEL If you do not like the layout or formatting of the report you can edit it in Design view but those steps are not covered in this manual Alternatively you can export the report to Word or Excel and then use the formatting options in either program to customise the report EXPORTING A REPORT TO WORD When the report is open on screen Click on the drop down arrow on the right hand side LF Be of the OfficeLinks button which might look like or In the drop down menu that appears select Publish it with MS Word Word will open up with a new document containing your report as a tabbed table You can now use the normal Word formatting options to change the layout of the report and then print and save it as a Word document TI p You can convert a tabbed table into a proper Word table by selecting the table and clicking on the Insert Table button HE If you wish to kee
28. de by all the users will be saved into the same database This avoids the need to duplicate data as you can create one single database that everyone can access at any time This Manual This Basic manual is aimed at people needing to use an existing Access 2000 database If you need to create a new database you should read this manual and then read the Intermediate manual This manual assumes a knowledge of Windows NT A familiarity with Excel will also be useful All the pictures in this manual are based on a demonstration Access 2000 database called Staff2000 you can request a copy of the database from the IT Training team All the important instructions in this manual appear in bold Toolbar button and Menu commands appear bold and in quotation marks Keyboard instructions are bold and in square brackets For example In the Edit menu select Undo or use the combination command Ctrl Z BASIC ACCESS 2000 1 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED NEW FEATURES IN ACCESS 2000 If you are upgrading from Access 2 0 you will find that Access 2000 looks slightly different to begin with but works in the same way These are some of the basic new features in Access 2000 Feature Description of Change Toolbars Toolbar buttons are now flatter Dor el S Be Ss In an attempt to simplify the drop down menus less popular commands do not initial
29. e database Tables are used to store all the information that is contained in the database Queries Queries are the questions that you ask your database about the data stored in it To run a query is to extract a particular set of information from the database Forms are used to provide an easy way to view or enter data into the database You can input data straight into the Tables But Forms can be created containing colours design elements and drop down menus that make it easier to enter new data Reports Reports are used to print out data from your database You can print out Tables and Queries as well but Reports can be formatted to produce more professional looking documentation Pages are Internet Intranet Forms They can be used to input data into an Access database via the Internet or Intranet using a web browser such as Microsoft Explorer Macros are an advanced way of automating common tasks in the database Modules Modules are an advanced way of automating common tasks in the database using Visual Basic programming 6 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 TABLES Tables are the main part of the database Tables are used to store all the information that is contained in the database You can have many tables in a database and you can tell Access the relationships between the data in each table so that it can link all the tables together Access Tables look similar to Excel spreadsheets a gr
30. each selected field is located in Sort Row is used to sort the extracted data into alphabetical or numerical order see section on Sorting a Query Show Row is used to show or hide the data for a particular field This is used in conjunction with Criteria to enable you to filter data using a particular field without showing data from that field in the final query see section on Criteria Criteria Row is used to extract only certain records in a particular field see section on Criteria To choose which fields are displayed in the query you add the field names into the Field Row in the grid Click and drag each field name from the Field list into the first empty cell in the Field Row Having chosen all the fields that you want to appear in your query you can run the query Click on the Datasheet View button on the toolbar to view the query results The query datasheet will appear BASIC ACCESS 2000 23 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED ce Query Select Query _ OR x _ Firstname Lastname DoB P Martin Smith 12 04 1970 Jenny Jones 04 08 1972 Betty Bond 05 07 1965 Jeannie Jones 21 03 1962 C James Brow 17 05 1960 Paul Mason 1601 1957 James Dean 03 03 1963 C Jane Parker 09 01 1963 Julie Newnan 2001 1971 U Robert Stroppendrop 05 05 1959 E Casslana Calamari TBA So I Candice Bergdorf 18 04 1963
31. either i r F ascending or descending order one of the fields included in the report 1 Lastname 8 as T eT A fa You can actually set up to four sort levels e Click in the first sort box and select a field to sort by The button on the right of the sort box Smertene allows you to choose and Ascending A to Z sort or Descending Z to A ema Eish If you have included a number field in the report you will be able to perform Statistical Analysis on the data using the Summary Options e Click on the Summary Options Summary Options button The Summary Options dialog box will appear BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 There are four calculations available Sum calculates a total Avg calculates an average Min displays the minimum value Max displays the maximum value Select the appropriate calculation Click OK You will then return to the sorting dialog box Click Next You can now choose a Layout style for the report Select a Layout style sek a eee ee You can also choose the Orientation of oO mom ne the report ee an Select Portrait or Landscape ee a mene KARRA MEMRI MARR REXAR KARMA MaRa Click Next You can now choose a format style Select a format style TT l j Casual Click Next a ear Formal Label from Detail Control from Deta
32. elect a field e Click on the Add button gt to add the field to the box on the right TIP You can double click on a field to add it to the selected fields list Which fields do you want on your report fou can choose from more than one table or query BASIC ACCESS 2000 43 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED Repeat these steps for each field you wish to add The order that you add the fields determines the order in which they will appear on the report The top field becomes the first column on the page If yoy make a mistake you can select a field and remove it using the Remove button OTIP To add all the fields in one go click on the Add All button 44 When you have added all the fields you wish to display on the report Click on the Next button Another dialog box will appear A na want to add any grouping You can now choose to group the data iaaa pean by one of the fields included in the report Lastname Firstname Lastname Salary Select the field you wish to group a by Priority ica Click on the Add button gt Click Next Grouping Options Cancel lt Back Finish Another dialog box will appear What sort order and summary information do you want for detail records You Can Now choose to sort the data by You can sort records by up to four fields in
33. en Click on the Open button The Form will open up BS Staff2000 Database Misi Gpen b Desan aben Ee ee Fee Objects FA Tables Frm Staff Training Frm Staff Details Ei Queries 3 Forms Reports fal Pages Macros fe Modules Groups Form design is so versatile that two Forms could be made to look very different from each other Thus it is hard to document the Forms you may have to work with But there are certain elements that are present on most Forms regardless of the style of the Form BASIC ACCESS 2000 33 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED USING A FORM A Form displays information for one record at a time from a particular table or a combination of tables Each form contains Fields that link directly to the fields in the underlying table The Field Label describes the data stored in the field E Staff List Form iol x siui Bizi talig g Firstname Martin Date of Birth 24A 470 Field Lastname Smith Sex Male Label i Department ji Ethnic Black African Field Office Grove Car User v Extension E 10 Record _ __ gt Record 14 4 j iF r of 26 Selector The information entered into the form fields are entered into the corresponding table fields enabling you to use the form to view and edit the data in the underlying table To Move Between Records
34. en in read only mode This means you can view the data but you cant create any new tables queries forms or reports or modify any existing ones To do that you will need to convert your database to Access 2000 If the database is simple ie it does not contain lots of clever features using Access Basic programming it should convert easily If in doubt contact the IT Helpdesk or the person who originally created the database To Convert A Database To Access 2000 Open Access 2000 but do not open a database In the Tools menu select Database Utilities then Convert Database then To Current Access Database Version The Database to Convert From dialog box will appear Navigate through the folder structure and select the database you wish to convert Click on the Convert button An identical looking dialog box will appear This one is used to save a copy of the converted database In the Convert Database Into dialog box Enter a name for the converted database and select a folder to save it in e Click on the Save button A new copy of the database will be created that is Access 2000 compatible The original database will still exist in the old format You should then open the converted database in Access 2000 and check to see that it functions properly and contains all the correct data Do not delete the old database until you are certain there are no problems with the
35. ialog box oe select MS Access 5 Databases Navigate through the A appropriate folders and Favors select the database File name MS Query Cancel e Cli ck Open Files of type ms Access Databases mdb mde gt T Select method The Microsoft Access dialog will Microsoft Access mE open containing a Tables tab Tables Queries listing all the tables in the Tables in Staff2000 mdb database and a Queries tab Seas listing all the queries Select the table or query you wish to merge with Click OK Cancel view SQL You will then asked if you wish to Edit the Main Docu ment to insert P Word found no merge fields in your main document Choose the Edit Main Document the Merge Fields yf button to insert merge fields into your main document Click Edit Main Document Your Word document is now connected to the Access database Follow the usual steps to insert your merge fields into the document and then merge the document with the database BASIC ACCESS 2000 47 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED CREATING MAILING LABELS IN ACCESS lf your database contains names and addresses then you may wish to create mailing labels You can either do this in Word using a Mail Merge that links to your Access database see previous page or you can create a report in Access to produce the mailing labels
36. id of intersecting rows and columns To Open A Table The database tables are visible in the Tables tab in the Database window Click on the Tables tab E Database Ex i E tbl Staff List S Tables tbl Training Attendance a Queries e Select the table amp Forms Reports Click on the Open button 3 Pages ES Tables Macros The Table will open up containing many basic windows elements that you will be familiar with from Word or Excel fs Modules Groups Title Bar Menu Bar Column Field Minimise Maximise Close Microsoft Access U Staff List Table T File Edit Yiew Insert Format Records Tools Winddw Help ME 6RY see S gt Aya Yay mK Dal g Toolbar Sa Fane Taaa T Toman T O Tex Sex 1 Martin Smith Grove 5110 12 04 1970 M 7 2 Jenny Jones ETT Services Kensington 4967 04 08 1972 F Row i E 3 Betty Bond Housing Services Kensington 5104 05 07 1965 F Record 4 Jeannie Jones IT Grove 5103 21 03 1962 F 5 James Brown Marketing Grove 4611 17 05 1960 M l E 6 Paul Mason Finance Grove 4599 16 01 1957 M 7 James Dean Furniture Windmill 4563 03 03 1963 M Vertical 8 Jane Parker Communications Grove 5387 09 01 1963 F _E 9 Julie Newman Finance Grove 5993 28 01 1971 F Scroll Bar BE 10 Robert Stroppendrop Fundraising Grove 5231 05 05 1959 M 11 Cassiana Calamari Admin Grove 5488 16 04 1970 F l E 12 Candice Bergdorf Housing Services Ealing 5722 18 04 19
37. il BASIC ACCESS 2000 45 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 You can now choose a title to appear at the top of the report Enter a title for the report Unfortunately the report will also be saved with this title as the report name but you can rename the report later e Click on the Finish button The report will appear in the Print Preview window You can now print and close the report To print the report Click on the Print button To close the report e Click on the Close button Close TO RENAME A REPORT EBIT SOLUTIONS LIMITED What title do you want for your report Staff Salaries by Department That s all the information the wizard needs to create your report Do you want to preview the report or modify the report s design Preview the report Modify the report s design I Display Help on working with the report Cancel lt Back Hery Finish B File Edit view Tools Window Help iM 4 COOB r cose BF 0 a 0 Page lt 1 gt orf Fe N a od a WL a Reports can be renamed in the Report tab in the Database window Click once to select the report you wish to rename Click again on the report name This will highlight the whole name a Staff2000 Database ith Preview RE Design YB New x 2a Ten g Objects E rpt Staff Phone List Mi x E rpt Staff T
38. keyboard navigation commands that can be used to move around the table Tabi Shihab Home Ena Tewe Home uefa Page Un Page Down cone BASIC ACCESS 2000 9 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED SUBDATASHEETS Subdatasheets are a handy new feature in Access 2000 lf a table is linked to another table you can view and edit the data from both tables in the main table The expand subdatasheet control at the beginning of a record the subdatasheet for that record allows you to view tbl Staff List Table po Sati Eismame Lasmame Department Office Extension DeB Sze Ethnic 1 Martin Smith IT Grove 5110 12041970 Male Black African 2 Jenny Jones Housing Services Kensington 4967 DADE TO Female White Bel x 4 Betty Bond Housing Services Kensington S104 05071965 Female White Attendance ID Course _ Date Attend Late Comment C 10 Windows 4011990 Yes 11 Word Basic 1540171995 No 12 Word Basic 2405 1998 Yes 13 Word Intermediate 0A061995 Yes 14 ord Advanced 27110 1995 Yes AutoNumber m t 4 Jeannie Jones IT Grove 5103 21031962 Female Other E 5 James Brown Marketing Grove 4611 17 05 1960 Male Black African fe aa 6 Paul Mason Finance Grove 4599 164011957 Male White ee t f James Dean Furniture Windmill 4565 03031963 Male Other M Oo Jane Parker Communications Grove 5307 09011963 Female White E t g Julie Ne
39. limit the query to only staff whose Office is Grove you must enter the text Grove into the Criteria row underneath the Office field it will change to Grove You can enter the text in a variety of different formats that will all work These are Grove Grove Grove Grove You can also enter numbers into the Criteria Row in the format 100 100 You can also enter dates into the Criteria Row in the format 16 1 98 16 Jan 98 16 1 98 16 1 98 16 1 98 To Use Criteria In A Query In the QBE grid in the Query window Enter the criteria text number into the Criteria Row underneath the field name that it relates to e Click on the Datasheet View button on the toolbar to view the query results The query will appear in its own window The next section contains examples of how criteria can be used 26 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 CRITERIA EXAMPLES You can use text numbers and dates in criteria You can use Operators to look for data that is equal to greater than or less than certain values You can also use Wildcard Characters in criteria to represent any one character or any string of characters Operators Operators are used to specify whether you look for data that is equal to more than less than etc the criteria value These are the basic operators equal to lt gt not equal to gt greater than greater than or equal to lt less than lt less than
40. ly appear in each menu But you can click on the arrow at the bottom of the menu to view all the available commands Y Database Window The Database Window has been reformatted There is also a new database object called a Page a Staff2000 Database O x oe e tbl Staff List FH Tables tbl Training Attendance ai Queries EH Forms Reports a Pages Macros 3 Modules Groups Drop Down Lists in This is a useful new feature You can now create a drop Tables down list in a table to allow users to input values by selecting them in the list Previously you could only do this on a form Staff ID Firstnam Lastname Department Office Extension 1 Martin Smith IT 2 Jenny Jones Housing fiat a Betty Bond Housing wlat 4 Jeannie Jones IT Kensington 5 James Brow Marketing Longfield 6 Paul Mason Finance f James Dean Furniture Sorting The sort order of records is now saved when you save the layout of a table so that the records will appear in the saved order when you next open the table Two Digit Year Dates Access 2000 recognises two digit dates between the period 1930 to 2029 2 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 Feature Description of Change You can now format a field on a form so that its formatting changes depending on its current value Editing forms is covered in the Intermediate Access 2000 manual Conditional Formatting on a Form This is another useful new fe
41. m T James Dean Furniture Your selection will be entered into the field 12 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 Tick Boxes Check boxes in Access 2 0 tables have been replaced with tick boxes in Access 2000 tables These are used in fields which can only contain a yes or no value For example is the person a car user ia E ee ee 1 Martin omith IT ae 2 Jenny Jones Housing services Kensington 3 Betty Bond Housing services Kensington 4 Jeannie Jones Marketing Grove E A tick means yes an empty box means no e Click in the box to add and remove the tick To Delete A Record You cannot always delete records in a table It depends on how the table was set up or whether old data is kept for archiving purposes Ask the owner of your database if you should delete records Select the record e In the Edit menu select Delete or press the Delete key A dialog box will appear asking you if you are sure you wish to delete the record Microsoft Access x AN You are about to delete 1 record s If you click Yes you won t be able to undo this Delete operation Are You sure you want to delete these records Click Yes BASIC ACCESS 2000 13 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED HIDING SHOWING AND RESIZING COLUMNS lf there are a lot of columns in a table you can hide columns to make it easier
42. merical order You can sort a query by any field Criteria More complicated queries involve the use of Criteria Criteria are used to extract only records that match certain conditions For example you could run a query to extract the first name and surname of only the staff who work at the Grove office Calculations You can perform calculations in queries For example to create totals averages or count the number of records that match a certain criteria BASIC ACCESS 2000 21 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 CREATING A QUERY EBIT SOLUTIONS LIMITED There are four main steps to create a new query 1 Select the table s you wish to extract the data from Choose which data fields you wish to extract from the selected table s 2 3 Establish the criteria if any of the data you wish to extract 4 Run the query To Create A New Query 22 New queries are created in the Query tab in the Database window e In the Database window click on the Query tab Click on the New button ce Mew The New Query dialog box will appear It is more versatile if you create queries in Design View Select Design View Click OK The Add Table dialog box will appear This is used to choose which tables you wish to extract data from You can extract data from one table or from several e Click to select a table e Click on the Add button
43. n the Report tab g Staff2000 Database 5 x E Preview be Design E New x 2o oS EE iE Click on the New button Objects poses hens ie HA Tables rpt Staff Trained on PowerPoint B rptStaff Trained on Word Basic az Queries z Me 3 Forms B Reports G Pages Macros The New Report dialog box will open up cs Modules The New Report dialog box is used to New Report 2 x choose which type of report you wish to create Report Wizard AutoReport Columnar Autor t Tabul Select AutoReport Columnar or chatwead S Create a new report without Label wizard AutoReport Tabular using a wizard You must now choose which table or query you wish to base the report on Choose the tabl h In the Choose a Table or eee a fal Query drop down list select i JK i the table or query you wish to De base the report on Click OK The report will automatically be created ae i a File Edit View Tools Window Help la xl containing all the fields that appear in the w pommit as Ha 2 selected table or query Staff Phone List te FF The name of the table or query that the report is based on will automatically appear as a title at the top of the report cigEridpapesesa ARETA Page MOH 1 mF Rey o EO Nr TI p If you do not wish the report to contain all the information in a table or if you wish to create a report that is based on more th
44. n the query in Design view If the query is closed you can open it straight up in design view Select the query in the Query tab in the Database window Click on the Design button bf Design Switching Between The Query Datasheet View And Design View If the query is open you can switch between the query datasheet view and the query design view using these toolbar buttons Design View a Datasheet View Click on the Design View button to view the query design Click on the Datasheet View to view the query results BASIC ACCESS 2000 25 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED USING CRITERIA IN A QUERY Criteria are used to control and limit the amount of information that is extracted from the database when you run a query It is a way of filtering out the data you want from the data you don t want For example in our Staff database you could run a query to show the first names Surnames and office location of only the staff who work at the Grove office Criteria are entered into the Criteria row in the query QBE grid N Field Firstname Lastname Office Table tbl Staff List tbl Staff List tbl Staff List ea CRs I Sort Sa Show Criteria Row Criteria Or The text that is entered into the Criteria row limits the query to only records that match the text for that particular field For example to
45. of the data in your table into Word or Excel Copying Data To Word Use the Record Selectors or the Field Selectors to select the rows or columns you wish to copy Click on the Copy button Open Word In Word Click in the document text to choose the insertion point Click on the Paste button E The data will be pasted into your document as a Word table Copying Data To Excel In your Access table e Use the Record Selectors or the Field Selectors to select the rows or columns you wish to copy Click on the Copy button Open Excel In Excel Click in the spreadsheet to choose the insertion point Click on the Paste button ce The data will be pasted into your spreadsheet BASIC ACCESS 2000 17 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED FINDING DATA The Find facility allows you to locate specific data in your table quickly The Find facility also contains Find and Replace used to find all the occurrences of a particular word and to replace it with something else To Find Data If you know which field contains the data you wish to search for and you wish to search the whole column from the top Click in the first cell in the column you wish to search Click on the Find button rh The Find dialog box will Find and Replace ME appear Find Replace In the Find What Find What
46. of Birth Sex etc Vertical Scroll Bar The Vertical Scroll Bar is used to move up and down the table Click on the up arrow at the top of the bar to scroll up Click on the down arrow at the bottom of the bar to scroll down Horizontal Scroll Bar The Horizontal Scroll Bar is used to move left and right across the table Click on the right arrow to scroll right Click on the left arrow to scroll left 8 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 VIEWING THE DATA You can use the Vertical Scroll bar to scroll up and down the table If there are a large number of fields columns of data you can use the Horizontal Scroll Bar to scroll left and right across the table You can also use the Record Selector at the bottom of the screen to move between records Total First Previous Current Next Last New Number of Record Record Record Record Record Record Records NY AA Record u 1 gt bt e of 24 First Record Returns you to record 1 Previous Record Moves you back one record Current Record Displays the record currently selected You can click in this area and type the record number you would like to move to then press Return Next Record Moves you forward one record Last Record Moves you to the last record in the table New Record Creates a new record at the bottom of the table Total Number of Displays the total number of records in the table Records Navigation Commands There are some
47. of all staff who work at the Grove office or at the Windmill office T SS Ea tbl Staff List tbl Staff List tbl Staff List PP Pr ae Grove Or Windmill Field T able Sort Show Criteria or To specify multiple criteria in the same field use the And and Or operators Use And when you want to find records that match both criteria Use Or when you want to find records that match either criteria Enter the appropriate criteria into the same cell in the Criteria row separated by the And or Or operators Examples Of Several Criteria In One Field Expression Things Found By Query Grove Or Windmill All fields containing exactly Grove or exactly Windmill Like A And Like S Fields starting with an A and ending with an S gt 50 And lt 100 Numbers between 50 and 100 including 50 and 100 Between 50 And 100 Numbers between 50 and 100 including 50 and 100 Between 1 6 98 And All dates between 1 June 98 and 30 June 98 including 1 30 6 98 June and 30 June BASIC ACCESS 2000 29 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED SORTING A QUERY You can choose to sort a query by any text number or date field You can perform an Ascending sort on data or a Descending sort Ascending Sorts text alphabetically from A to Z Sorts numbers from lowest to highest Sorts dates from oldest to most recent Descending Sorts text f
48. or equal to Wildcard Characters There are two wildcard characters that can be used in criteria These are the and symbols When using wildcard characters you will also need to use the expression Like instead of the symbol otherwise the query looks for fields actually containing an asterisk or question mark represents any one text character For example using Like c t would bring up fields containing cut cot and cat represents any string of characters For example using Like work would bring up fields containing works worker workers working workings and worked TEXT EXAMPLES Expression Things Found By Query Grove All fields containing exactly Grove Not Grove All fields not containing exactly Grove BASIC ACCESS 2000 27 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED NUMBER EXAMPLES Expression Things Found By Query 50 Numbers equal to 50 gt 50 Numbers greater than 50 lt 50 Numbers less than 50 gt 50 Numbers greater than or equal to 50 lt 50 Numbers less than or equal to 50 DATE EXAMPLES Expression Things Found By Query 1 6 1998 Fields containing 1 June 1998 01 06 1998 Fields containing 1 June 1998 gt 1 6 1998 Dates later than 1 June 1998 lt 1 6 1998 Dates earlier than 1 June 1998 lt 1 6 1998 Dates earlier than or equal to 1 June 1998 THE SHOW BOX Just because you are using a field in your que
49. or to move the column 14 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 PRINTING TABLES You can print out a whole table or just a few records or columns of information To Print The Whole Table Click on the Print button To Print Part Of The Table Select the fields records or columns that you want to print In the File menu select Print The Print dialog box will appear Print HE Printer You can use the Print Range AIGROPPS2qit_ 4000 P ti options to control how much of the a _Broverties table is printed Status Ready Type HP LaserJet 4000 Series PCL 6 All prints the whole table Where IT 1st Floor North Pod Comment Print to File Selection prints just the selected Copies records or columns Number of Copies l Print Range i All Pages allows you to specify a e aa range of pages if the table is too big to fit on one page poe poe W Collate Select the appropriate Print m cci i Selected Record s Range Click OK PAGE SETUP OPTIONS You can change the page setup options to make the table print out on portrait or landscape paper To Set Landscape Or Portrait Paper In the File menu select Page Setup poo Su 21x Margins Page The Page Setup dialog box will appear orientation Click on the Page tab Porta C Landscape In the Orientation options select Portrait or Lan dscape
50. p this document or mail it to anyone else you need to save it as a Word document An exported report appears in Word as an Rich Text Format file rather than a Word document You must use the Save As command to save it as a Word document e In the File menu select Save As The Save As dialog box will appear Enter a File Name and choose the folder you wish to store the document in e In the Save As Type drop down menu at the bottom of the dialog box select Word Document Click OK EXPORTING A REPORT TO EXCEL If the report contains lots of numbers that you want to perform calculations on or if you need to be able to change the column widths of the fields you may wish to export it to Excel When the report is open on screen Click on the drop down arrow on the right hand side LF ae of the OfficeLinks button which might look like or o In the drop down menu that appears select Analyse it with MS Excel Excel will open up with a new workbook containing your report You can now use the normal Excel features to format and print the data When you save the workbook use the Save As command to bring up the Save As dialog box In the Save as Type box ensure that Microsoft Excel Workbook is selected not Microsoft 5 0 95 Excel Workbook 42 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 USING THE REPORT WIZARD The Report Wizard is used to create reports
51. r This counts the number of staff living in W6 You can change the headings that appear at the top of a query by using an AvgOfSalary Admin 17 S00 00 Communications 22 000 00 Field eraen RESE Salary Salar Avene Sal Sain Table thl Staff List tbl Staff List Total E EE Sort Show Criteria or Department Average Salary 17 500 00 22 000 00 Admir Communications BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 FORMS Forms are used to input or look up data in the database tables Forms are often used instead of Tables because they are versatile and can be customised to create a more user friendly screen in which to work Forms display fields for one record at a time Here is an example of a Form than could be Sep Baril ee rine oe 4 Firstname Matin s Date of Birth 127041970 Lastname Smith Sex Male Department IT ts Ethnic Black Afican Office Grove Car User Extension 5110s Record Aal gt on me of 26 ce This manual does not cover how to create new Forms see the Intermediate Access manual This chapter is designed to introduce you to the concept of a Form as any database you have to use will probably contain some sort of Form To Open A Form Existing Forms are visible in the Forms tab in the Database window Click on the Forms tab to view the existing Forms e Click to select the form you wish to op
52. rained on PowerPoint E rpt Staff Trained on Word Basic HH Tables az Queries E Forms i l Reports Pages Macros ces Modules You can either type a new name over the old name or click again to insert the flashing text insertion point and then edit the existing name using the left and right arrow keys and the Backspace and Delete keys Type a new name e Press Return 46 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 MAIL MERGE You can use an Access database to create mailing labels within Access Or you can use the database as the data source in a Word Mail Merge to create mailing labels and bulk letters USING WORD TO MAIL MERGE WITH AN ACCESS DATABASE If you are already familiar with mail merging in Word to create bulk letters or mailing labels then you might prefer to use this method to merge with a database The following steps relate to the part of a mail merge in Word that deals with linking to an Access database see Word Advanced manual for more instructions In Word having created your Main Document you will be ready to attach a Data Source e In the Tools menu select Mail Merge to bring up the Mail Merge Helper dialog box Click on the Get Data button and select Open Data Source The Open Data Source dialog Do ies _ lt 1 box will appear Look in ef 4 Staff2000 mdb In the Files of Type 6 drop down menu at the N bottom of the d
53. rom Z to A Sorts numbers from highest to lowest Sorts dates from most recent to oldest To Sort A Query Click in the Sort cell underneath the Field you wish to sort by This will bring up a drop down menu arrow El i T e a T Click on the drop Field down menu arrow T able E Staff List i Staff List tbl Staff List and select the Sot Ascending ee Ascending or Sed TET Descending sort or option When you run the query the resulting dynaset will be sorted by the field you specified SORTING ON SEVERAL FIELDS 30 You can sort by more than one field at a time This is useful if there is identical data in the main sort field for any records For example if you sort by surname if any people share the same surname you will then need to sort them by some other field ie by first name The priority of the fields 1st Sort Field 2nd Sort Field sorted is determined by their order in the QBE E E OA S AN E e e a E sort field The next sorted Show a field is the secondary sort ctf field etc a Ensure that the first field you wish to sort by appears in the QBE grid before the other fields you wish to sort by Click in the Sort cell underneath each Field you wish to sort by This will bring up a drop down menu arrow E Click on the drop down menu arrow and select the Ascending or Descending sort option When you run the query the resulting dynaset will be sorted by the fields you specified BASIC ACC
54. ry does not mean that you want to display it in the query results For example you may wish to create a query that shows the dates of birth for all staff at the Grove office without having to display the fields containing the Office data The Show boxes in the QBE grid control which fields are displayed in the query Each box is turned on by default for each field a a ST Field Firstname Lastname Dffice Table tbl Staff List tbl Statt List tbl Statt List Soe a Show Boxes gt Show Criteria or To Hide Fields In A Query e Click in the Show box to turn it off by removing the tick 28 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 SPECIFYING MULTIPLE CRITERIA A simple criteria will contain one criteria in one field But you can specify several criteria in one field or several criteria for several fields This allows you to create more complex queries SEVERAL CRITERIA IN SEVERAL FIELDS For example you could run a query to display the names of all staff who work at the Grove office and whose date of birth is after 1 1 65 a E Field Firstname Office Dob Table tbl Staff List tbl Staff List thl Staff List tbl Staff List B es ee Show TT OM Mm M fh Criteria Grove gt HOT an 1965H oF Enter the appropriate criteria in the Criteria row for each field you wish to filter by SEVERAL CRITERIA IN ONE FIELD For example you could run a query to display the names
55. staff member The subform record selector buttons move you through the records of the training courses attended by the staff member showing in the main form To Move Between Records On The Main Form Click on the left and right arrow buttons on the Main Form Record Selector To Move Between Records On The Subform Click on the left and right arrow buttons on the Subform Record Selector To Close The Form In the File menu select Close BASIC ACCESS 2000 37 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 REPORTS EBIT SOLUTIONS LIMITED Reports are used to print out data from the database in a format that is meaningful or simply more attractive to look at than a table Reports are based on a tables or queries Reports can also be used to perform calculations on data totals and subtotals and to create mailing labels Once a report has been created it will update to display the latest data in the database each time you open it TYPES OF REPORT There are five default report types that you can use to quickly create a report The Columnar AutoReport and Tabular AutoReport create the report for you automatically once you have selected the table or query you wish to base the report on The Report Wizard Chart Wizard and Label Wizard take longer to create the report but are more versatile A series of dialog boxes will appear to guide you through the crea
56. stom label or create a new one and input the label measurements If you wish to work in centimetres select the Metric option Click on the New button Label Type SheetFeed Continuous Unit of Measure E English Metric 48 BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED These label measurements SEPTEMBER 1999 will be saved for later use e Enter a name for the labels Enter the label measurements Click OK Click Close Click Next You can now specify the text COADA formatting for the labels Select the appropriate formatting Click Next You can now choose which LADANN fields appear on the labels The available fields are shown on the left Firstname Lastname The prototype label is Firsname z Address constructed on the right Deparment eee To add a field ag i Select the field and click on the Add button To add a space new paragraph or other punctuation Type the appropriate key on the keyboard When the prototype label is constructed Click Next BASIC ACCESS 2000 IT Support London Free Microsoft Office Training Manuals 49 www ebitsolutions net SEPTEMBER 1999 You can now choose a field to sort the labels by Select a field and then click on the Add button Ea Click Next You can now choose a name for the report In this c
57. t cover creating a new database see the Intermediate Access 2000 manual Select Open an Existing Database Create a new database using The four most recently used databases are shown in the list If the database you wish to open is shown in the list Select the database e Click OK If the database is not shown in the list you MAHAR YEYACCDEY PhotogFront can use the More Files option to locate all e a a other databases MHARVEYACCDEY Misc Dir95 MAHARYEYITRASHStaffe000 Select More Files Click OK The Open dialog box will appear BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 The Open Database dialog box allows you to open any existing Access database Open H Ei Look in E ittrain on grofps1 Ms i t Access Folder 19 08 1999 10 54 Acrobat Folder 16 11 1998 17 31 Convert Folder 16 11 1998 17 31 Excel Folder 16 11 1998 17 31 Exchange Folder 10 06 1999 11 42 Personal Harvey Folder 20 07 1999 12 01 Hou_man Folder 16 11 1998 17 35 Inductic Folder 14 12 1998 11 06 ae Internet Folder 15 01 1999 11 53 Desktop Intranet Folder 20 08 1999 10 53 Mail Folder 16 11 1998 17 36 Murray Folder 12 08 1999 09 54 Pictures Folder 21 05 1999 10 15 Powerpt Folder 16 11 1998 17 36 Project Folder 16 11 1998 17 36 File name fae Open Files oF type Data Files mdb adp mdws mda mde ade dk e Navigate through the folder struc
58. t types of field control that can be used in a form You will be familiar with these control types as they are consistently used across all Microsoft programs for making choices and inputting text in dialog boxes The most common field controls are text boxes drop down menus scrolling menus option buttons and check boxes Text Box The text box is probably the most common field control You can type text numbers or dates directly into the text box Lastname mith There may be restrictions on the text box to stop you from entering too much text or an Input Mask to force you to enter dates in the correct format Drop Down Menu Combo Box Drop Down Menus are used to provide you with a list of available options The list is accessed by clicking on the drop down arrow at the right of the box You then click on the appropriate option in the list to select it Office Grove 2 Office Grove If there is no appropriate option in the list you can type something different into the box at the top of the list Scrolling Menu List Box Scrolling Menus are used to provide you with Grove kensington Longfield Hewhamn Office a list of available options and to force you to choose one of those options Kensington ae Longfield If the list is long a scroll bar will appear on eham the right to enable you to scroll up and down the list Click to select the appropriate option Option Buttons
59. tion process These are the two AutoReports Columnar AutoReport The Columnar AutoReport displays fields in a single column spreading down the page See section on creating an AutoReport Tabular AutoReport The Tabular AutoReport displays each record as a row of fields See section on creating an AutoReport 38 Dates of Birth DoB 0401972 Firstrame Lastname DoB OS 07 1 965 DoB 21 05 1962 Staff Phone List F rsiname Lasiname Celine Adams Alice Baker Chadotte Baker Jenny Baker Torny Baker Candice Bergdorf Betty Bond Shelly Borbora Office Extension Grove 5637 Ealing 5195 Gow 5563 Give 5118 Give 5660 Ealing 5722 Kersington 5104 Give DA BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 These are the three report wizards Report Wizard Salaries by Department The Report Wizard is the most versatile wizard Department Lastname Firstname Salary Admin SOSOSOC S lt S 7TC TO lt can be used to create a report Baker his 98 90 that contains sorting grouping or Calamari Casina 16 500 H Su for De t dmin 2 detail records calculations a _ Communications See section on Using the Report i ae o Ro Wizard Summary for Department Communications 1 detail cord Sum 22 000 Label Wizard Celine Adams Alice Baker Charlotte Baker Flat2 Waverley Place 45 Salisbury Road 12c Murray Road z Fae London London London The Label Wizard creates mailing NWE 3JN N4 S
60. ture to select the folder the database is stored in The Access databases stored in that folder will be displayed Select the database Click OK The database will open up in the Database Window To Close A Database e In the File menu select Close To Open Another Database You can only have one database open at atime If you already have one open it will automatically be closed when you try to open another e In the File menu select Open The Open Database dialog box will appear Follow the steps shown above To Exit Out Of Access In the File menu select Exit BASIC ACCESS 2000 5 IT Support London Free Microsoft Office Training Manuals www ebitsolutions net SEPTEMBER 1999 EBIT SOLUTIONS LIMITED THE DATABASE WINDOW When you open an existing database the first window you will see is the Database window The Database window i Staff2000 Database mE ES contains the seven different ij open B Desin new X 20 We EEE elements of an Access tbl Staff List database Tables Queries E Tables tbl Training Attendance Forms Reports Pages Queries Macros and Modules E Forms Reports a Pages Macros fe Modules Groups Each element is accessed through a tab on the left of the window The Tables tab is currently selected so that all the tables of the database are visible Here is a brief description of each element Element Tables Tables are the key part of th
61. wman Finance Grove 5993 20011971 Female Black Carribeali E 10 Robert otroppendrop Fundraising Grove 5231 05 05 1959 Male Irish m 11 Cassiana Calamari Admin Grove 5406 16041970 Female Black African E 12 Candice Bergdorf Housing Services Ealing 5722 18041963 Female Black Carribear m 13 Tim Tiny Housing Services Windrnill 5093 060971975 Male Asian m 14 Michelle Green Human Resources Grove 5029 07071968 Female White w 15 Calin Firth Marketing Grove 4006 154071975 Male Black African Record 14 4 J 1 r r of 26 4 d To View The Subdatasheet Click on the Expand control for the appropriate record E To Hide The Subdatasheet 10 When the subdatasheet is showing the expand control changes to the collapse control Click on the Collapse control E BASIC ACCESS 2000 EBIT SOLUTIONS LIMITED SEPTEMBER 1999 SELECTING DATA You can select fields records and columns in any table The Field Headings are used to select columns The Record Selectors are used to select records Column Selector containing Field Heading Staff ID Firstname Lastname Department 1 Martin Smith IT Record 2 denny Jones Grove 3 Bethy Blond Housing Mat Selector 4 Jeanne Jones IT A James B rowr Marketing 6 Faul Mason Finance You will need to select data if you wish to delete it move it or copy it TI Pp You can copy and paste data from an Access table into any Word document or Excel spreadsheet See section on Copying

Download Pdf Manuals

Related Search

Related Contents

Especificaciones  AUDI MMI 3G Bedienungsanleitung Instruction manual  Galilée et l`astronomie  Samsung LT-P2035 Manuel de l'utilisateur  Handbuch Intellibox Basic  American Standard Electronic Air Cleaner User's Manual  MANUAL DE INSTRUCCIONES L:7 :9>I>DC  

Copyright © All rights reserved. Failed to retrieve file