「AccessVBA開発」カテゴリーアーカイブ

【AccessVBA】リストボックスのソース（テーブルorクエリ）を並べ替えて表示する方法

2016年11月3日AccessVBA開発Jomo

【Access】で、ボタンをクリックして、リストボックスにデータを表示させるときのソース（テーブルorクエリ）を指定するイベントプロシージャは

※リストボックス名：『listTable』
※ソースのクエリ名：『q市場業販売上集計』
※ボタン名：『市場売上表示ボタン』

Private Sub 市場売上表示ボタン_Click()
Me.listTable.RowSourceType = “Table/Query”
Me.listTable.RowSource = “q市場業販売上集計”
End Sub

また、、これを並べ替えて表示させる為には、クエリ名（もしくはテーブル名）の部分をSQLに置き換えて、以下のように書き換える。

※フィールド名：『取引日』
※『q市場業販売上集計』の『取引日』で降順で並べ替える

Private Sub 市場売上表示ボタン_Click()
Me.listTable.RowSourceType = “Table/Query”
Me.listTable.RowSource = “SELECT * FROM q市場業販売上集計 ORDER BY 取引日 DESC;”
Me.listTable.Requery
End Sub

同様に、フィルターをかけて表示させたいときは「Filter」はコンボボックスやリストボックスには使用できないので、WHERE句を使ったSQLでソースを指定するとよい。

【Access】＝帳票印刷＝　レポートをグループ化して改ページする

2016年6月12日AccessVBA開発Jomo

レポートをグループ化して改ページする

Access のレポートの機能を使って、特定のカラムの値でグループ化して、そのグループ毎にページを印刷します。
例）取引先企業ごとに請求明細を印刷する。

取引先企業

企業A

企業B

企業C

【前提条件】
グループ化するための値のカラムが存在する。
例）カラム名が「取引先企業」

【準備1】

レポートの下段に、『グループ化、並べ替え、集計』があるので、そこで、『グループの追加』をする。
※グループとして追加したい項目名を登録する事で、グループヘッダー、グループフッターが作成される。

【準備2】※2パターンある。

①最終ページにはレポートフッターだけにしたい場合

グループフッターの改ページプロパティを『カレントセクションの後』に設定する。
グループヘッダーの改ページプロパティは『しない』に設定する。

②　レポートフッターを最後のグループフッターの、直後の同じページ内に出力したい場合

グループフッターの改ページプロパティを『しない』に設定する。
グループヘッダーの改ページプロパティは『カレントセクションの前』に設定する。

【AccessVBA】　NullとEmptyとNothingと空の文字列の違い

2015年6月12日AccessVBA開発Jomo

VBAでは変数やフィールドに有効な値がない状態や、空の状態を表す値がいくつもあります。
ここでは、それぞれの意味や使用上の注意についてご紹介します。

■長さ0の文字列（””）

文字を1つも含まない文字列 (“”) です。
「長さ0の文字列」の名前のとおり、Len関数やLenB関数の引数に指定すると0を返します。

    Dim myStr As String
    myStr = ""
    Debug.Print Len(myStr)     '--> 0　と表示される

■値0の文字列（vbNullString）

String型の変数の初期状態を表す値です。vbNullStringという定数で表します。
長さ0の文字列（””）と同様に、Len/LenB関数の引数に指定すると 0 を返します。
また、「If vbNullString = “” Then」のように比較すると、True になります。
そのため、空の文字列として長さ0の文字列 (“”)と区別されずに扱われることが多いですが、長さ0の文字列とは異なります。

値0の文字列と長さ0の文字列は、StrPtr関数で判別できます。
StrPtr関数はString型の変数のアドレス値を返す関数です。
StrPtr関数に値0の文字列（vbNullString）を指定すると 0 を返します。
一方、長さ0の文字列（””）を指定すると、変数のアドレス値を返します。

■Empty

バリアント型の変数に格納される特殊な値（リテラル値）です。
Emptyは変数が初期化されていない状態を表します。バリアント型の変数の初期値です。
また、Emptyを格納できるのはバリアント型だけです。

バリアント型は、文字列や数値、日付やブール値など、いろいろなデータを扱うことができる特殊なデータ型です。Emptyのほか、Nullなどの特殊な値もバリアント型のリテラル値です。
Emptyかどうかは、次のようにIsEmpty関数で判定します。

    Dim myVar As Variant
    Debug.Print VarType(myVar)   '--> 0（定数vbEmpty）と表示される
    Debug.Print TypeName(myVar)  '--> Empty　と表示される
    Debug.Print IsEmpty(myVar)   '--> True　と表示される

VBAでは、明示的に型を変換しなくても、暗黙的に型の変換（型強制）が行われます。
バリアント型のEmptyを文字列として扱うと、String型に型強制されて長さ0の文字列（””）になり、数値として扱うと0に、ブール値として扱うとFalseになります。

    Dim myVar As Variant                    '初期値はEmpty
    If myVar = 0 Then Debug.Print "True"    '--> True　と表示される
    If myVar = "" Then Debug.Print "True"   '--> True　と表示される

