VBAでAccessデータベースを活用しよう～データ取得編～

f:id:takehito33711:20190103144331j:plain

前回はSQL文を使って全データを取得しました。
今回は条件に合ったデータを取得していきます。

基本コード
色々なSQL
まとめ

基本コード

データベースには次のデータが入ってるとして、この中から必要なデータを抽出していきましょう。

ID	名前	生年月日	年齢	職業
1	サザエ	1922/11/22	27	主婦
2	波平	1985/9/14	54	会社員
3	フネ	1901/1/11	48	主婦
4	マスオ	1971/4/3	32	会社員
5	カツオ	1938/3/11	11	小学生
6	ワカメ	1942/6/15	9	小学生
7	タラオ	1947/3/18	3	子ども

まず基本コードを載せます。
この16行目のSQL文を色々工夫することで様々な条件のデータを取得していきます。

Public Sub 列データ取得()
'参照設定：Microsoft ADO Ext.6.0 for DDL and Security

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strDB As String
Dim DBFile As String
Dim SQL As String
Dim AryData()
Dim i
Dim j
   
DBFile = "ファイルの絶対パス"
strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
SQL = "★★★★★"
    
Set CN = New ADODB.Connection
CN.ConnectionString = strDB
CN.Open
   
Set RS = New ADODB.Recordset
RS.Open Source:=SQL, ActiveConnection:=CN
    
RS.MoveFirst
i = 1
   
Do Until RS.EOF
ReDim Preserve AryData(RS.Fields.Count - 1, i)   
For j = 0 To RS.Fields.Count - 1
AryData(j, 0) = RS(j).Name
AryData(j, i) = RS(j)
Next j
i = i + 1
RS.MoveNext
Loop
    
RS.Close
CN.Close
    
Set RS = Nothing
Set CN = Nothing
    
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Clear
On Error Resume Next
For i = 0 To UBound(AryData, 2)
For j = 0 To UBound(AryData, 1)
Cells(i + 1, j + 1) = AryData(j, i)
Next j
Next i
    
End Sub

色々なSQL

列指定SELECT文

SQLのSELECT文を「*」にすると全列のデータを取得することができたけど、列の順番を指定したいなあ。
そんなときはSELECTの後に列名を書き、「,」で区切ります。
列の順番や必要な列だけのデータを取得できます。

次の例はID、名前、生年月日をこの順で取得するSQLです。

SQL = "SELECT ID, 名前, 生年月日 FROM テーブル名"

[実行結果]
f:id:takehito33711:20190104094149p:plain

合致を検索WHERE句

サザエさんのデータだけが欲しいなあ。
特定の条件に合ったデータだけを取得できないかな。

そんなときはWHERE句を使います。
特定の条件に合致したデータのみを取得できます。

次の例は名前が「サザエ」に該当するデータの取得するSQLです。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 WHERE 名前='サザエ'"

[実行結果]
f:id:takehito33711:20190104095427p:plain

データの並び替えORDER BY句

年齢が順番に並んでいたらデータが見やすいのになあ。
並び替えできる方法はないだろうか。

そんなときはORDER BY句を使います。
データの並び替えができます。
昇順に並び替えしたいときは「ASC」を、降順に並び替えしたいときは「DESC」を続けて書きます。

次の例は年齢を昇順に並び替えるSQLです。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 ORDER BY 年齢 ASC"

[実行結果]
f:id:takehito33711:20190104095639p:plain

重複をなくすDISTINCT句

表の中にどんな職業があるのかを知りたいなあ。
でも普通に取得すると主婦、会社員、主婦、会社員、小学生、小学生、子どものように重複したデータになってしまう。
重複したデータを避けたいなー。

こんなときはDISTINCT句を使います。
重複したデータを避けることができます。

次の例は職業の重複を避けてデータを取得するSQLです。

SQL = "SELECT DISTINCT 職業 FROM テーブル名"

[実行結果]
f:id:takehito33711:20190107200756p:plain

AND句、OR句、NOT句

条件を細かく設定して目的のデータを取得できたらいいのになあ。

そんなときはAND、OR、NOTを使って条件を細かく設定してはどうでしょう。

次の例は年齢が10以上で、かつ職業が小学生になっているデータを取得するSQLです。
「かつ」なのでANDを使います。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 WHERE 年齢 > 10 AND 職業 = '小学生'"

[実行結果]
f:id:takehito33711:20190107193913p:plain

次の例は職業が会社員と主婦のデータを取得するSQLです。
両方のデータを取得するにはORを使います。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 WHERE 職業 = '会社員' OR 職業='主婦'"

[実行結果]
f:id:takehito33711:20190104100651p:plain

次の例は職業が会社員でないデータを取得するSQLです。
NOTを使います。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 WHERE NOT 職業='会社員'"

[実行結果]
f:id:takehito33711:20190104101151p:plain

まとめるGROUP BY句

表の中の最も年齢が高い人や平均年齢が知りたいなあ。
そんなときは集計関数を使うのはどうでしょうか。

SQLではSUM（合計）、AVG（平均）、COUNT（レコード数）、MAX（最大値）、MIN（最小値）を使うことができます。

次の例は表全体のデータ数と、平均年齢、最大年齢を取得するSQLです。
列名はASを使って設定しています。

SQL = "SELECT COUNT(ID) AS レコード数, AVG(年齢) AS 平均年齢, MAX(年齢) AS 最大年齢 FROM テーブル名"

[実行結果]
f:id:takehito33711:20190107195523p:plain

さっきは表全体の話だったけど、職業ごとで平均年齢とか知りたいなあ。
そんなときはGROUP BY句を使いましょう。
カテゴリーごとに集計するこができます。

次の例は職業ごとに平均年齢と最大年齢を取得するSQLです。

SQL = "SELECT 職業, AVG(年齢) AS 平均年齢, MAX(年齢) AS 最大年齢 FROM テーブル名 GROUP BY 職業"

[実行結果]
f:id:takehito33711:20190107195820p:plain

可能性無限大サブクエリ

年齢が最も高い人のデータを知りたいなあ。
そんなときはサブクエリを活用してみてはどうでしょうか。
SELECT、FROM、WHEREなどの後にさらに（）を使い、SQL文を組み込むことができます。
サブクエリを活用すればデータベースの欲しいデータの取得方法は格段に広がります。
是非使えるようにしましょう。

次の例は年齢が最も高い人のデータを取得するSQLです。
WHEREの後に最大年齢を取得するサブクエリを入れています。

SQL = "SELECT ID, 名前, 生年月日, 年齢, 職業 FROM テーブル名 WHERE 年齢= (SELECT MAX(年齢) FROM テーブル名)"

[実行結果]
f:id:takehito33711:20190107200343p:plain