ACCESS VBA レコード数をカウントするクエリ、RefreshとRefreshDatabaseWindow

#地味だけど公式の説明はわかりづらい
TableNameという名前のTableにFieldnameという列があるとします(まんまだ)
公式はどう書いているかというと

>1.[作成] タブの [その他] グループで、[クエリ デザイン] をクリックします。
Access のリボンの画像
2.[テーブルの表示] ダイアログ ボックスで、クエリで使用したいテーブルをダブルクリックし、[閉じる] をクリックします。
クエリ デザイナーの上の部分に、テーブルがウィンドウとして表示されます。
3.カテゴリ データを含むフィールドをダブルクリックし、カウントする値を含むフィールドもダブルクリックします。 その他の記述フィールドをクエリに含めることはできません。
4.[デザイン] タブの [表示/非表示] グループで [集計] をクリックします。
[集計] 行がデザイン グリッドに表示され、[グループ化] がクエリのフィールドごとに行に表示されます。
5.[集計] 行で、カウントしたいフィールドをクリックして、表示されるリストから [カウント] を選択します。
6.[デザイン] タブの [結果] グループで、[実行] をクリックします。
クエリの結果がデータシート ビューに表示されます。
7.必要に応じて、クエリを保存します。

 

 

わかりづらい…

#VBAを使う
VBAはまず使えるように設定してください。
一つ空白のデータベースを作ります。

Tablenameというテーブルは1列Fieldnameという名前のフィールドがありデータはこんなだとします

A

A

B

B

B

C

レコード数をカウントすると、結果はこんな感じです。

A・・・２

B・・・３

C・・・１

 

##テーブルのコード
それではまずテーブルを作りましょう。VBAを使えば簡単です。

```vb.net

Sub Create_Table()

Dim cDB As DAO.Database: Set cDB = CurrentDb

Dim tdf As DAO.TableDef

Dim drs As DAO.Recordset

Dim fld As DAO.Field

Dim ar, iar As Long: ar = Split("A,A,B,B,B,C", ",")

DoCmd.RunSQL "Create Table TableName( FieldName  Text(255));"

Set drs = cDB.OpenRecordset("TableName")

For iar = LBound(ar) To UBound(ar)

drs.AddNew

drs.Fields(0).Value = ar(iar)

drs.Update

Next iar

End Sub

```

 

とりあえずテーブルはこんなです

では上の結果をクエリに表示させるにはどうするか。次はクエリをVBAで作りましょう


```visualbasic

Sub Create_Query()

‘[VBAでクエリを作成する (DAO)](https://www.moug.net/tech/acvba/0040007.html)

Dim cDB As DAO.Database: Set cDB = CurrentDb

Dim Q As DAO.QueryDef

Dim sSQL As String

sSQL = _

"SELECT [TableName].Fieldname, Count([TableName].Fieldname) AS Fieldnameのカウント" & vbCrLf & _

"FROM [TableName]" & vbCrLf & _

"GROUP BY [TableName].Fieldname;"

Set Q = cDB.CreateQueryDef("Q_RecordCount", sSQL)

cDB.QueryDefs.Refresh ‘いつのAccessからか効かなくなっている命令。エラーにならずVersion違いに備え書いておく

Application.RefreshDatabaseWindow ‘Mougにない命令Access2010以降効くのはこちら

End Sub

```

 

##完成したクエリ

Fieldname             Fieldnameのカウント

A                          2

B                          3

C                          1

 

となります。

##クエリのポイント

Select [TableName].FieldName まずここでテーブルからカウントするフィールドを選びます。

 

, Count([TableName].Fieldname) AS Fieldnameのカウント

コンマのあとが2列目の定義です。Countが関数で AS 以下が式の入っているフィールド名です。

FROM [TableName]

GROUP BY [TableName].Fieldname; ←セミコロンを忘れないこと

以下改行で、FROM テーブル名 GROUP BY テーブル名.フィールド名セミコロン

FROM テーブル名がクエリのもととなるテーブル名、
Group BY フィールド名でグルーピングをしています。ここが公式の手順の4に該当します。

###改行は空白でも大丈夫です
つまり、

```visualbasic
sSQL = "SELECT [TableName].Fieldname, Count([TableName].Fieldname) AS Fieldnameのカウント FROM [TableName] GROUP BY [TableName].Fieldname;"
```

と書いてもいいです。

##絞り込んでカウントする
クエリで、Aだけ抜き出したい場合はSQLはどうなるでしょう。
クエリデザインで "A"といれてSQLビューで見てみます

```sql
SELECT TableName.Fieldname, Count(TableName.Fieldname) AS Fieldnameのカウント
FROM TableName
GROUP BY TableName.Fieldname
HAVING (((TableName.Fieldname)="A"));
```

