[ Pobierz całość w formacie PDF ]
.e.the fields you wish toperform a calculation on and the fields you want to order by.Including otherfields will just confuse the calculation.TRAININGTo set up a The mechanics of setting up a summary query are simple: select the fieldssummary and criteria as normal and then specify what should happen in the Totalsgroup byquery line for each field.Normally you will select for one field and then acalculation for another field.In the example shown above, the query isgrouped by country and all UnitPrice fields for the records within eachcountry are totaled.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/ Creating Simple Queries 133Using Access 2000 Foundation © 1995-2000 Cheltenham Computer TrainingMultiple You can have any number of calculations in a summary query: you maycalculations want to group by country and then sum the UnitPrice field and see theaverage of the SalesQuantity field.You can have any number of groups by columns in a summary query: youmay want to group by country and within countries group by region.If youhave more than one group by column, then Access works from left to rightto determine the precedence of the groupings.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/ 134 Creating Simple Queries© 1995-2000 Cheltenham Computer Training Using Access 2000 FoundationUpdate QueriesUpdate of all entries in a column or&Update of all entries matchingthe specified criteriaArithmetic operationsTo specific values© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.99Update QueriesWhat is an Update queries allow you to quickly update a number of records in a table.update The records updated depend on the criteria that you specify.For example,query? you may wish to set the ReorderLevel to 10 for all the records in theProduct table.Alternatively, if you wished to set the ReorderLevel to 10 foronly those products beginning with the letter B, then you could specify thisSAMPLE ONLYin the criteria.To create an update query, click on the Query Type tool and select UpdateQuery.This adds another line to the query grid, entitled Update To.Specifythe rules for updating a column in this line or leave it blank for columnsNOT TO BEthat you do not wish to change.Sometimes it is useful to include othercolumns in the query, so that you can specify search criteria etc, but theseare columns that you might not wish to change.When you have specified the update rules, click on the Run tool (!) to actionUSED FORthe query.Access will display a message saying how many records are tobe changed and requesting confirmation that you wish to proceed.NB.Ifyou are performing an update query on a subset of the table, it is goodpractice to set up the criteria in a normal select query first, look at theanswer and check that the right records are being displayed, and thenTRAININGmodify the query to include the Update To statements.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/ Creating Simple Queries 135Using Access 2000 Foundation © 1995-2000 Cheltenham Computer TrainingThe expression that in the Update To line is built up using the standardAccess expression components:fieldname[ ] A valid field from one of the tables selected forthe query, enclosed by square brackets* Multiply/ Divide+ Add- Subtract() Parentheses are used to indicate order ofprecedence in a complex expressionYou can also use any other constants (i.e.numbers) or functions that are inthe Access library (e.g.you can use the Format function to format acalculated field as currency or a special layout of date).Examples 10[Price]*1.175[UnitPrice]*[OrderQuantity][StockLevel]-[QuantityOrdered]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/ 136 Creating Simple Queries© 1995-2000 Cheltenham Computer Training Using Access 2000 FoundationAppend QueriesAdd the results of a query to a table© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.100Append QueriesWhat is an Append queries copy the results of a query to a specified table.Theappend destination table must have the same fields as you have included in thequery? answer to your query.To create an append query, click on the Query Typetool and select Append Query.SAMPLE ONLYTo set up an You can specify the table that you wish to append to and whether it is in theappend query current database or in another database file.When setting up an appendquery, the selection of fields, criteria etc is performed in the normal way.When you have set up the query rules, click on the Run tool (!) to action thequery.Access will display a message saying how many records are to beNOT TO BEappended and requesting confirmation that you wish to proceed.Tip If you are performing an append query on a subset of the table, it is goodpractice to set up the criteria in a normal select query first, look at theanswer and check that the right records are being displayed, and thenUSED FORmodify the query to include the Append To statements.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 Simple Queries 137Using Access 2000 Foundation © 1995-2000 Cheltenham Computer TrainingMake-Table QueriesUse the results of a queryto make a new table© Cheltenham Computer Training 1995-2000 Access 2000 Foundation/Intermediate - Slide No.101Make-Table QueriesWhat is a Make-table queries create a new table and copy the results of the query toMake-table that table.Make-table queries operate in a similar manner to appendQuery? queries.To create a make-table query, click on the Query Type tool andselect Make-Table Query.SAMPLE ONLYThe new table name specified can be in the current.mdb file or in anotherdatabase.When setting up a make-table query, the selection of fields,criteria etc is performed in the normal way.When you have set up thequery rules, click on the Run tool (!) to action the query.Access will displaya message saying how many records are to be copied to the new table andNOT TO BErequesting confirmation that you wish to proceed.Tip
[ Pobierz całość w formacie PDF ]