ちなみに、Excelのセルを表すRangeオブジェクトのValueプロパティはバリアント型で、初期値はEmptyです。
セルが初期状態のとき、長さ0の文字列や数値の0と比較するとTrueになるのは、Emptyがこれらに型強制されているためです。

■Null

Nullは、バリアント型の変数に格納される特殊な値（リテラル値）です。
特殊な内部処理形式（vbNull）を持ち、有効なデータが格納されていないことを表します。
NullかどうかはIsNull関数で判定します。

    Dim myVar As Variant        '初期値はEmpty
    myVar = Null                'Nullを代入
    Debug.Print VarType(myVar)  '--> 1（定数vbNull）と表示される
    Debug.Print TypeName(myVar) '--> Null　と表示される
    Debug.Print IsNull(myVar)   '--> True　と表示される

Nullを格納できるのはバリアント型だけです。
ADOやDAOを使ってデータベースを扱う場合、フィールドを操作するオブジェクトのValueプロパティはバリアント型のため、値のない状態として Null を扱うことができます。

また、変数やフィールド、Accessフォーム/レポートのコントロールがNullになるのは、次のケースです。

明示的にNullを代入したとき
Nullを含む演算（計算）を行ったとき
フィールドやAccessフォーム/レポートコントロールに有効な値がないとき

このうち、2)については、値がないものと演算をするのですから、結果も有効な値にならないというわけです。
3)については、少し注意が必要で、フィールドを表すオブジェクトやコントロールのValueプロパティはバリアント型です。そしてバリアント型の初期値は前述のとおりEmptyです。しかし、フィールドに有効な値がない場合や、バインドしていない（非連結）コントロールの場合、初期値はEmptyでなくNullになっています。

●補足1●

Excelのユーザーフォームにもテキストボックスがありますが、Acccessのフォーム/レポートのテキストボックスとは異なります。
Excelのユーザーフォームで使用するテキストボックスの場合、Valueプロパティはバリアント型ですが、内部処理形式はString型で、値0の文字列（vbNullString）に初期化されています。

●補足2●

レコードセットを使用して処理を行う場合、元のフィールドが数値型でも値がなければNullになるため、それを使って演算すると結果はNullになってしまいます。
値がないときには別の値に置き換えたいという場合は、演算前にNullかどうかを判定するか、またはNz関数を使用して別の値に置き換えてもよいでしょう。
ただし、「If myVar = Null」や「If myVar <> Null」のように比較するといずれもFalseになってしまうので、Nullの判定にはIsNull関数を使用してください。

■Nothing

オブジェクト変数に格納される特殊な値（リテラル値）です。
オブジェクト変数が特定のオブジェクトと関係付けられていないことを表します。
オブジェクト変数を宣言した後、オブジェクトへの参照が設定されるまで、オブジェクト変数にはNothingが設定されています。
また、オブジェクト変数が特定のオブジェクトを参照しているとき、Setステートメントを使用して次のように記述すると、オブジェクト変数と参照先のオブジェクトとの関係が無効になります。

    Set myObj = Nothing

ただし、オブジェクト変数にNothingを代入しても、オブジェクトを破棄しているわけではないことに注意。
オブジェクトは、どこからも参照されなくなったときに、自分自身を破棄する仕組みになっています。そのため、複数のオブジェクト変数が同じオブジェクトを参照している場合は、すべての変数に明示的にキーワードNothingを設定するか、または参照しているすべてのオブジェクト変数の適用範囲 (スコープ) 外になるまで、リソースは解放されません。

オブジェクト変数がNothingであるかどうかを確認するには、Is演算子を使用します。

    If myObj Is Nothing Then
        Set myObj = Forms![フォーム1]
    End If

この例では、オブジェクト変数Objが特定のオブジェクトと関連付けられていない場合は、Objにフォーム1を割り当てています。

【Access】レポートでのデータ（レコード）の並び順が元のクエリと違う！！

2015年2月26日AccessVBA開発Jomo

レポートのレコードソースとなっているクエリと同じ並び順で印刷されそうな気がしてしまうところですが、
レポートでの並び順は別に指定をしなければなりません。

・レポートをデザインビューで開く
＞[グループ化と集計]グループから、『並べ替えの追加』を選ぶ
＞『並べ替えキー』で『日付』を選択
＞『昇順』を選択

↓
以上で、日付順に印刷データが並びます！！
確認は、レポートを印刷プレビューで開きます。

【Access】レポートでの集計

2015年2月20日AccessVBA開発, 未分類Jomo

レポートでの集計 – Access

レイアウトビューで合計などの集計を追加する
レイアウトビューを使用すると、合計や平均などの集計を最もすばやくレポートに追加できます。
ナビゲーションウィンドウで、レポートを右クリックし、 Button image (レイアウトビュー) をクリックします。
集計するフィールドをクリックします。たとえば、列の数値に合計を追加する場合は、その列の数値の 1 つをクリックします。

