この記事では、トップ値クエリと合計クエリを使用して、レコード セット内の最も新しい日付または最も古い日付を検索する方法について説明します。 これにより、顧客が最後に注文を行った場合や、市区町内で売上に最も近い 5 つの四半期など、さまざまなビジネス上の質問に答えるのに役立ちます。
この記事の内容
概要
トップ値クエリを使用して、データをランク付けし、上位のアイテムを確認できます。 トップ値クエリは、Web サイトで最も人気のある 5 つのページなど、結果の上部から指定された値の数またはパーセントを返す選択クエリです。 トップ値クエリは、任意の種類の値に対して使用できます。数値である必要はもう必要ない値です。
データをランク付けする前にグループ化または集計する場合は、トップ値クエリを使用する必要があります。 たとえば、会社の活動対象となっている都市ごとに、特定の日付における販売に関する数値を調べる必要があるとします。 この場合、都市がカテゴリとなり、都市ごとのデータを取得する必要があるため、集計クエリを使用します。
トップ値クエリを使用して、テーブルまたはレコード グループ内の最も新しい日付または最も古い日付を含むレコードを検索する場合、次のようなさまざまなビジネス上の質問に答えます。
-
最近、誰が売り上げを最も多くしているのですか?
-
前回得意先から注文があったのはいつか。
-
次の 3 人の誕生日はチームにいつですか?
トップ値クエリを作成するには、まず選択クエリを作成します。 次に、質問に従ってデータを並べ替えます。上位と下位の順に並べ替えます。 データをグループ化または集計する必要がある場合は、選択クエリを集計クエリに変換します。 その後、最大値や最小値を返す場合は Max や Min などの集計関数を使用し、最も古い日付または最も新しい日付を返す場合は First 関数または Last 関数を使用できます。
この記事では、使用する日付値に日付/時刻データ型があることを前提とします。 日付の値がテキスト フィールドにある場合は、
トップ値クエリの代わりにフィルターを使用する
特定の日付を念頭に置いた方がフィルターの方が一般的です。 トップ値クエリを作成するか、またはフィルターを適用するかについては、次の基準を考慮します。
-
日付が一致するか、特定の日付より前または後のすべてのレコードを返す場合は、フィルターを使用します。 たとえば、4 ~ 7 月の間に売り上げがあった日付を確認するには、フィルターを適用します。
-
フィールドに最新または最新の日付を含む、指定した量のレコードを返す必要がある場合に、正確な日付値がわからない場合や、関係ない場合は、トップ値クエリを作成します。 たとえば、5 つのベストセールス 四半期を表示するには、トップ値クエリを使用します。
フィルターの作成と使用の詳細については、「フィルターを適用して Access データベースのレコードを選択して表示する」 を参照してください。
サンプル データを準備して、例と共に従う
この記事の手順では、次のサンプル テーブルのデータを使用します。
Employees テーブル
LastName |
名 |
Address |
市区町村 |
CountryOrR egion |
生年月日 |
雇用日 |
川井 |
保美 |
東京都 |
調布市 |
日本 |
1968 年 2 月 5 日 |
1994 年 6 月 10 日 |
山本 |
浩平 |
神奈川県 |
川崎市 |
日本 |
1957 年 5 月 22 日 |
1996 年 11 月 22 日 |
山水 |
友野 |
3122 75th Ave. S.W. |
青梅市 |
日本 |
1960 年 11 月 11 日 |
2000 年 3 月 11 日 |
宮西 |
君彦 |
千葉県 |
松戸市 |
日本 |
1964 年 3 月 22 日 |
1998 年 6 月 22 日 |
藤巻 |
陽子 |
埼玉県 |
さいたま市 |
日本 |
1972 年 6 月 5 日 |
2002 年 1 月 5 日 |
小林 |
昌子 |
東京都 |
青梅市 |
日本 |
1970 年 1 月 23 日 |
1999 年 4 月 23 日 |
河田 |
弘穀 |
神奈川県 |
横浜市 |
日本 |
1964 年 4 月 14 日 |
2004 年 10 月 14 日 |
西村 |
美智江 |
長野県 |
松本市 |
日本 |
1959 年 10 月 29 日 |
1997 年 3 月 29 日 |
EventType テーブル
タイプ コード |
イベントの 種類 |
1 |
製品の販売開始 |
2 |
企業の催事 |
3 |
私的な催事 |
4 |
資金調達 |
5 |
見本市 |
6 |
講義 |
7 |
コンサート |
8 |
展示会 |
9 |
街頭宣伝 |
"得意先" テーブル
顧客 ID |
会社 |
担当者 |
1 |
Contoso, Ltd. Graphic |
今賀 千津 |
2 |
Tailspin Toys |
Eiji Yasuda |
3 |
Fabrikam |
西詰 幸造 |
4 |
Wingtip Toys |
福本 富美江 |
5 |
A. Datum |
舟田 けい |
6 |
Adventure Works |
ブライアン バーク |
7 |
Design Institute |
藤島 昭英 |
8 |
School of Fine Art |
田本 千賀 |
[イベント] テーブル
イベント コード |
イベントの 種類 |
得意先 |
イベント の日付 |
価格 |
1 |
製品の販売開始 |
Contoso, Ltd. |
4/14/2011 |
¥1,000,000 |
2 |
企業の催事 |
Tailspin Toys |
4/21/2011 |
¥800,000 |
3 |
見本市 |
Tailspin Toys |
5/1/2011 |
¥2,500,000 |
4 |
展示会 |
Graphic Design Institute |
5/13/2011 |
¥450,000 |
5 |
見本市 |
Contoso, Ltd. |
5/14/2011 |
¥550,0000 |
6 |
コンサート |
美術学校 |
5/23/2011 |
¥12,000 |
7 |
製品の販売開始 |
A. Datum |
6/1/2011 |
¥15,000 |
8 |
製品の販売開始 |
Wingtip Toys |
6/18/2011 |
¥21,000 |
9 |
資金調達 |
Adventure Works |
6/22/2011 |
¥130,000 |
10 |
講義 |
Graphic Design Institute |
6/25/2011 |
¥245,000 |
11 |
講義 |
Contoso, Ltd. |
2011/7/4 |
¥380,000 |
12 |
街頭宣伝 |
Graphic Design Institute |
2011/7/4 |
¥550,000 |
注: 以下の手順では、[得意先] テーブルと [イベント タイプ] テーブルが、[イベント] テーブルとの 1 対多リレーションシップの "1" 側にあることを想定しています。 この場合、[イベント] テーブルは、[得意先コード] フィールドと [タイプ コード] フィールドを共有します。 次のセクションで説明する集計クエリは、このリレーションシップが定義されていないと動作しません。
サンプル データを Excel ワークシートに貼り付ける
-
Excel を起動します。 空のブックが開きます。
-
Shift キーを押しながら F11 キーを押してワークシートを挿入します (4 つ必要です)。
-
各サンプル テーブルのデータを空のワークシートにコピーします。 列見出し (最初の行) を含める。
ワークシートからデータベース テーブルを作成する
-
最初のワークシートから列見出しを含むデータを選択します。
-
ナビゲーション ウィンドウを右クリックし、[貼り付け] を クリックします。
-
[ はい] をクリックして、最初の行に列見出しが含まれるか確認します。
-
残りのワークシートごとに手順 1 ~ 3 を繰り返します。
最も新しい日付または最も新しい日付を検索する
このセクションの手順では、サンプル データを使用して、トップ値クエリを作成するプロセスを示します。
基本的なトップ値クエリを作成する
-
[作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。
-
Employees テーブルをダブルクリックし、[閉じる] をクリック します。
サンプル データを使用する場合は、[社員] テーブルをクエリに追加します。
-
クエリに使用するフィールドをデザイン グリッドに追加します。 それぞれのフィールドをダブルクリックするか、または [フィールド] 列の空白のセルにそれぞれのフィールドをドラッグ アンド ドロップします。
サンプル テーブルを使う場合は、[姓]、[名]、[誕生日] の各フィールドを追加します。
-
上位または下位の値を含むフィールド (サンプル テーブルを使う場合は [誕生日] フィールド) 内で、[並べ替え] 行をクリックし、[昇順] または [降順] を選びます。
降順の並べ替え順序を選んだ場合は最も新しい日付が返され、昇順の並べ替え順序を選んだ場合は最も古い日付が返されます。
重要: [並べ替え] 行に値を設定する必要があるのは、日付を含むフィールドに対してだけです。 他のフィールドに対して並べ替え順序を指定してクエリを実行した場合は、必要な結果を得られません。
-
[デザイン] タブの [ツール] で [すべて] ([トップ値] の一覧) の横にある下向き矢印をクリックし、表示するレコードの数を入力するか、一覧からオプションを選びます。
-
[実行] をクリックしてクエリを実行し、データシート ビューに結果を表示します。
-
クエリを NextBirthDays として保存します。
このようなトップ値クエリでは、最年長または最年少の社員などの基本的な質問の回答を得ることができます。 次の手順では、式やその他の抽出条件を使ってクエリの有効性と柔軟性を高める方法について説明します。 次の手順で示す抽出条件を使用すると、次に誕生日を迎える 3 人の社員が返されます。
抽出条件をクエリに追加する
次の手順では、前の手順で作成したクエリを使用します。 テキスト値ではなく、実際の日付/時刻データが含まれている場合は、別のトップ値クエリに従います。
ヒント: このクエリの動作をよりよく理解したい場合は、各手順でデザイン ビューとデータシート ビューを切り替えます。 実際のクエリ コードを表示する場合は、ビューに切りSQLします。 ビューを切り替える場合は、クエリの上部にあるタブを右クリックし、必要なビューをクリックします。
-
ナビゲーション ウィンドウで、NextBirthDays クエリを右クリックし、[デザイン ビュー] を クリックします。
-
クエリ デザイン グリッドの BirthDate の右側の列に
、MonthBorn: DatePart("m",[BirthDate])を入力します。 この式では 、DatePart 関数を使用して BirthDate から月を抽出します。 -
クエリ デザイン グリッドの次の列に
、DayOfMonthBorn: DatePart("d",[BirthDate])を入力します。この式は 、DatePart関数を使用して BirthDate から月の日を抽出します。 -
入力した 2 つの式のそれぞれについて、[表示] 行のチェック ボックスをオフにします。
-
各式の [並べ 替え] 行をクリックし、[昇順] を 選択します。
-
[生年月日] 列の[条件] 行に
、Month([Birth Date]) > Month(Date()) OR Month([Birth Date])= Month(Date()) AND Day([Birth Date])>Day(Date())という式を入力します。-
Month([Birth Date]) > Month(Date()) は、各従業員の生年月日が将来の月に当たります。
-
Month( [誕生日])= Month(Date()) And Day([誕生日])>Day(Date())は、誕生日が現在の月に当たる場合、誕生日が当日以降に当たる場合に指定します。
つまり、この式は、誕生日が 1 月 1 日から現在の日付の間に発生するレコードを除外します。
ヒント: クエリ条件式のその他の例については、「クエリ条件の 例」を参照してください。
-
-
[デザイン] タブの [クエリ設定] グループで、[返品] ボックスに「3」と入力します。
-
[デザイン] タブの [結果] グループで、[実行] をクリックします。
注: 独自のデータを使用する独自のクエリでは、指定した数を超えるレコードが表示される場合があります。 上位の値の一部である値を共有する複数のレコードがデータに含まれている場合、クエリは、必要な数のレコードを返す場合でも、そのようなレコードを返します。
レコード グループの最も新しい日付または最も新しい日付を検索する
合計クエリを使用して、都市別にグループ化されたイベントなど、グループに含めるレコードの最も古い日付または最新の日付を検索します。 集計クエリは、集計関数 (Group By、Min、Max、Count、First、Last など)を使用して各出力フィールドの値を計算する選択クエリです。
カテゴリに使用するフィールド (グループ化するフィールド) と集計する値を含むフィールドを含める。 他の出力フィールド (イベントの種類別にグループ化する顧客の名前など) を含める場合、クエリではこれらのフィールドを使用してグループを作成し、元の質問に回答しなさそうに結果を変更します。 他のフィールドを使用して行にラベルを付けするには、ソースとして合計クエリを使用する追加のクエリを作成し、そのクエリに追加のフィールドを追加します。
ヒント: 手順に従ってクエリを作成すると、より高度な質問に答える効果的な戦略になります。 複雑なクエリが機能しない場合は、クエリを一連の単純なクエリに分割できるかどうかを検討します。
合計クエリを作成する
この手順では 、Events サンプル テーブルと EventType サンプル テーブルを使用 して、 次の質問に回答します。
コンサートを除く各イベントの種類の最新のイベントは、いつですか?
-
[作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。
-
Events テーブルと EventType テーブルをダブルクリックします。
各テーブルは、クエリ デザイナーの上部セクションに表示されます。 -
EventType テーブルの EventType フィールドと Events テーブルの EventDate フィールドをダブルクリックして、クエリ デザイン グリッドにフィールドを追加します。
-
クエリ デザイン グリッドの[EventType]フィールドの [条件] 行に、「コンサート<>入力します。
ヒント: 条件式のその他の例については、「クエリ条件の 例」を参照してください。
-
[デザイン] タブの [表示/非表示] グループで [集計] をクリックします。
-
クエリ デザイン グリッドで、[EventDate] フィールドの [合計 ] 行をクリックし、[最大] を クリックします。
-
[デザイン] タブの [結果] グループで [表示] をクリックし、[SQL ビュー] をクリックします。
-
このウィンドウSQL、SELECT 句の最後にある AS キーワードの直後に 、MaxOfEventDate を MostRecentに置き換えます。
-
クエリを MostRecentEventByType として保存します。
2 番目のクエリを作成して関連データを追加する
この手順では、前の手順の MostRecentEventByType クエリを使用して、次の質問に答えます。
各イベントの種類の最新のイベントで顧客は誰でしたか?
-
[作成] タブの [クエリ] グループで [クエリ デザイン] をクリックします。
-
[クエリ ] タブ で、MostRecentEventByType クエリをダブルクリックします。
-
[テーブル ] タブ で、[イベント] テーブルと [顧客] テーブルをダブルクリックします。
-
クエリ デザイナーで、次のフィールドをダブルクリックします。
-
[イベント] テーブルの [EventType] をダブルクリックします。
-
MostRecentEventByType クエリで、[MostRecent] をダブルクリックします。
-
[顧客] テーブルの [会社] をダブルクリックします。
-
-
クエリ デザイン グリッドの[EventType]列の[並べ替え] 行で、[昇順] を選択します。
-
[デザイン] タブの [結果] グループで、[実行] をクリックします。