窓際の陽光
PCで個人的に役に立った事項を中心にまとめています。
何か1つでも問題解決に繋がれば、幸いです。
Access -Query-
クエリでは、各データの処理(変更や削除、データの統合・抽出)などを中心に行うことが出来ます。
メニュー
「デザインビュー」に入力
- 小数点以下の計算 （Intを用いて四捨五入、切上げ、切捨て）
- 日付の抽出条件
- レコードの空白部分の削除 （Trim、LTrim、RTrim 関数）
- 文字列から指定した文字数を抽出 （Left、Right、Mid 関数）
- データ型を変更 （CStr、CLng、CDbl、CDate 関数）
「SQLビュー」に入力
小数点以下の計算
ROUND関数をExcel同様に持っていますが、正常に処理出来合い場合があります。
これが案外厄介で、使い始めて悩むポイントの１つだと思います。方法は色々あるようですが、私が日頃使っている１例を提示。
四捨五入を行う（対象の値が正の数の場合）
フィールド名:Int([値]*10+0.5)/10
「*10」と「/10」の２ヶ所をを変更する事で少数点の位が変更出来ます。(この部分は、10の乗数表記で同じ値として下さい)
※値がマイナスの場合、切り上げします。
四捨五入を行う（対象の値が負の数を含む場合）
フィールド名:IIf([値]<0,Int([値]*10+0.49)/10,Int([値]*10+0.5)/10)
Fix関数を使った方が楽ですが、Int関数で行う場合はこれで良いと思います。
「*10」と「/10」の２ヶ所をを変更する事で少数点の位が変更出来ます。(この部分は、10の乗数表記で同じ値として下さい)
切上げを行う
フィールド名: IIf([値]>0,Int([値]*10+0.9)/10,Int([値]*10)/10)
「*10」と「/10」の２ヶ所をを変更する事で少数点の位が変更出来ます。(この部分は、10の乗数表記で同じ値として下さい)
切捨てを行う
フィールド名: IIf([値]>0,Int([値]*10)/10,-Int(-[値]*10)/10)
「*10」と「/10」の２ヶ所をを変更する事で少数点の位が変更出来ます。(この部分は、10の乗数表記で同じ値として下さい)
日付の抽出条件
１ヵ月分のデータを抽出する①（日付）
日付より抽出を行う場合、Between関数を使用して日付の範囲を指定するのが一般的だと思います。
（下記の式は、2010/04/01～2010/04/30の範囲で抽出した場合）
Between #2010/04/01# and #2010/04/30# （データ型が日付/時刻型の場合）
Between "2010/04/01" and "2010/04/30" （データ型がテキスト型の場合）
処理を行っているPCのシステム時間を用いて下記のような方法もあります。
Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)
Andの前の「1」と末尾の「0」の辺りを変える事で色々出来ます。
１ヵ月分のデータを抽出する②（日付）
処理を行っているPCのシステム時間を用いて下記のような方法もあります。
Like Format(DateAdd("m",0,Date()),"yyyy/mm") & "*" （今月分のデータを抽出）
数字の部分（0、-1、1）の部分を変更する事で月を変更出来ます。(現在月は、処理しているPCのシステム時間を引用します)
１ヵ月分のデータを抽出する③（日付）
こうする事で、実行の際に入力ボックスが表示され、入力で抽出できます。
Between [開始日を入力して下さい。] And [終了日を入力して下さい。]
レコードの空白部分を削除
外部からのデータを引用した場合、必要とする文字列以外に空白を含んでいる場合があります。下記の方法で削除が可能です。
フィールド名: Trim([対象のフィールド名])
Trimは文字列の前後にある空白を削除します。（基本はこれで良いと思う）
TrimをLTrimに変更すると先頭に空白がある場合、削除します。
TrimをRTrimに変更すると文字列の後に空白がある場合、削除します。
文字列から指定した文字数を抽出
Excel同様にLeft、Right、Mid関数を使用する事が出来ます。
フィールド名: Mid([対象のフィールド名],開始位置,指定数) （指定する範囲の文字列を抽出）
フィールド名: Left([対象のフィールド名],指定数) （左から指定数の文字列を抽出。LeftをRight変更すると右から抽出）
指定数、開始位置には、数値（0,1,2…）を入力します。
データ型を変更
CStr、CLng、CDbl、CDate関数などを使用するとフィールドのデータ型を変更出来ます。
既存テーブルのデータ型変更はこちらを参考。
フィールド名: CStr([対象のフィールド名]) （文字列型へ変更）
フィールド名: CLng([対象のフィールド名]) （長整数型へ変更）
フィールド名: CDbl([対象のフィールド名]) （倍精度浮動小数点数型へ変更）
フィールド名: CDate([対象のフィールド名]) （日付/時刻型へ変更）
クエリ同士でリレーションシップを使用した際のデータ型不一致を解消出来ます。（元のクエリ側を直す必要がありますが･･･）
また、テーブル作成クエリで作成されるデータ型もこれで変更出来ます。
しかし、変更後の書式にデータが合っていない場合は適用されません。
複数のテーブルまたは、クエリを集約（SQL使用）
ユニオンクエリを用いて複数のテーブルまたは、クエリを集約します。
ORDER BYで並び替えを行っています。（不要の場合は削除）
全体を集約
SELECT * FROM [テーブル(クエリ)名] UNION ALL SELECT * FROM [テーブル(クエリ)名]
ORDER BY [フィールド名１],[フィールド名２];
持っているフィールド数が違う場合は、作成出来ません。集約後のフィールド名は1つ目のものとなります。
フィールドを決めて集約
SELECT [フィールド名1],[フィールド名2] FROM [テーブル(クエリ)名]
UNION ALL
SELECT [フィールド名1],[フィールド名2] FROM [テーブル(クエリ)名]
ORDER BY [フィールド名1],[フィールド名2];
*部分をフィールド名にして、特定のフィールドだけを集約する事も可能。
文は長くなりますが、不要フィールドを省いたり、フィールドの並びも変更出来るため便利。
また、UNION ALLをUNION変更すると重複データを削除します。
既存テーブルにフィールド追加またはデータ型変更（SQL使用）
クエリよりテーブルにフィールドを追加する方法。
クエリでは1つずつしか行えないため、複数行う場合はこちら。
テキスト型フィールドを追加（フィールドサイズ：5）
ALTER TABLE [テーブル名] ADD COLUMN [対象のフィールド名] TEXT(5);
長整数型フィールドを追加
ALTER TABLE [テーブル名] ADD COLUMN [対象のフィールド名] LONG;
倍精度浮動小数点型フィールドを追加
ALTER TABLE [テーブル名] ADD COLUMN [対象のフィールド名] DOUBLE;
日付/時刻型フィールドを追加
ALTER TABLE [テーブル名] ADD COLUMN [対象のフィールド名] DATETIME;
オートナンバー型フィールドを追加
ALTER TABLE [テーブル名] ADD COLUMN [対象のフィールド名] COUNTER;
ADD COLUMNをALTER COLUMNに変えると既存のデータ型を変更出来ます。
しかし、変更後の書式に合っていないデータは無くなってしまう為、注意が必要。
既存テーブルのフィールドを削除（SQL使用）
クエリよりテーブルのフィールドを削除する方法。
クエリでは1つずつしか行えないため、複数行う場合はこちら。
ALTER TABLE [テーブル名] DROP COLUMN [対象のフィールド名];
既存テーブルのデータを置換（SQL使用）
更新クエリでReplace関数を使用して文字を置換。
クエリでは1つずつしか行えないため、複数行う場合はこちら。
UPDATE [テーブル名] SET [対象のフィールド名] = REPLACE([対象のフィールド名],'検索語','置換語');
数値型の場合は以下のようにします。（空欄のフィールドを0で置換）
UPDATE [テーブル名] SET [対象のフィールド名] = 0 WHERE [対象のフィールド名] IS NULL;