[書式設定] タブの [グループ化と集計] で [集計] をクリックします。 Button imageフィールドに追加する集計の種類をクリックします。次の表は、Microsoft Office Access 2007 でレポートに追加できる集計関数の種類です。

レポートフッターセクションにテキストボックスが追加され、その “ControlSource/コントロールソース” プロパティが目的の計算を実行する式に設定されます。レポートにグループレベルがある場合は、各グループフッターセクションにも、同じ計算を実行するテキストボックスが追加されます。

ページの先頭へ

デザインビューで合計などの集計を追加する
デザインビューを使用すると、集計の配置や外観をさらに制御できます。グループ化されたレポートでは、各グループのヘッダーまたはフッターに合計などの集計を配置できます。レポート全体の集計は、レポートヘッダーセクションまたはレポートフッターセクションに配置できます。

レポートをデザインビューで開きます。

[デザイン] タブの [コントロール] で [テキストボックス] をクリックします。 Button image

次のいずれかの操作を行います。

レコードのグループの合計または平均を計算するには、[グループヘッダー] セクションまたは [グループフッター] セクション内をクリックします。

レポート内のすべてのレコードの総計または平均を計算するには、[レポートヘッダー] セクションまたは [レポートフッター] セクション内をクリックします。

クリックしたセクションに、テキストボックスが挿入されます。

テキストボックスを選択します。プロパティシートが表示されていない場合は、F4 キーを押して表示します。

[データ] タブをクリックします。[コントロールソース] ボックスに、Sum 関数を使用する式 (合計を計算する場合)、または Avg 関数を使用する式 (平均を計算する場合) を入力します。たとえば、次のように入力します。
=Sum(運送料)

または
=Avg(運送料)

ヒント式ビルダを使用して式を作成できます。式ビルダを開くには、[コントロールソース] ボックスのボタンの画像をクリックします。

注 Sum などの集計関数、または DSum などの定義域集計関数を使用して、デザインビューで合計を計算するときには、関数で演算コントロールの名前を使用することはできません。演算コントロールの式を繰り返す必要があります。たとえば、次のようになります。

=Sum([数量]*[単価])

ただし、基になるクエリに、”=明細金額:[数量]*[単価]” のような集計フィールドが含まれている場合は、次のように Sum 関数でそのフィールドの名前を使用できます。

=Sum([明細金額])

ページの先頭へ

割合 (%) を計算する

ナビゲーションウィンドウで、レポートを右クリックし、ショートカットメニューのボタンの画像(デザインビュー) をクリックします。

[デザイン] タブの [コントロール] で [テキストボックス] をクリックします。 Button image

次のうち、必要な処理を行います。

レポートのグループ小計が計算されていない場合は、[グループフッター] セクションをクリックして、グループ小計を表示するテキストボックスを追加します。

レポートの総計が計算されていない場合は、[レポートフッター] セクションにテキストボックスを追加して総計を表示します。

次のうち、必要な処理を行います。

グループ小計や総計に対する各アイテムの割合 (%) を計算するには、[詳細セクション] セクションにテキストボックスを追加します。

総計に対するアイテムグループの割合 (%) を計算するには、[グループヘッダー] セクションまたは [グループフッター] セクションにテキストボックスを追加します。

注レポートに複数のグループレベルがある場合は、割合を計算するレベルのヘッダーまたはフッターにテキストボックスを挿入します。

テキストボックスを選択します。プロパティシートが表示されていない場合は、F4 キーを押して表示します。

[データ] タブをクリックします。[コントロールソース] ボックスに、小計をその小計を含む合計で割る式を入力します。たとえば、「=[日別小計]/[総計]」のように入力します。

ヒント式ビルダーを使用して式を作成するには、ボタンの画像をクリックします。

テキストボックスの “Format/書式” プロパティを “Percent/パーセント” に設定します。

グループ化されたレポートの作成と使用方法の詳細については、「グループ化されたレポートまたは集計レポートを作成する」を参照してください。

ページの先頭へ

データを集計する (累計)

Office Access 2007 を使用してレポート内で “集計” を作成できます。集計は、グループやレポート全体のレコードを累計して得られた合計を指します。

Running sum in a report

集計実行を作成する

ナビゲーションウィンドウでレポートを右クリックし、ショートカットメニューのボタンの画像(デザインビュー) をクリックします。

[デザイン] タブの [コントロール] で [テキストボックス] をクリックします。 Button image

[詳細] セクション、[グループヘッダー] セクション、または [グループフッター] セクション内でクリックして、テキストボックスを作成します。

テキストボックスの横にラベルが表示されている場合は、ラベルを削除するか、テキストをわかりやすい値に変更します。

テキストボックスを選択します。プロパティシートが表示されていない場合は、F4 キーを押して表示します。

