本文說明如何使用 Access 中的頂端值查詢,在一組記錄中尋找最新或最早的日期。 您可以使用這類查詢傳回的資訊來回答各種商務問題,例如客戶最後一次下訂單的時間。
您想要做什麼?
瞭解頂端值查詢如何搭配日期使用
當您需要尋找資料表或記錄群組中包含最新或最早日期的記錄時,您可以使用頂端值查詢。 接著,您可以使用資料來回答多種類型的商務問題,例如:
-
員工上次進行銷售的時間是? 答案可協助您找出生產力最高或生產力最低的員工。
-
客戶上次下單的時間是? 如果客戶有一段時間未下訂單,您可能會想要將客戶移至非作用中清單。
-
誰有下一個生日或下一個 n 個生日?
建立和使用頂端值查詢的規則
您必須先建立選取查詢,藉以建立頂端值查詢。 視您要的結果而定,您可以將排序順序套用至查詢,或是將查詢轉換成合計查詢。 如果轉換查詢,則使用匯總函數,例如 Max 或 Min 傳回最高或最低值,或 First 或 Last 以傳回最早或最新日期。 只有當您需要尋找屬於一組群組或類別的數據時,才能使用合計查詢和匯總函數。 例如,假設您需要針對貴公司運作的每一個城市,尋找指定日期的銷售數位。 在這種情況下,城市會變成類別 (您需要尋找每個城市) 的數據,因此您需要使用合計查詢。
繼續進行時請記住,無論查詢類型為何,您的查詢都必須使用包含描述性數據的欄位,例如客戶名稱,以及包含您要尋找之日期值的欄位。 此外,日期值必須位於設定為 [日期/時間] 數據類型的欄位中。 如果您嘗試根據 [簡短文字] 字段中的日期值執行查詢,本文描述的查詢會失敗。 此外,如果您想要使用合計查詢,您的數據欄位必須包含類別資訊,例如城市或國家/地區欄位。
在頂端值查詢和篩選之間進行選擇
若要判斷您是否應該建立頂端值查詢或套用篩選,請選擇下列其中一項:
-
如果您想要傳回欄位中包含最新或最新日期的記錄,但不知道確切的日期值,或者這些值都沒關係,請建立頂端值查詢。
-
如果您想要傳回日期相符、早於或晚於特定日期的所有記錄,請使用篩選。 例如,若要查看 4 月到 7 月之間的銷售日期,請套用篩選。 篩選的完整討論不在本主題之外。
如需建立和使用篩選的詳細資訊,請參閱 套用篩選以檢視 Access 資料庫中的選取記錄一文。
尋找最新或最早的日期
本節中的步驟說明如何建立使用排序順序的基本頂端值查詢,以及使用表示式和其他準則的進階查詢。 第一節示範建立頂端值查詢的基本步驟,第二節說明如何新增準則來尋找接下來幾個員工生日。 這些步驟會使用下列範例數據表中的數據。
姓氏 |
名字 |
地址 |
城市 |
國家/地區 |
出生日期 |
雇用日期 |
---|---|---|---|---|---|---|
劉 |
沙東 |
1 Main St. |
New York |
Taiwan |
05-Feb-1968 |
10-Jun-1994 |
Heloo |
Waleed |
52 1st St. |
Boston |
Taiwan |
22-May-1957 |
22-Nov-1996 |
盧珮佳 |
Guido |
3122 75th Ave. S.W. |
Seattle |
Taiwan |
11-Nov-1960 |
11-Mar-2000 |
貝果 |
Jean Philippe |
1 Contoso Blvd. |
London |
UK |
22-Mar-1964 |
22-Jun-1998 |
價格 |
朱利安 |
Calle Smith 2 |
Mexico City |
墨西哥 |
05-Jun-1972 |
05-Jan-2002 |
休斯 |
克莉 絲 汀 |
3122 75th St. S. |
台北市 |
Taiwan |
23-Jan-1970 |
23-Apr-1999 |
萊利 |
史蒂夫 |
67 Big St. |
Tampa |
Taiwan |
14-Apr-1964 |
14-Oct-2004 |
Birkby |
丹娜 |
2 Nosey Pkwy |
苗栗縣 |
Taiwan |
29-Oct-1959 |
29-Mar-1997 |
如有需要,您可以手動將此範例數據輸入新表格中,或者將此範例表格複製到電子表格程式,例如 Microsoft Excel,然後使用 Access 將產生的工作表匯入表格。
建立基本頂端值查詢
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
在對話框中,按下您要在查詢中使用的數據表,按兩下 [ 新增 ],將數據表放在設計工具的上方區段,然後按兩下 [ 關閉]。
-或-
按兩下表格,然後按兩下 [ 關閉]。
如果您使用列出的範例數據是上一節,請將 [員工] 數據表新增至查詢。
-
將您要在查詢中使用的欄位新增至設計網格線。 您可以按兩下每個欄位,或將每個欄位拖放到 [ 功能 變數] 列中的空白儲存格上。
如果您使用範例數據表,請新增 [名字]、[姓氏] 和 [出生日期] 字段。
-
在 [出生日期] 欄位 (包含頂端或底端值的欄位中,如果您使用範例數據表) ,請按兩下 [ 排序 ] 列,然後選取 [ 遞增 ] 或 [ 遞減]。
遞減排序順序會傳回最新的日期,而 [遞增排序順序] 會傳回最早的日期。
重要: 您必須只針對包含日期的欄位,在 [排序 ] 列中設定值。 如果您為另一個字段指定排序順序,查詢不會傳回您要的結果。
-
在 [ 查詢設計] 索引 標籤的 [ 查詢設定 ] 群組中,按兩下 [ 全部 ] 旁的向下箭號 ([ 熱門值 ] 清單) ,然後輸入您要查看的記錄數目,或從清單中選取選項。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
-
儲存查詢並保持開啟,以便在後續步驟中使用。
您可以看到這種類型的最上層值查詢可以回答基本問題,例如公司中年齡最舊或最年幼的人。 下列步驟將說明如何使用表達式和其他準則,為查詢增添功能和彈性。 下一個步驟中顯示的準則會傳回接下來的三個員工生日。
新增準則至查詢
附註: 這些步驟假設您會使用上一節中所述的查詢。
-
將您在上述步驟中建立的查詢切換到 [設計視圖]。
-
在查詢設計網格線的 [出生日期] 數據行右側的欄中,複製並貼上或輸入此表達式: 表達式1:DatePart (“m”,[Birth Date]) 。 然後按兩下 [ 執行]。
DatePart 函數擷取了 [出生日期] 字段的月份部分。
-
切換到 [設計視圖]。
-
在第一個表達式的右側貼上或輸入此表達式: 表達式2:DatePart (“d”,[Birth Date]) 。 然後按兩下 [ 執行]。
在此情況下, DatePart 函數會擷取 [出生日期] 欄位的日期部分。
-
切換到 [設計視圖]。
-
清除 [ 顯示 ] 列中您剛輸入之兩個表達式的複選框,按兩下每個表達式的 [排序 ] 列,然後選取 [ 遞增]。
-
按一下 [執行]。
-
或者,您可以指定準則來限制查詢的範圍。 當您指定準則時,查詢只會排序符合準則的記錄,並識別排序列表中頂端或底端的域值。
若要繼續使用範例數據,請切換到 [設計視圖]。 然後在 [出生日期] 欄的 [準則] 列中,輸入下列表達式:
Month ([Birth Date]) > Month (Date () ) Or Month ([Birth Date]) = Month (Date () ) And Day ([Birth Date]) >Day (Date () )
此表達式會執行下列操作: [月份 ([出生日期]) > 月份 (日期 () ) 部分會檢查每位員工的出生日期,以查看是否落在未來月份,如果是,則查詢中包含這些記錄。 月份 ([出生日期]) = 月份 (日期 () ) 日 ([出生日期]) >日 (日期 () ) 表達式的一部分會檢查目前月份中的出生日期,以查看生日是落在當天或之後。 如果條件為 True,函數會在查詢中包含這些記錄。 摘要:此表達式會忽略生日介於 1 月 1 日到您執行查詢的日期之間的任何記錄。
若要查看查詢條件表達式的更多範例,請參閱 查詢條件範例一文。
-
在 [ 查詢設計] 索引 標籤的 [ 查詢設定 ] 群組中,按兩下 [ 全部 ] 旁的向下箭號 ([ 熱門值 ] 清單) ,然後輸入您要查看的記錄數目,或從清單中選取選項。
若要查看接下來的三個生日,請輸入 3。
-
按兩下 [執行 ] 以執行查詢,並在 [資料工作表檢視] 中顯示結果。
如果您看到的記錄數目超過您指定的數目
如果您的數據包含多筆共用日期值的記錄,則頂端值查詢傳回的數據可能超過您指定的數目。 例如,您可以設計一個熱門值查詢來擷取三筆員工記錄,但查詢會傳回四筆記錄,因為「又賀」和「生日」共用,如下表所示。
姓氏 |
BirthDate |
---|---|
貝卡 |
9/26/1968 |
傑克森 |
10/2/1970 |
愛德華茲 |
10/15/1965 |
威爾遜 |
10/15/1969 |
如果您看到的記錄少於您指定的數目
假設您設計查詢以傳回欄位中前五筆或最後五筆記錄,但查詢只會傳回三筆記錄。 原則上,您可以在 [設計視圖] 中開啟查詢,並檢閱設計網格線中欄的 [ 準則 ] 列,以解決這類問題。
如需準則的詳細資訊,請參閱 查詢條件範例一文。
如果您看到重複的記錄
如果頂端值查詢傳回重複專案,則基礎表包含重複的記錄,或是記錄看起來重複,因為查詢不包含可區分記錄的欄位。 例如,下表顯示一個查詢的結果,該查詢會傳回最近出貨的五筆訂單,以及處理該交易的銷售人員名稱。
ShippedDate |
銷售人員 |
---|---|
11/12/2004 |
Freitag |
11/12/2004 |
Cajhen |
10/12/2004 |
德爾加多 |
10/12/2004 |
德爾加多 |
10/12/2004 |
Freitag |
第三筆和第四筆記錄看起來重複,但銷售人員 Delgado 可能處理了同一天出貨的兩筆不同訂單。
根據您的需求,您可以執行兩個動作之一,以避免傳回重複的記錄。 您可以變更查詢的設計,新增欄位以協助區分記錄,例如 OrderID 和 CustomerID 欄位。 或者,如果只要看到其中一筆重複的記錄就足夠了,您只能將查詢的 [唯一值 ] 屬性設為 [ 是] 來顯示相異的記錄。 若要設定此屬性,請在查詢 [設計檢視] 中,以滑鼠右鍵按下查詢設計工具上半部空白區域中的任何位置,然後按下快捷方式功能表上的 [ 內容 ]。 在查詢的屬性表中,找出 [唯一值] 屬性,並將它設為 [ 是]。
如需處理重複記錄的詳細資訊,請參閱 使用查詢尋找重複記錄一文。
尋找類別或群組中記錄的最新或最早日期
您可以使用合計查詢來尋找屬於群組或類別之記錄的最早或最新日期。 合計查詢是選取查詢,使用 Min、 Max、 Sum、 First 和 Last 等匯總函數來計算指定欄位的值。
本節中的步驟假設您執行的是事件管理企業,您負責分階段、光源、保養,以及大型函數的其他方面。 此外,您管理的活動分為數種類別,例如產品上市、街地道和音樂會。 本節中的步驟說明如何回答常見問題:下一個事件的時機,依類別? 換句話說,下一次產品上市的時機、下一場音樂會等等。
繼續進行時,請記住下列事項:根據預設,您在這裡建立的合計查詢類型只能包含包含群組或類別數據的欄位,以及包含日期的欄位。 您不能包含描述類別中專案的其他欄位,例如客戶或供應商名稱。 不過,您可以建立第二個查詢,將合計查詢與包含描述性數據的欄位合併。 本節稍後的步驟說明如何執行該工作。
本節中的步驟假設使用下列三個數據表:
[事件類型] 資料表
TypeID |
事件類型 |
---|---|
1 |
產品啟動 |
2 |
公司函數 |
3 |
Private 函數 |
4 |
募款人 |
5 |
商展 |
6 |
演講 |
7 |
音樂會 |
8 |
展覽 |
9 |
街地道 |
[客戶] 資料表
客戶識別碼 |
公司 |
連絡人 |
---|---|---|
1 |
Contoso, Ltd. 圖形 |
吳亞斯 |
2 |
Tailspin Toys |
Ellen Adams |
3 |
Fabrikam |
Carol Philips |
4 |
Wingtip Toys |
Lucio Iallo |
5 |
A. 基準 |
曼達薩曼文 |
6 |
冒險工廠 |
Brian Burke |
7 |
Design Institute |
Jaka Stele |
8 |
美術學校 |
Milena Duomanova |
事件數據表
事件識別碼 |
事件類型 |
客戶 |
事件日期 |
價格 |
---|---|---|---|---|
1 |
產品啟動 |
Contoso, Ltd. |
4/14/2003 |
$10,000 |
2 |
公司函數 |
Tailspin Toys |
4/21/2003 |
$8,000 |
3 |
商展 |
Tailspin Toys |
5/1/2003 |
$25,000 |
4 |
展覽 |
Graphic Design Institute |
5/13/2003 |
$4,500 |
5 |
商展 |
Contoso, Ltd. |
5/14/2003 |
$55,000 |
6 |
音樂會 |
美術學校 |
5/23/2003 |
$12,000 |
7 |
產品啟動 |
A. 基準 |
6/1/2003 |
$15,000 |
8 |
產品啟動 |
Wingtip Toys |
6/18/2003 |
$21,000 |
9 |
募款人 |
冒險工廠 |
6/22/2003 |
$1,300 |
10 |
演講 |
Graphic Design Institute |
6/25/2003 |
$2,450 |
11 |
演講 |
Contoso, Ltd. |
7/4/2003 |
$3,800 |
12 |
街地道 |
Graphic Design Institute |
7/4/2003 |
$5,500 |
附註: 本節中的步驟假設 [客戶] 和 [事件類型] 數據表與 [事件] 數據表位於一對多關聯的「一」端。 在此情況下,[事件] 資料表會共用 [客戶標識符] 和 [TypeID] 字段。 沒有這些關聯,下一節所述的合計查詢將無法運作。
如何? 將此數據新增至資料庫?
若要將這些範例數據表新增至資料庫,您可以將數據複製到 Excel,然後匯入數據,但有一些例外狀況:
-
當您將 [事件類型] 和 [客戶] 資料表複製到 Excel 時,請勿複製 TypeID 和 CustomerID 數據行。 Access 會在匯入工作表時為您新增主鍵值;這可為您節省一些時間。
-
匯入資料表之後,您必須在 [設計檢視] 中開啟 [事件] 資料表,並將 [事件類型] 和 [客戶] 數據行轉換為查閱字段。 若要這麼做,請按兩下每個欄位的 [ 資料類型 ] 欄,然後按兩下 [ 查閱精靈]。
在建立查閱欄位時, Access 會將 [事件類型] 和 [客戶] 資料行中的文字值取代為源數據表中的數值。
如需建立和使用查閱欄位的詳細資訊,請參閱 建立或刪除多重值欄位一文。 本文說明如何建立一種查閱字段類型,讓您為指定欄位選取多個值,並說明如何建立查閱清單。
建立合計查詢
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下您要使用的數據表。 每個數據表都會出現在查詢設計工具的上方區段中。
如果您使用上面列出的範例數據表,請新增 [事件] 和 [事件類型] 資料表。
-
按兩下您要用於查詢的數據表欄位。 請確定此時您只將群組或類別欄位及值欄位新增至查詢。
如果您使用前三個數據表中列出的範例數據,請從 [事件類型] 數據表新增 [事件類型] 字段,或從 [事件] 數據表新增 [事件日期] 字段。
-
或者,您可以指定限制查詢範圍的準則。 只有符合準則的記錄會進行排序,而且在此排序列表中會識別上層和底端域值。
例如,如果您想要傳回 [私人函數] 類別中的事件,請在 [事件類型] 欄的 [準則] 列中輸入此表達 式:<>“Private Function”。
若要查看查詢條件表達式的更多範例,請參閱 查詢條件範例一文。
-
執行下列動作,將查詢轉換成合計查詢:
在 [ 查詢設計] 索引 標籤的 [ 顯示/隱藏 ] 群組中,按兩下 [ 合計]。
[ 合計] 列 會出現在設計網格線中。
-
確定每個群組或類別欄位的 [ 合計 ] 列設為 [ 群組依據],然後將值欄位的 [ 合計 ] 列設 (頂端或底端值的欄位) 為 [最大 值] 或 [ 最小值]。
Max 會傳回數值欄位中的最大值,以及 [日期/時間] 字段中最近的日期或時間值。 Min 會傳回數值欄位中的最小值,以及 [日期/時間] 欄位中最早的日期或時間值。
-
在 [ 查詢設計] 索引 標籤的 [ 查詢設定 ] 群組中,按兩下 [ 全部 ] 旁的向下箭號 ([ 熱門值 ] 清單) ,然後輸入您要查看的記錄數目,或從清單中選取選項。
在此情況下,選取 [ 全部],然後按兩下 [ 執行 ] 以在 [數據工作表檢視] 中顯示結果。
附註: 根據您在步驟 6 中選擇的函數, Access 將查詢中的值字段名稱變更為 MaxOfFieldName 或 MinOfFieldName。 如果您使用範例數據表, Access 重新命名字段 MaxOfEvent Date 或 MinOfEvent Date。
-
儲存查詢並移至後續步驟。
查詢結果不會顯示產品名稱或產品的其他資訊。 若要查看其他數據,您必須建立第二個查詢,併入您剛才建立的查詢。 下列步驟將說明如何執行此動作。
建立第二個查詢以新增更多數據
-
在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。
-
按兩下 [ 查詢] 索 引標籤,然後按兩下您在上一節中建立的合計查詢。
-
按兩下 [ 資料表] 索 引標籤,新增您在合計查詢中使用的數據表,並新增包含其他相關數據的任何數據表。 如果您使用前三個範例數據表,請將 [事件類型]、[事件] 和 [客戶] 資料表新增至新的查詢。
-
將合計查詢中的欄位聯結至父數據表中的對應欄位。 若要這麼做,請將合計查詢中的每個欄位拖曳到數據表中的對應欄位。
如果您使用三個數據表的範例數據,請將合計查詢中的 [事件類型] 資料行拖曳至 [事件類型] 數據表中的 [事件類型] 字段。 然後,您將合計查詢中的 MaxOfEvent 日期 數據行拖曳至 [事件] 資料表中的 [事件日期] 字段。 建立這些聯結可讓新的選取查詢將合計查詢中的數據與其他數據表中的數據匯總在一起。
-
新增查詢中其他數據表的其他描述欄位。
如果您使用三個數據表的範例數據,您可以從 [客戶] 數據表新增 [公司和聯繫人] 字段。
-
您也可以為一或多個欄指定排序順序。 例如,若要依字母順序序列出類別,請將 [事件類型] 欄的 [排序] 列設為 [遞增]。
-
在 [ 查詢設計] 索引 標籤的 [ 結果 ] 群組中,按兩下 [ 執行]。
查詢的結果會顯示在 [數據工作表檢視] 中。
提示: 如果您不想讓 [價格 ] 欄標題顯示為 MaxOfPrice 或 MinOfPrice,請在 [設計視圖] 中開啟查詢,然後在網格線的價格欄中輸入 Price:MaxOfPrice 或 Price: MinOfPrice。 價格 會在 [數據工作表檢視] 中顯示為欄標題。
尋找最新和最早的日期
您先前在本文中建立的查詢可以傳回頂端或底部的值,但不能同時傳回兩者。 如果您想要在單一檢視中看到這兩組值,您需要建立兩個查詢:一個擷取頂端值,另一個擷取底端的值,然後合併並將結果儲存在單一數據表中。
尋找頂端和底端值,並在資料表中顯示該資料的程式會遵循下列大略步驟:
-
建立頂端值和底端值查詢,或者,如果您需要將數據分組,請建立使用 Min 和 Max 函數 的合計查詢。
-
將頂端值查詢 (或最大合計查詢) 組成數據表查詢,然後建立新數據表。
-
將底端的值查詢 (或 Min totals 查詢) 轉換為追加查詢,並將記錄新增至最上方的值數據表。
這些章節中的步驟說明如何執行此動作。
建立查詢
-
建立頂端和底端的值查詢。
如需建立頂端或底端值查詢所需的步驟,請參閱本文稍早所述的 尋找最新或最早日期。 如果您需要依類別將記錄分組,請參閱本文稍早所述 的尋找類別或群組中記錄的最新或最早日期。
如果您使用最後一個區段的範例數據表,請只使用 [事件] 資料表中的數據。 在兩個查詢中使用 [事件類型]、[客戶] 和 [事件日期] 字段。
-
以有意義的名稱儲存每個查詢,例如 [頂端值] 和 [底端值],然後在後續步驟中保持開啟以供使用。
-
建立數據表查詢
-
在 [設計檢視] 中開啟頂端值查詢:
在 [ 查詢設計] 索引 標籤的 [ 查詢類型] 群組中,按兩下 [ 建立資料表]。
[製成資料表] 對話方塊便會出現。
-
在 [ 數據表名稱] 方 塊中,輸入要儲存頂端和底部記錄的數據表名稱。 例如,輸入 [頂端] 和 [底部記錄],然後按兩下 [ 確定]。
每次您執行查詢,而不是在 [數據工作表檢視] 中顯示結果時,查詢會建立數據表,並以目前的數據取代頂端值。
-
儲存並關閉查詢。
建立追加查詢
-
在 [設計檢視] 中使用底部值查詢:
在 [ 查詢設計] 索引 標籤的 [ 查詢類型] 群組中,按兩下 [ 附加]。
-
[新增] 對話方塊就會出現。
-
輸入您在 [ 製作表格 ] 對話框中輸入的相同名稱。
例如,輸入 [頂端] 和 [底部記錄],然後按兩下 [ 確定]。 每次您執行查詢,而不是在 [資料工作表檢視] 中顯示結果時,查詢會將記錄附加到 [頂端] 和 [底端記錄] 資料表。
-
儲存並關閉查詢。
執行查詢
-
您現在可以執行這兩個查詢了。 在 [導航窗格] 中,按兩下頂端值查詢,然後在 Access 提示時按兩下 [ 是 ]。 然後按兩下 [底端值] 查詢,然後在 Access 提示時按兩下 [ 是 ]。
-
在 [數據工作表檢視] 中開啟 [頂端] 和 [底部記錄] 數據表。
重要: 如果您嘗試執行製作數據表或追加查詢,但似乎沒有任何反應,請檢查 Access 狀態列以取得下列訊息:
[巨集指令或事件已經被停用模式封鎖。]
如果您看到該訊息,請採取下列步驟:
-
選 取 [啟用此內容] ,然後按兩下 [ 確定]。
-
再次執行查詢。