霍莉

摘要

查詢作為Access數(shù)據(jù)庫六大對象之一，是Access數(shù)據(jù)庫中最重要的應(yīng)用查詢可以對數(shù)據(jù)進(jìn)行檢索、處理，數(shù)據(jù)處理中經(jīng)常會涉及到數(shù)據(jù)的計(jì)算。創(chuàng)建計(jì)算查詢是整個查詢對象的重難點(diǎn)之一，幾種方式容易混淆，計(jì)算查詢在Access全國計(jì)算機(jī)等級二級考試中也是必考知識點(diǎn)。本文對計(jì)算查詢中涉及到的查詢種類以及適用場合用具體的實(shí)例做了詳細(xì)分析。

【關(guān)鍵詞】Access數(shù)據(jù)庫 查詢設(shè)計(jì) 計(jì)算查詢

1 引言

Access數(shù)據(jù)庫對象有表、查詢、窗體、報(bào)表、宏、模塊。從數(shù)據(jù)庫最典型的應(yīng)用來說，查詢是Access數(shù)據(jù)庫中最重要的對象之一。它不僅可以能夠快速從不同的數(shù)據(jù)表中檢索中符合條件的記錄，還能對檢索結(jié)果進(jìn)行分析和處理。數(shù)據(jù)處理包括對數(shù)據(jù)進(jìn)行計(jì)算、統(tǒng)計(jì)，這類查詢統(tǒng)稱計(jì)算查詢。

2 計(jì)算查詢作用分析

在數(shù)據(jù)庫操作中，數(shù)據(jù)的統(tǒng)計(jì)、計(jì)算與檢索在日常工作中占很大的比例。近年來，計(jì)算查詢在每年的全國計(jì)算機(jī)等級二級Access考試中成為了必考知識點(diǎn)。

Access查詢分為選擇查詢、參數(shù)查詢、交叉表查詢、操作查詢、SQL查詢五種類型，有關(guān)計(jì)算的查詢沒有單獨(dú)歸類。其中參數(shù)查詢是一種特殊的選擇查詢，只是在選擇查詢的條件行上用方括號處理。操作查詢可以對數(shù)據(jù)表中的原始數(shù)據(jù)進(jìn)行修改，包括生成一張新表、向現(xiàn)有表中追加數(shù)據(jù)、更新現(xiàn)有表中的數(shù)據(jù)以及刪除現(xiàn)有表中的數(shù)據(jù)，這幾種操作查詢都沒有涉及到計(jì)算查詢。SQL查詢是通過Select語句來創(chuàng)建各種查詢包括計(jì)算查詢在內(nèi)，Select語句在本文中不做介紹。本文只介紹在查詢設(shè)計(jì)視圖中涉及的計(jì)算查詢：選擇查詢和交叉表查詢。

3 計(jì)算查詢分類與實(shí)例分析

3.1 計(jì)算查詢分類

計(jì)算查詢分為兩大類：一是利用表達(dá)式計(jì)算出新字段的查詢;二是利用總計(jì)行進(jìn)行分組計(jì)算的查詢。下面就通過具體的教學(xué)實(shí)例來詳細(xì)分析這兩種計(jì)算查詢。

本文所有實(shí)例均使用“學(xué)生管理系統(tǒng)”數(shù)據(jù)庫來完成，該數(shù)據(jù)庫中有六張數(shù)據(jù)表：“學(xué)生信息表”、“學(xué)生成績表”、“課程表”、“班級表”、“教師表”、“授課表”，各表都已經(jīng)按照相關(guān)字段建立了數(shù)據(jù)庫表間關(guān)系。各表結(jié)構(gòu)如下：

學(xué)生信息表（學(xué)號，姓名，性別，出生年月，政治面貌，生源地，班級編號，聯(lián)系電話）;學(xué)生成績表（學(xué)號，課程號，成績）;課程表（課程編號，課程名稱）;班級表（班級編號，班級名稱，所在系）;教師表（教師號，姓名，性別，出生年月，政治面貌，參加工作時(shí)間，職稱，工資，所在系，聯(lián)系電話）;授課表（教師號，課程號，班級編號，學(xué)期）