[データ] タブをクリックします。[コントロールソース] ボックスに、集計を作成するフィールドの名前または式を入力します。

たとえば、”明細金額” フィールドを集計対象にする場合は「明細金額」、グループレベルで集計を行う場合は「=Sum([明細金額])」という式を入力します。

[集計実行] ボックスをクリックします。

プロパティボックスのドロップダウン矢印をクリックし、次のいずれかの操作を行います。

1 つ上位のグループレベルに到達した時点で、集計を 0 にリセットする場合は、一覧の [グループ全体] をクリックします。

レポートの最後まで集計を行う場合は、一覧の [全体] をクリックします。

プロパティシートを閉じます。

注 “RunningSum/集計実行” プロパティを “OverAll/全体” に設定すると、[レポートフッター] セクションで総計を繰り返すことができます。レポートフッターにテキストボックスを作成し、その “ControlSource/コントロールソース” プロパティをデータを集計するテキストボックスの名前 (たとえば、”=[受注金額]”) に設定します。

【AccessVBA】レポートの罫線を最後まで引くときに使うReport.MoveLayout プロパティ

2015年2月19日AccessVBA開発Jomo

MoveLayout プロパティの設定値は次のとおりです。

設定値	内容
True	セクションの “Left/左位置” および “Top/上位置” プロパティは、次の印刷位置まで移動します。(既定値)
False	セクションの “Left/左位置” プロパティおよび “Top/上位置” プロパティは、変更されません。

Reports("Purchase Order").MoveLayout = True 

【例】

Private Sub ページヘッダーセクション_Format(Cancel As Integer, FormatCount As Integer)

Me.MoveLayout = False

End Sub

【Access】印刷すると、偶数ページだけ、ページヘッダの部分が印字されない

2015年2月19日AccessVBA開発Jomo

今回の問題は、「偶数ページだけ、ページヘッダの部分が印字されない」

⇒ページヘッダの「背景色」を「背景1」に設定したら、偶数ページにも印字されるようになった。

！！！問題解決！！！

たどり着くまで、

めっちゃ、時間がかかった。（TT)

【AccessVBA】SQL構文

2015年2月19日AccessVBA開発Jomo

SQL構文

○SQL (Structured Query Language)データベース操作用言語、リレーショナルデータベースの操作に使用します。

はじめに
 文字の表記
 コメントの書き方
 データベース操作
 テーブル操作
 トランザクション
 データの削除
 データの挿入
 データの更新
 データの出力
 テーブルの結合
 計算および関数の使い方
 条件式
 並べ替え
 フィールドをグループ化して処理
 SELECT文の結果を結合
 ビュー(クエリー)
トリガー
 複数の指定された値が存在しているかどうか
 どれか一つが一致するレコードを返す
 単一行との比較
 CASE演算子

■はじめに

Microsoft SQLServerなどで使用できます

= は比較のみ、代入機能はありません

