[ Pobierz całość w formacie PDF ]
.TRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ 138 Creating Simple Queries© 1995-2000 Cheltenham Computer Training Using Access 2000 FoundationReview Questions© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.102Review Questions - How Would You.1.Explain the concepts behind the use of queries?2.Open an existing query?3.Create a query using the Query Wizard?SAMPLE ONLY4.Select fields that you wish to add to your simple query?5.Set up a calculated field?NOT TO BE6.Define an update query?7.Specify an append to query?8.Set up a make-table query?USED FOR9.Check the criteria are correct in an update/append/make-table query beforeactioning it?TRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ Creating Calculated Fields 139Using Access 2000 Foundation © 1995-2000 Cheltenham Computer TrainingCreating Calculated FieldsLearning Module ObjectivesWhen you have completed" Seen how to open the Expression Builderthis learning module you will" Seen how to create calculated fields, using thehave:Expression BuilderSAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ 140 Creating Calculated Fields© 1995-2000 Cheltenham Computer Training Using Access 2000 FoundationCreating Calculated Fields" It is often more efficient to calculate information(e.g.for a report) when it is needed rather thanholding it in a table" Instead of having a monthly pay field, youcould use an expression to calculate itfrom Salary divided by 12Monthly Pay: [Employees]![Salary]/12© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.104Creating Calculated FieldsBackground When designing tables, you avoid creating fields that hold derived orcalculated data.Usually it is more efficient to do the calculation when it'swanted.As employees' monthly pay is not held on the database, youcould calculate it by running a query to divide the salary by 12 (months inyear).SAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ Creating Calculated Fields 141Using Access 2000 Foundation © 1995-2000 Cheltenham Computer TrainingUsing the Expression BuilderExpression BoxCommonOperatorsObjectFoldersElementsthat can bepasted intoanexpressionElements that canbe pasted into anexpression© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.105Using the Expression BuilderBackgroundThe Expression Builder is a useful tool if you are not quite sure how toenter expressions directly." Open a database, (in this example we have opened the Northwinddatabase).SAMPLE ONLY" Click on the Query icon." Click on the New button and then select Design View, as illustratedbelow.NOT TO BEUSED FORTRAINING" Click on the OK button.SAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ 142 Creating Calculated Fields© 1995-2000 Cheltenham Computer Training Using Access 2000 Foundation" The Show Table dialog will be displayed as illustrated." Click on the table that you wish to extract information from.In this caseProducts." Click on the Add button and then click on the Close button and thescreen will resemble that shown below.SAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ Creating Calculated Fields 143Using Access 2000 Foundation © 1995-2000 Cheltenham Computer Training" Next we need to drag and drop the required fields into the query.DragProductName into the first field column and the screen will be asillustrated." Also drag UnitPrice into the second column, as illustrated.SAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ 144 Creating Calculated Fields© 1995-2000 Cheltenham Computer Training Using Access 2000 FoundationTo open the" Click in the first cell of the thirdExpressioncolumn.Builder" Click once using the right-handmouse button to display a pop-upmenu." Select the Build command, asillustrated." The Expression Builder window will be displayed." Lets say that we wish to discount the Unit Price by 5% and wish toknow the value that the item will be reduced by.A 5% discount isSAMPLE ONLYequivalent to dividing a value by 20.So what we need to do is to add anew field that take the UnitPrice field from the Products table, and thendivides it by 20.NOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ Creating Calculated Fields 145Using Access 2000 Foundation © 1995-2000 Cheltenham Computer Training" First we need to select the right table.Double click on Tables asdisplayed in the Expression Builder dialog box." Select Products, as illustrated below." Next we need to select the required field from the Products table, asillustrated below.SAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal.com/ 146 Creating Calculated Fields© 1995-2000 Cheltenham Computer Training Using Access 2000 Foundation" Click on the Paste button (displayed within the Expression Builderwindow) and the screen will be as illustrated below." Next we need to divide this value by 20.To do this click on the dividesymbol.The division symbol will be added to the end of theexpression that we have built." Enter the number 20 at the end of the expression line, as illustratedbelow.SAMPLE ONLYNOT TO BEUSED FORTRAININGSAMPLE ONLY NOT TO BE USED FOR TRAINING©Cheltenham Computer Training 1995-2000 - Tel: +44 (0)1242 227200 - Fax: +44 (0)1242 253200Email: sales@cctglobal.com - Internet: http://www.cctglobal
[ Pobierz całość w formacie PDF ]