3.2 表達(dá)式計(jì)算出新字段的查詢

利用表達(dá)式構(gòu)造新字段的計(jì)算查詢，這類查詢適用于要查詢的內(nèi)容在原始數(shù)據(jù)表中并不存在該字段，但是我們可以通過已經(jīng)存在的相關(guān)字段加以計(jì)算而得出。具體實(shí)例如下：

實(shí)例一：創(chuàng)建計(jì)算學(xué)生年齡的查詢，要求結(jié)果顯示學(xué)號，姓名，年齡內(nèi)容。

分析：所需數(shù)據(jù)表為“學(xué)生信息表”;所需字段為學(xué)號、姓名，年齡字段并不存在，但是通過對出生日期計(jì)算可以得到年齡;年齡的計(jì)算方法分析當(dāng)前年份減去出生年份，相應(yīng)表達(dá)式為：year（date（））-year（[出生年月]）

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“學(xué)生信息表”;

（3）選擇“學(xué)號”、“姓名”字段;

（4）在“姓名”字段的后一個字段列輸入年齡：year（date（））-year（[出生年月]）;

（5）運(yùn)行;

（6）保存。

注意：本實(shí)例的關(guān)鍵是年齡字段的構(gòu)造，利用正確的表達(dá)式計(jì)算出年齡的值。如果表達(dá)式中出現(xiàn)了數(shù)據(jù)表中的字段名，則字段名一定要放在英文半角的方括號中。

在“學(xué)生管理系統(tǒng)”數(shù)據(jù)庫中還有“教師表”，可以利用相似的表達(dá)式來計(jì)算教師的工齡。在“圖書管理系統(tǒng)”的圖書表中還可以利用圖書入庫量字段減去出庫量字段得到圖書庫存量。

3.3 總計(jì)行進(jìn)行分組計(jì)算的查詢

總計(jì)行的分組計(jì)算查詢分為兩種：

（1）利用選擇查詢中的匯總按鈕Σ計(jì)算;

（2）利用交叉表查詢中的總計(jì)行計(jì)算。

兩種總計(jì)行中的選項(xiàng)一樣，常用主要有Group By、合計(jì)、平均值、最大值、最小值、計(jì)數(shù)六個選項(xiàng)。Group By是本計(jì)算查詢中的分組項(xiàng)，可以是字段也可以是表達(dá)式。

下面通過具體實(shí)例來分析兩種總計(jì)行分組計(jì)算查詢的適用場合及用法。操作對象使用本文的“學(xué)生管理系統(tǒng)”數(shù)據(jù)庫。

3.3.1 選擇查詢中的匯總

實(shí)例二：統(tǒng)計(jì)各班人數(shù)（要求結(jié)果中出現(xiàn)班級名稱、總?cè)藬?shù)）。

分析：所需表“學(xué)生信息表”、“班級表”;所需字段“班級表”中的“班級名稱”，“學(xué)生信息表”中的“學(xué)號”;分組項(xiàng)為“班級名稱”字段，“學(xué)號”字段進(jìn)行計(jì)數(shù)計(jì)算。

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“學(xué)生信息表”、班級表;

（3）選擇“班級名稱”、“學(xué)號”字段;

（4）單擊匯總按鈕，在總計(jì)行設(shè)置“班級名稱”為Group By，“學(xué)號”為計(jì)數(shù)，并且在“學(xué)號”的字段行更改“學(xué)號之計(jì)數(shù)”為“總?cè)藬?shù)”。

（5）運(yùn)行;

（6）保存。

運(yùn)行結(jié)果如圖1。

實(shí)例三：統(tǒng)計(jì)不同職稱的教師工資（要求結(jié)果中出現(xiàn)職稱、平均工資）。