■文字の表記==================================================================================
▼データーベース名やテーブル名、フィールド名で a-a や a a などは [a-a] [a a] または "a-a" "a a" と表記する。
▼  "  のみを入力したい場合は["]を表記する。
▼  [  のみを入力したい場合は"["とする。
▼シングルクオーテーション  '  の入力には  ''  と入れる

■コメントの書き方============================================================================
▼一行コメント
--コメント行
▼複数行コメント

/*
複数行のコメント
*/

■データベース操作============================================================================
▼データーベース(testDB)を選択します
USE testDB;

▼新しいデータベースを作成します
CREATE DATABASE testDB;

▼データーベースを削除します
DROP DATABASE testDB;

■テーブル操作================================================================================

▼テーブルの作成
CREATE TABLE テーブル名 (フィールド名1 フィールド1の型,フィールド名2 フィールド2の型);
CREATE TABLE table1 (field1 char(10),field2 int);

▼NULL文字を禁止したフィールドの作成
CREATE TABLE table1(field1 char(10) NOT NULL , field2 int);

▼テーブルの削除
DROP TABLE テーブル名

▼フィールドの変更
ALTER TABLE テーブル名 (ADD DROP....)

▽フィールドtestを追加します
ALTER TABLE table1 ADD test char(10);

▼インデックスの作成
CREATE INDEX インデックス名 ON テーブル名(フィールド名);
CREATE INDEX indexName1 ON table1(field1);

▽ユニークなインデックス
CREATE UNIQUE INDEX インデックス名 ON テーブル名(フィールド名);

▼インデックスの削除
DROP INDEX テーブル名.インデックス名;
DROP INDEX table1.indexName1;

■トランザクション============================================================================
レコードがロックされます。
トランザクションの開始を行い、対象レコードに対して更新・削除を行うと、対象レコード付近にロックがかかり、他からの対象レコードに対しての参照・更新・削除がトランザクションの完了までロックされます。

▼トランザクションの開始
BEGIN TRANSACTION;

▼トランザクションの完了
COMMIT;

▼トランザクションのキャンセル
ROLLBACK;

■レコードに参照・削除・更新禁止のロックをかける
（レコード単位のロックのため挿入はロックできません）
BEGIN TRANSACTION;
SELECT * FROM table1 WITH(XLOCK)    ←table1の対象レコードに排他ロックをかけて読み出す。
            ↑
            この間、対象となるレコードは参照・削除・更新がロックされている。
            ↓
COMMIT

これにより対象レコードに対するDELETE文、UPDATE文がロックされます。
ただし、SELECT文の場合は上記のようにロックをかけても、

SELECT * FROM table1
のように排他ロックを取得せずに読み出された場合にはロックがかかりません

SELECT * FROM table1 WITH(XLOCK)
のように排他ロックを取得するように呼び出された場合において読み出しロックが掛かります。

■トランザクションの分離レベルの設定
最初に設定するとそのセッション中有効です

▼ダーティーリード
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

他者がトランザクションでロックをしていても、その更新中のレコードを見ることができます。
データの整合性が保障されません

*****************************************************

SELECT * FROM table1;他者がロック中でも見えます

*****************************************************

▼書き込む側からのレコードのロック(デフォルト)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

ロック中のレコードはコミットされるまで他者からの読み出し、更新がロックされます(新しいレコードはロックされてないので追加はできます)
SQLServerのデフォルト設定です

*****************************************************

BEGIN TRANSACTION;
UPDATE  table1 SET field1 ='sss' WHERE field1='bbb';該当レコードのみをロック
COMMIT;

*****************************************************

▼読み出す側からのレコードのロック

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

レコード単位のロックのため、SELECTで選択されたレコード以外には変更や追加ができます
SELECT文で読み込んだレコードのみを更新できないようにロックする

*****************************************************

BEGIN TRANSACTION;
SELECT * FROM table1 WHERE field1='aaa';ココで選択されたfield1='aaa'のレコードのみトランザクション中に他から更新されないようにロックされる

COMMIT;

*****************************************************

▼直列化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

トランザクション中は、他者からの追加、更新をロックする(見ることはできる)

*****************************************************

BEGIN TRANSACTION;
SELECT * FROM table1 WHERE field1='a'; table1にロックがかかる
この間、table1に対して他者からの追加・更新はロックされる

COMMIT;

*****************************************************

■データの削除================================================================================
DELETE FROM テーブル名 WHERE 条件式

▼field1='2です'のみのレコードを削除
DELETE FROM table1 WHERE field1='2です';

▼全てのレコードを削除
DELETE FROM table1;

■データの挿入================================================================================
INSERT INTO テーブル名 (フィールド名1,フィールド名2....) VALUES (値1,値2....);
INSERT INTO table1 (field1,field2) VALUES ('1です',1);

▼SELECTでの出力をINSERTする
テーブルtable1のフィールドa,b
から
テーブルtable2のフィールドc,d
に複製する
INSERT INTO table2(c,d) SELECT a,b FROM table1;

▼SELECTの出力の全ての項目を既存のテーブルに挿入する
INSERT INTO table2 SELECT * FROM table1

▼SELECTの出力を新しいテーブルを作成して書き出す
SELECT * INTO 作成するテーブル名 FROM テーブル名;

--新しいテーブルtestを作成してSELECTの結果を書き出す
SELECT * INTO test FROM table1;

--作成したテーブルの表示
SELECT * FROM test;

--作成されたテーブルの削除
DROP TABLE test;

■データの更新================================================================================
UPDATE テーブル名 SET フィールド名1=値1,フィールド名2=値2 WHERE 条件式

▼field2=3　のレコードの field1を'test'に変更します
UPDATE table1 SET field1='test' WHERE field2=3;

▼全てのレコードのfield1を'test'に変更
UPDATE table1 SET field1='test';

■データの出力================================================================================
SELECT フィールド名 FROM テーブル名 WHERE 条件式;

▼ワイルドカードで全てのフィールドを出力する
SELECT * FROM table1;

▼列に任意の名前をつけて表示する
SELECT  field1 AS 'HELLO' FROM table1;

HELLO       
----------- 
0
1
2
3

▼重複行があった場合は一つの行にまとめられます
SELECT DISTINCT * FROM table1;

▼指定のフィールドのみ出力する
SELECT field1,field2 FROM table1;

▼複数のテーブルのフィールドを出力する
全ての組み合わせパターンが出力される
SELECT * FROM table1,table2;

▼先頭から指定行だけを返す
4行のみ返す
SELECT TOP 4 * FROM table1;

▼フィールドにNULLが含まれる行だけ返す
SELECT * FROM table1 WHERE field1 is NULL;

▼サブクエリー(副問い合わせ)
SELECTの条件式の中のSELECTをサブクエリーと言います
SELECT * FROM table1 WHERE field2=(SELECT field2 FROM table1 WHERE field2=2);

▼(実行例)
*****************************************************
SELECT * FROM table1;

field1               field2      
-------------------- ----------- 
1です                  1
2です                  2
3です                  3
4です                  4

*****************************************************
SELECT * FROM table2;

field3               field4      
-------------------- ----------- 
--4--                4
--3--                3
--2--                2
--1--                1

*****************************************************
SELECT * FROM table1,table2;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --4--                4
2です                  2           --4--                4
3です                  3           --4--                4
4です                  4           --4--                4
1です                  1           --3--                3
2です                  2           --3--                3
3です                  3           --3--                3
4です                  4           --3--                3
1です                  1           --2--                2
2です                  2           --2--                2
3です                  3           --2--                2
4です                  4           --2--                2
1です                  1           --1--                1
2です                  2           --1--                1
3です                  3           --1--                1
4です                  4           --1--                1

*****************************************************

■テーブルの結合==============================================================================

▼結合元のテーブル table1
*****************************************************
SELECT * FROM table1;

field1               field2      
-------------------- ----------- 
0です                  0
1です                  1
2です                  2
3です                  3

*****************************************************

▼結合元のテーブル table2
*****************************************************
SELECT * FROM table2;

field3               field4      
-------------------- ----------- 
--4--                4
--3--                3
--2--                2
--1--                1

*****************************************************

▼テーブルを結合しました
*****************************************************
SELECT * FROM table1,table2 WHERE field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 =*    * の付いている側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1,table2 WHERE field2=*field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
NULL                 NULL        --4--                4
3です                  3           --3--                3
2です                  2           --2--                2
1です                  1           --1--                1

*****************************************************

▼外部結合 *=    * の付いている側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1,table2 WHERE field2*=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
0です                  0           NULL                 NULL
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼テーブルを結合しました２
*****************************************************
SELECT * FROM table1 INNER JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 LEFT 側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1 LEFT JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
0です                  0           NULL                 NULL
1です                  1           --1--                1
2です                  2           --2--                2
3です                  3           --3--                3

*****************************************************

▼外部結合 RIGHT 側のテーブルのデータを全て残します
*****************************************************
SELECT * FROM table1 RIGHT JOIN table2 ON field2=field4;

field1               field2      field3               field4      
-------------------- ----------- -------------------- ----------- 
NULL                 NULL        --4--                4
3です                  3           --3--                3
2です                  2           --2--                2
1です                  1           --1--                1

*****************************************************

■計算および関数の使い方========================================================================================

▼計算

SELECT 1+2;
SELECT field2+1 FROM table1;
SELECT (SELECT field2 FROM table1 WHERE field1='2です')*(SELECT field2 FROM table1 WHERE field1='4です');

▼レコード数のカウント
SELECT COUNT(*) FROM table1;

▼最大値を出力
SELECT MAX(field2) FROM table1;

▼最小値を出力
SELECT MIN(field2) FROM table1;

▼合計を出力
SELECT SUM(field2) FROM table1;

▼平均を出力
SELECT AVG(field2) FROM table1;

▼特定のレコードの足し算は条件式で指定して計算する
SELECT SUM(field2) FROM table1 WHERE field1='1です' OR field1='4です';

▼最小値のレコードのみ削除
DELETE FROM table1 WHERE field2 = (SELECT MIN(field2) FROM table1);

▼文字列の連結
SELECT 'aa'+'bb';

▼両側のスペースを削除
SELECT RTRIM(LTRIM('   aa    '))+'bb';

▼数字を文字に変換
SELECT 'aa'+STR(10);

▼文字を数字に変換
SELECT 10 + CONVERT(int , '10');

▼NULLであるかどうか
SELECT 'NULLです' WHERE (NULL) IS NULL;

▼SELECT文の結果に行が含まれるかどうか
SELECT '行が存在します' WHERE EXISTS (SELECT * FROM table1);

▼文字の長さを出力　結果は4です
SELECT LEN('test');

■条件式========================================================================================

▼関係演算子

▽数字の比較
フィールド名 >= 値
フィールド名 = フィールド名

▽文字列が同一かどうか比較
フィールド名 = '比較文字列'

▼論理演算子
(条件式) AND (条件式)
(条件式) OR (条件式)

▽符号の反転
NOT (条件式)

▼数字を文字列に変換して比較
LTRIM(STR(field2))='1'

▼フィールドのある範囲に含まれているか判定
▽2から5の間の値の抽出
SELECT * FROM テーブル名 WHERE フィールド名 BETWEEN 2 AND 5;

▽2から5と7から9の間の値の抽出
SELECT * FROM テーブル名 WHERE (フィールド名 BETWEEN 2 AND 5) OR (フィールド名 BETWEEN 7 AND 9);

▽2から5以外の値の抽出
SELECT * FROM テーブル名 WHERE フィールド名 NOT BETWEEN 2 AND 5

▼文字列のパターン判定
指定された文字列が指定されたパターンと一致するかどうか判定
戻り値はboolです。
SELECT * FROM テーブル名 WHERE フィールド名 LIKE 'パターン文字列';

▽ワイルドカード文字-------------------------------

% : 0個またはそれ以上の文字で構成される任意の文字列
_ : 任意の1文字

---------------------------------------------------

▽フィールドが3文字のレコードのみ抽出
SELECT * FROM テーブル名 WHERE フィールド名 LIKE '___';

▽フィールドが3文字以外のレコードのみ抽出
SELECT * FROM テーブル名 WHERE NOT フィールド名 LIKE '___';

▽フィールドにAが含まれるレコードのみ抽出
SELECT * FROM table1 WHERE  field1 LIKE '%A%';

▽ b または c または d のどれかの一文字を判定するには
'[bcd]'と書きます
bcdどれかの一文字が含まれるフィールドのみ抽出
SELECT * FROM table1 WHERE field1 LIKE '%[bcd]%';

■並べ替え===================================================================================
SELECT * FROM テーブル名 ORDER BY 並べ替えるキーになるフィールド;
SELECT * FROM テーブル名  WHERE (条件式)  ORDER BY 並べ替えるキーになるフィールド;

▼昇順
ORDER BY キーになるフィールド名 ASC

▼降順
ORDER BY キーになるフィールド名 DESC

▼複数フィールドの並び替え

ORDER BY フィールド1 ASC,フィールド2 DESC,フィールド3 ASC
フィールド3→フィールド2→フィールド1の順番に並び替えられる

■フィールドをグループ化して処理================================================================

SELECT * FROM テーブル名 GROUP BY グループ化するフィールド名 HAVING 出力させる条件式

*****************************************************
SELECT * FROM table1;

field1      group1               
----------- -------------------- 
1           A                   
2           A                   
3           A                   
4           A                   
5           A                   
1           B                   
2           B                   
1           C                   
2           C                   
3           C                   
4           C            
       
*****************************************************

上のテーブルをgroup1ごとに分けて合計を出します。

*****************************************************
SELECT group1,SUM( field1) FROM table1 GROUP BY group1;

group1                           
-------------------- ----------- 
A                    15
B                    3
C                    10

*****************************************************
■SELECT文の結果を結合========================================================================
*****************************************************
SELECT * FROM table1;

field1               field2               
-------------------- -------------------- 
a                    b                   
c                    d                   
e                    f                   
g                    h                   
a                    b                   
*****************************************************
上の field1 と field2 の SELECT文を結合します
このとき重複行があった場合は一つにまとめられます。
*****************************************************
SELECT field1 FROM table1 UNION  SELECT field2 FROM table1;

field1               
-------------------- 
a                   
b                   
c                   
d                   
e                   
f                   
g                   
h       

*****************************************************
UNION ALL を指定すると全ての行が返されます
*****************************************************
SELECT field1 FROM table1 UNION ALL SELECT field2 FROM table1;

field1               
-------------------- 
a                   
c                   
e                   
g                   
a                   
b                   
d                   
f                   
h                   
b              

****************************************************
▼複数のテーブルの結合
*****************************************************
SELECT * FROM a

a_field1        a_field2
-------------------------------
a               あ
aa              ああ
*****************************************************

*****************************************************
SELECT * FROM b

b_field1        b_field2
-------------------------------
bbb             びびび
bbbb            びびびび
*****************************************************
上記テーブルを結合します
*****************************************************
SELECT * FROM a UNION ALL SELECT * FROM b

a_field1        a_field2
-------------------------------
a               あ
aa              ああ
bbb             びびび
bbbb            びびびび
*****************************************************
結合されたテーブルから条件で抽出もできます
*****************************************************
SELECT * FROM (SELECT * FROM a UNION ALL SELECT * FROM b) WHERE a_field1='bbb'

a_field1        a_field2
-------------------------------
bbb             びびび
*****************************************************

■ビュー(クエリー)============================================================================

▼ビューの作成
CREATE VIEW ビュー名 AS ビューを生成するためのSELECT文
CREATE VIEW testView AS SELECT field1 FROM table1

▼ビューの削除
DROP VIEW ビュー名
DROP VIEW testView

■トリガー====================================================================================
トランザクションの途中でもトリガーは働きます

DELETE INSERT UPDATE
が行われたときに実行される命令を規定します。
複数のレコードが影響を受けたとしても、実行されるのは一度だけです。

▼トリガーの作成
CREATE TRIGGER トリガーの名前 ON トリガーを仕掛けるテーブル名 FOR イベント名 AS 実行される命令;
CREATE TRIGGER tgrName1 ON table1 FOR DELETE AS SELECT 'DELETEが処理されました';

▼トリガーの削除
DROP TRIGGER トリガーの名前;
DROP TRIGGER tgrName1;

■複数の指定された値が存在しているかどうか=======================================================
SELECT * FROM テーブル名 WHERE フィールド名 IN(値1,値2....);
SELECT * FROM テーブル名 WHERE フィールド名 IN(SELECT文);
*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
SELECT * FROM table1 WHERE field1 IN('aaa','aaaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
aaaa                 bbbb                 1000

*****************************************************
SELECT * FROM table1 WHERE field1 IN(SELECT field1 FROM table1 WHERE field3>100);

field1               field2               field3      
-------------------- -------------------- ----------- 
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
■どれか一つが一致するレコードを返す============================================================
SELECT * FROM テーブル名 WHERE フィールド名 = ANY(SELECT フィールド名２ FROM テーブル名２);

*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
SELECT * FROM table2;

field1               
-------------------- 
aaa                 
aaaa          
      
*****************************************************
二つのレコードのfield1のどれか一つが一致するレコードを返します
*****************************************************
SELECT * FROM table1 WHERE field1=ANY(SELECT field1 FROM table2);

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
aaaa                 bbbb                 1000

*****************************************************
■単一行との比較==============================================================================
SELECT * FROM テーブル名 WHERE フィールド名 = ALL(単一行を返すSELECT文);
ALL(単一行を返すSELECT文)が単一行以外(複数の行)を返した時点で判断式はFALSEになります。
*****************************************************
SELECT * FROM table1;

field1               field2               field3      
-------------------- -------------------- ----------- 
aa                   bb                   10
aaa                  bbb                  100
aaaa                 bbbb                 1000
aaaaa                bbbbb                10000

*****************************************************
複数行を返すSELECT文の場合
*****************************************************
SELECT * FROM table1 WHERE field1 = ALL(SELECT field1 FROM table1 WHERE field1 = 'aaa' or field1='aaaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
*****************************************************
単一行を返すSELECT文の場合
*****************************************************
SELECT * FROM table1 WHERE field1 = ALL(SELECT field1 FROM table1 WHERE field1 = 'aaa');

field1               field2               field3      
-------------------- -------------------- ----------- 
aaa                  bbb                  100
*****************************************************

■CASE演算子==================================================================================
a           
----------- 
0
1
2

▼上のテーブルの値を変換し出力する
aが1ならば'１です'が出力され、それ以外なら'１以外です'と出力する。
SELECT (CASE a WHEN 1 THEN '１です' ELSE '１以外です' END) FROM test;
---------- 
１以外です
１です
１以外です

▼条件式により値を変換し出力する
SELECT (CASE WHEN a>1 THEN  '1より大きい' ELSE str(a)  END) FROM test;
----------- 
         0
         1
1より大きい


■おぼえ======================================================================================
*****************************************************
SELECT * FROM data1

str                  no                   str2      
-------------------- -------------------- ----------- 
文字列１             数字                 文字列２
*****************************************************

上記のようなテーブルにデータがあり先頭がら1000行のみ取り出し、
strの中に"で"が含まれる行を検索し、その結果をstrで並び替える場合、

①SELECT TOP 1000 * FROM data1 WHERE str LIKE '%で%' ORDER BY str
②SELECT * FROM data1 WHERE str IN (SELECT TOP 1000 str FROM data1 WHERE str LIKE '%で%') ORDER BY str

①は結果が1000以上の場合でも正しく表示するが、②は正しくない
結果が1000以下の場合には①と②は同一の結果を出す
そのため多量の検索結果がある場合は、①は非常に時間がかかる。

【AccessVBA】テーブル作成時の注意（テーブル名に「空白」を含めない）

2015年2月19日AccessVBA開発Jomo

※ADOでのレコードセットを使うときの注意

⇒rs.open　で、テーブル名を入れて開こうとすると、SQLエラーが出る

⇒テーブル名に「空白」が含まれていたことが原因

・・。テーブル作成では問題なかったので、

解明するまで、すごく時間がかかった。

ｗｗｗｗｗ

【Access】Access2013⇒Access2007移行(バージョンダウンした場合のエラー対応)

2015年2月19日AccessVBA開発Jomo

****************************************

バージョンダウンした場合のエラー対応

⇒まずは、参照設定で「参照不可●●」についているチェックを外してみる。

****************************************

【やりたいこと】
Accessで集計したデータをEXCELに出力して表示したい。
ADOも利用する

※ アクセス2000以前では、ファイルの操作はDAO（Data　Access Objects)
が標準で設定されていました。アクセス2000からは、ADOが標準で設定されています。マイクロソフトは、ファイルの操作についてはDAOからADOを
推奨していました。
その為、ADOが標準で設定されていました。でも・・・・・

アクセス2007では、ADOが標準では、設定されていません。

【環境】
Win7 32bit
Office2007（ACCESS2007、EXCEL2007）

【前提条件】
参照設定で「Microsoft EXCEL XX.X Object Library」を参照していること。
※、XX.Xは任意のバージョン番号。

「Microsoft ActiveX Data Objects 2.1 Library」にチェックを入れます。
「Microsoft Visual Basic for Application Extensibility 5.3」にも
チェックを入れたほうがいいです。