地味だけど公式の説明はわかりづらい
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を使えば簡単です。
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で作りましょう
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に該当します。
改行は空白でも大丈夫です
つまり、
sSQL = "SELECT [TableName].Fieldname, Count([TableName].Fieldname) AS Fieldnameのカウント FROM [TableName] GROUP BY [TableName].Fieldname;"
と書いてもいいです。
絞り込んでカウントする
クエリで、Aだけ抜き出したい場合はSQLはどうなるでしょう。
クエリデザインで "A"といれて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で結びます
SELECT TableName.Fieldname, Count(TableName.Fieldname) AS Fieldnameのカウント
FROM TableName
GROUP BY TableName.Fieldname
HAVING (((TableName.Fieldname)="A")) OR (((TableName.Fieldname)="B"));
つまり通常のテーブルに応用するには
テーブルは手動でも作れますので、VBAを使わなくてもできますよね。
クエリもそうです。
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)
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 メソッド
データベースオブジェクトが作成、更新、削除、名前の変更後にデータベース ウィンドウを更新します。(以下略)
というわけでまともに動くのはRefreshDatabaseWindowのようなのでこちらを使うか、QueryDefs.Refreshのあとに使うかした方が確実に画面が更新されます。
なのでMoug-VBAでクエリを作成する (DAO)
に乗っているコードはRefreshDatabaseWindow確実に動くというわけでもないのです。
原因はバージョンか
MougのコードはAccess97用です。
なので、バージョンで違うものと考えられます。
そこでAccess2010以降はVBAでSQL以外の方法でテーブルを作る場合は確実にRefreshDatabaseWindowを使うべきでしょう。
ただし現実は厳しい
こうしてExcelに変換して提示すると現実にはたいていキれられます。
「なんだこれは、DEFGHIJKLMNOPQRSTUVWXYZがないじゃないか？なにレコード数が0の場合は集計されない？君、馬鹿なことを言っちゃいかん。0も0として表示されないとダメじゃないか。」
まあAccessだともう一つテーブルを作らないと無理だし。