分析：所需表“教師表”;所需字段“職稱”、“工資”;分組項(xiàng)為“職稱”字段，“工資”字段進(jìn)行平均值計(jì)算。

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“教師表”;

（3）選擇“工資”、“職稱”字段;

（4）單擊匯總按鈕，在總計(jì)行設(shè)置“職稱”為Group By，“工資”為平均值，并且在“工資”的字段行更改“工資之平均值”為“平均工資”。

（5）運(yùn)行;

（6）保存;

注意：平均值在計(jì)算時(shí)經(jīng)常會出現(xiàn)小數(shù)位數(shù)過多的情況，可以把“工資”字段列更改成表達(dá)式：ROUND（AVG（[工資]））來完成只保留整數(shù)部分，小數(shù)部分四舍五入。也可以把四舍五入ROUND（）函數(shù)換成INT（）直接取整。

實(shí)例四：統(tǒng)計(jì)所有課程的總分、平均分、最高分、最低分（要求結(jié)果中出現(xiàn)課程名稱及四個統(tǒng)計(jì)結(jié)果）

分析：所需表“課程表”、“學(xué)生成績表”;所需字段“課程名稱”、“成績”;分組項(xiàng)為“課程名稱”字段，“成績”字段進(jìn)行四次計(jì)算分別為總計(jì)、平均值、最大值、最小值，在這里要特別注意“成績”字段要選四次分別計(jì)算。

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“課程表”、“學(xué)生成績表”;

（3）選擇“課程名稱”、“成績”字段;

（4）單擊匯總按鈕，在總計(jì)行設(shè)置“課程名稱”為Group By，四個“成績”字段分別為總計(jì)、平均值、最大值、最小值，并更改四個“成績”的字段行標(biāo)題“總分”、“平均分”、“最高分”、“最低分”。

（5）運(yùn)行;

（6）保存。

運(yùn)行結(jié)果如圖2。

3.3.2 交叉表查詢

交叉表查詢是表中的記錄按照不同的字段進(jìn)行幾次分組，分組字段分行標(biāo)題和列標(biāo)題兩種，行標(biāo)題位于數(shù)據(jù)表左側(cè)，列標(biāo)題位于數(shù)據(jù)表頂端，行列標(biāo)題交叉點(diǎn)是分組計(jì)算的某個字段的計(jì)算結(jié)果。交叉表查詢在很多教材中都是通過向?qū)?chuàng)建的，只用了很少的篇幅一帶而過。交叉表查詢向?qū)е荒軇?chuàng)建數(shù)據(jù)源來自一個表或查詢的字段，對涉及到多表的查詢通常需要使用查詢設(shè)計(jì)視圖來創(chuàng)建。初學(xué)者理解起來比較困難，筆者通過幾個實(shí)例詳細(xì)分析交叉表查詢的創(chuàng)建過程及注意事項(xiàng)。

交叉表查詢在創(chuàng)建之前除了選定數(shù)據(jù)源表或查詢之外，還要確定三項(xiàng)數(shù)據(jù)：一是行標(biāo)題;二是列標(biāo)題;三是行列交叉處的字段，以及該字段需要什么總計(jì)方式。在這里要說明的是：無論是行標(biāo)題還是列標(biāo)題都是分組項(xiàng)，一個交叉表查詢最少要有一個行標(biāo)題、一個列標(biāo)題、一個值。最多行標(biāo)題的數(shù)目可以是三個，列標(biāo)題還是一個，值還是一個。交叉表查詢最少要有兩個分組字段，最多可以四個分組字段，值始終都只能是一個，列標(biāo)題字段的字段名不出現(xiàn)，只出現(xiàn)字段值。例如選取“性別”字段作列標(biāo)題，則結(jié)果頂端只顯示“男”、“女”兩個值作為列標(biāo)題。

實(shí)例五：統(tǒng)計(jì)各班的男女生人數(shù)。（結(jié)果顯示“班級名稱”，“男”，“女”）