とHavingが付きます。Havingが絞り込み条件だとわかります。このかっこの使い方複雑ですね。Accessのデザインビューはわかりやすく便利だとは言えませんが、こうしたSQLをわかりやすく入力する機能があるのです。

###AとBならORで結びます

```sql
SELECT TableName.Fieldname, Count(TableName.Fieldname) AS Fieldnameのカウント
FROM TableName
GROUP BY TableName.Fieldname
HAVING (((TableName.Fieldname)="A")) OR (((TableName.Fieldname)="B"));

 ```
##つまり通常のテーブルに応用するには
テーブルは手動でも作れますので、VBAを使わなくてもできますよね。
クエリもそうです。

```sql
SELECT [TableName].Fieldname, Count([TableName].Fieldname) AS Fieldnameのカウント FROM [TableName] GROUP BY [TableName].Fieldname;
```

ここでメモ帳(Notepad)を起動します。
メモ帳に上記のSQLをコピーし、TableNameとFieldNameを置換で書き換えます。
ACCESSに戻ります。
クエリデザインを開いて、テーブルを選択します。
SQLビューを開きます
メモ帳のSQLを選択してコピーし。それをSQLビューにコピーします。
データシートビューに切り替えます。
出来上がりを確認して名前を付けて保存します。
これで出来上がりです。

##シンプルだと思いませんか?
ということをヘルプや公式に書いてほしいんだけどなあ。
あとSQLビューで置換が効かないのってどうにかならないのか。
##公式の意味の分からないところ
カウントする値を含むフィールドもダブルクリックします。 その他の記述フィールドをクエリに含めることはできません。
これ何が言いたいかというと「カウントするフィールドだけにしろ」って意味ですね。


#個人的な見解Application.RefreshDatabaseWindow
###テーブルやクエリを作るのは3以上つある
Tableを作るには上記のSQLやCurrentDb.CreateTabledef、Appendと3つあります。この他にADOもあった気がしますが、
この際忘れます
クエリもそうですね。
###SQLで作成しない場合はすぐに表示されない
前者の場合は、すぐ表示されますが、後2者の場合、F5キーを押さないと表示されない場合があります。
###ネットではQuerydefs.refresh
クエリの場合、そんな時はQuerydefs.refreshとありますが、これも効かないことがあります。Application.RefreshDatabaseWindowが効きます。

###QueryDefs.Refreshは本当に効くのか？
公式のQueryDefs.Refreshは書き方が冷たいです。Access 2010以降このオブジェクトのためにサポートされないとありあます。

[QueryDefs.Refresh Method (DAO)](https://msdn.microsoft.com/en-us/library/office/ff192942(v=office.14).aspx)

>Not supported for this object.
Syntax
--------------------------------------------------------------------------------
expression .Refresh
expression A variable that represents a QueryDefs object.

>Remarks
--------------------------------------------------------------------------------
You can't use the Refresh method with collections that aren't persistent, such as Connections, Databases, Recordsets, Workspaces, or the QueryDefs collection of a Connection object.

 
###RefreshDatabaseWindow
一方RefreshDatabaseWindowは翻訳はおかしいので訂正が必要ですが、大変目的に沿っています
なんで翻訳がこんなめちゃくちゃなのか。

[Application.RefreshDatabaseWindow メソッド ](https://msdn.microsoft.com/ja-jp/vba/access-vba/articles/application-refreshdatabasewindow-method-access)
データベースオブジェクトが作成、更新、削除、名前の変更後にデータベース ウィンドウを更新します。(以下略)

というわけでまともに動くのはRefreshDatabaseWindowのようなのでこちらを使うか、QueryDefs.Refreshのあとに使うかした方が確実に画面が更新されます。

なので[Moug-VBAでクエリを作成する (DAO)](https://www.moug.net/tech/acvba/0040007.html)
に乗っているコードはRefreshDatabaseWindow確実に動くというわけでもないのです。

###原因はバージョンか
MougのコードはAccess97用です。
なので、バージョンで違うものと考えられます。
そこでAccess2010以降はVBAでSQL以外の方法でテーブルを作る場合は確実にRefreshDatabaseWindowを使うべきでしょう。

#ただし現実は厳しい
こうしてExcelに変換して提示すると現実にはたいていキれられます。
「なんだこれは、DEFGHIJKLMNOPQRSTUVWXYZがないじゃないか？なにレコード数が0の場合は集計されない？君、馬鹿なことを言っちゃいかん。0も0として表示されないとダメじゃないか。」

まあAccessだともう一つテーブルを作らないと無理だし。