分析：本題中的數(shù)據(jù)源為“學(xué)生信息表”、“班級表”;所需分組字段為“學(xué)生信息表”的“性別”為列標(biāo)題，“班級表”中的“班級名稱”為行標(biāo)題;所需統(tǒng)計(jì)字段為“學(xué)生信息表”的“學(xué)號”作計(jì)數(shù)計(jì)算。兩張表是通過“班級編號”創(chuàng)建的一對多的關(guān)系。

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“學(xué)生信息表”、“班級表”;

（3）選擇“班級名稱”、“性別”、“學(xué)號”三個字段;

（4）單擊查詢類型的“交叉表”按鈕，在總計(jì)行設(shè)置“班級名稱”為Group By，“性別”為Group By，“學(xué)號”為計(jì)數(shù)，并且在交叉表行設(shè)置“班級名稱”為行標(biāo)題，“性別”為列標(biāo)題，“學(xué)號”為值。

（5）運(yùn)行;

（6）保存。

運(yùn)行結(jié)果如圖3。

本實(shí)例也可以使用選擇查詢中匯總來完成。

運(yùn)行結(jié)果如圖4。

實(shí)例六：統(tǒng)計(jì)學(xué)生每門課程成績，要求使用交叉表查詢。

分析：所需數(shù)據(jù)源表“課程表”、“學(xué)生成績表”、“學(xué)生信息表”;所需分組字段為“學(xué)生成績表”中的“學(xué)號”作行標(biāo)題，“學(xué)生信息表”中的“姓名”作行標(biāo)題，“課程表”中的“課程名稱”為列標(biāo)題，“學(xué)生成績表”中的“成績”作值。這里“成績”的總計(jì)行選“總計(jì)”、“平均值”、“最大值”、“最小值”都可以，因?yàn)槊總€學(xué)生每門課程只有一個值，它無論以上四種計(jì)算結(jié)果都一樣。

操作步驟：

（1）打開查詢設(shè)計(jì)器;

（2）添加“學(xué)生信息表”、“學(xué)生成績表”、“課程表”;

（3）選擇“學(xué)號”、“姓名”、“課程名稱”、“成績”四個字段;

（4）單擊查詢類型的“交叉表”按鈕，在總計(jì)行設(shè)置“學(xué)號”為Group By，“姓名”為Group By，“課程名稱”為Group By，“成績”為“總計(jì)”、“平均值”、“最大值”、“最小值”四個中的任意一個，并且在交叉表行設(shè)置“學(xué)號”為行標(biāo)題，“姓名”為行標(biāo)題，“課程名稱”為列標(biāo)題，“成績”為值。

（5）運(yùn)行;

（6）保存。

4 總結(jié)

基于Access的計(jì)算查詢第一種構(gòu)造新字段查詢，關(guān)鍵在于如何利用已經(jīng)存在的字段，書寫正確的能計(jì)算出符合要求表達(dá)式;第二種分組統(tǒng)計(jì)查詢是使用交叉表查詢還是選擇查詢中的匯總，要根據(jù)實(shí)際情況來確定。

綜上所述，如表1結(jié)論。

在實(shí)際創(chuàng)建統(tǒng)計(jì)查詢時(shí)，有時(shí)以上兩種方式都可以完成例如本文的實(shí)例五，但統(tǒng)計(jì)結(jié)果在清晰度上有一定的差別，需要查詢設(shè)計(jì)者自己體會，選擇最合適的查詢方式。希望本文能夠?qū)τ谠O(shè)計(jì)者創(chuàng)建計(jì)算查詢有所幫助。

參考文獻(xiàn)

[1]李雁翎.Access 2003數(shù)據(jù)庫技術(shù)及應(yīng)用[M].高等教育出版社，2010.

[2]張成叔.Access 2010數(shù)據(jù)庫技術(shù)及應(yīng)用（第四版）[M].中國鐵道出版社，2013.

[3]屈武江.耿青松.Access數(shù)據(jù)庫技術(shù)與應(yīng)用項(xiàng)目人教程[M].大連理工出版社，2015.