ACCESSのクエリを使用してデータ更新したい

質問

Office2010のAccessを使用しています。
以下のようなことをしたいのですが、どのようにクエリを設定すればよいか教えてください。
処理内容は、FileAに対してKeyマッチするFileBの２項目に更新し、FileCを出力する。

FileA：メンテナンス前データ
FileB：更新データ
FileC：メンテナンス後データ

FileA：固定長のテキストデータ
Key（７桁）＋金額１（８桁）＋金額２（８桁）＋区分（２桁）
※金額１（前ブランク埋めのカンマ編集）
※金額２（前ゼロ埋め）

FileB：カンマ区切りのテキストデータ
Key（７桁）＋金額１（８桁）＋金額２（８桁）

例）
FileA
0000001 90,00000090000AA
0000002 90,00000090000AA
0000003 90,00000090000BB

FileB
0000002,30000,30000

FileC
0000001 90,00000090000AA
0000002 30,00000030000AA
0000003 90,00000090000BB

初心者なもので、ハードコピーなどで図解解説があれば助かります。
よろしくお願いいたします。

回答No.11

piroin654 · ベストアンサー

あとは、そちらでされるようなので、必要なことだけ
書いておきます。

一応、半角の数字と半角のスペースを前提に
していますが、
たとえば、以下のようなデータがあるとします。
列が3、行が6あります。「**」はスペースが2つ。
列間は便宜上全角のスペースになっていますが、
一応、半角のスペース。つまり、列間の区切りは
半角スペースとしています。カンマ切りの場合も
以下では同様です。
表示上、形がくずれるかもしれません。

0000001**90,00000090000AA　0000002**90,00000090000DD　0000003**90,00000090000BB
0000002**90,00000090000CC　0000003**90,00000090000BB　0000004**90,00000090000SS
0000003**90,00000090000BB　0000002**90,00000090000CC　0000002**90,00000090000CC
0000004**90,00000090000EE　0000005**90,00000090000AA　0000006**90,00000090000BB
0000002**90,00000090000CC　0000003**90,00000090000BB　0000001**90,00000090000AA
0000003**90,00000090000PP　0000002**90,00000090000CC　0000005**90,00000090000QQ

Do Until EOF(1)
　　Line Input #1, buf1

と、

Loop

の間で、データの取得は、固定長データの文字数を25、
列数を3とすると、

For j = 1 To 3
　Debug.Print Mid(buf1, (25 + 1) * (j - 1) + 1, 25)
Next j

　実際はここで、
　Mid(buf1, (25 + 1) * (j - 1) + 1, 25)
　によって取得したデータに対して一つ一つ
　加工していきます。

とすると、取得したデータがイミディエイトウィンドウに
縦に表示されます。
(25 + 1)は、25文字と半角のスペース1という、意味です。
また、For j = 1 To 3　の3は列数を表します。列数が10ならば、
For j = 1 To 10　となります。
テキストへの出力は、一応、以下のbuf1は変更されたデータとしますと、

For k = 1 To 3
　　If k = 3 Then
　　ss = ss & Mid(buf1, (25 + 1)* (k - 1) + 1, 25)
　　Else
　　　　ss = ss & Mid(buf1, (25 + 1) * (k - 1) + 1, 25) & " "
　　End If
Next k
　　Debug.Print ss
ss = ""

のようにすると、イミディエイトウィンドウにテキストへの
出力と同じ形態で表示されます。ssはその都度1行のデータを表示します。

これらのことを把握されているならば、
ほぼ出来上がるだろうと、思いますが。

お礼

本当に最後までお付き合いいただきありがとうございました。
とても感謝しています。

回答No.17

chayamati

VBAなら

PrivateSub FileC作成_Click()
DoCmd.RunSQL "DELETE * FROM TblB;"
DoCmd.RunSQL "INSERT INTO TblB ( [key], 金額1, 金額2 ) SELECT FileB.key, FileB.金額1, FileB.金額2 FROM FileB;"
DoCmd.RunSQL "IIf(Len([TblB]![金額1])>3,Left([TblB]![金額1],Len([TblB]![金額1])-3) & ", " & Right([TblB]![金額1],3),[TblB]![金額1])"
DoCmd.RunSQL "UPDATE TblB SET TblB.金額1 = Left("" "",8-Len([TblB]![金額1])) & [TblB]![金額1], TblB.金額2 = Left(""00000000"",8-Len([TblB]![金額2])) & [TblB]![金額2];"
DoCmd.RunSQL "DELETE *FROM FileC;"
DoCmd.RunSQL "INSERT INTO FileC ( [key], 金額1, 金額2, 区分 ) SELECT FileA.key, FileA.金額1, FileA.金額2, FileA.区分 FROM FileA;"
DoCmd.RunSQL "UPDATE FileC, TblB SET FileC.金額1 = [TblB]![金額1], FileC.金額2 = [TblB]![金額2] WHERE (((FileC.key)=[TblB]![key]));"
End Sub

とするとFileC作成ボックスをクリックで処理が終了します。
上記コーディングは難しそうですが、クエリのデザインビューで右クリックからSQLレビューで表示されます

回答No.16

chayamati

回答No.15を全面修正です。

以下はクエリーだけの処理です

1.FileBをTblBに複写
2.TblBの金額１にカンマ挿入
3.TblBの金額1にスペース挿入、金額2に0挿入
4.FileAをFileCに複写
5.FileCをTblBで更新

以上5つのクエリー実行で完了します
ACCESS2010での手順をやります。

1.FileBをTblBに複写
【作成】⇒【クエリデザイン】⇒【FileB】⇒【追加】⇒【閉じる】
⇒【全フィールドをフィールド行へドラッグ】
⇒【上の枠の空白部で右クリック】⇒【クエリの種類】⇒【追加】⇒【TblB】⇒【OK】⇒【】⇒【】⇒【】⇒【】⇒

2.TblBの金額１にカンマ挿入
【作成】⇒【クエリデザイン】⇒【TblB】⇒【追加】⇒【閉じる】
⇒【金額1をフィールド行へドラッグ】
⇒【上の枠の空白部で右クリック】⇒【クエリの種類】⇒【更新】
⇒【レコードの更新行へ　IIf(Len([TblB]![金額1])>3,Left([TblB]![金額1],Len([TblB]![金額1])-3) & "," & Right([TblB]![金額1],3),[TblB]![金額1])　と入力】

3.TblBの金額1にスペース挿入、金額2に0挿入
【作成】⇒【クエリデザイン】⇒【TblB】⇒【追加】⇒【閉じる】
⇒【金額1、金額2をフィールド行へドラッグ】
⇒【上の枠の空白部で右クリック】⇒【クエリの種類】⇒【更新】
⇒【金額１の更新行へ　Left(" ",8-Len([TblB]![金額1])) & [TblB]![金額1]　と入力】
⇒【金額２の更新行へ　Left("00000000",8-Len([TblB]![金額2])) & [TblB]![金額2]　と入力】

4.FileAをFileCに複写
【作成】⇒【クエリデザイン】⇒【FileA】⇒【追加】⇒【閉じる】
⇒【全フィールドをフィールド行へドラッグ】
⇒【上の枠の空白部で右クリック】⇒【クエリの種類】⇒【追加】
⇒【FileC】

5.FileCをTblBで更新
【作成】⇒【クエリデザイン】⇒【FileC】⇒【追加⇒【TblB】⇒【追加】⇒【閉じる】
⇒【FileCのKey、金額1、金額２をフィールド行へドラッグ】
⇒【上の枠の空白部で右クリック】⇒【クエリの種類】⇒【更新】
⇒【keyの抽出条件行に　[TblB]![key]】
⇒【金額1のレコードの更新行に　[TblB]![金額1]】
⇒【金額2のレコードの更新行に　[TblB]![金額2]】
添付参照

以上5つのクエリができたらTblBwo初期化して、
準にクエリーを実行します。

5つのクエリを順に事項しなければなりませんが、VBAならワンクリックで済みます

回答No.15

chayamati

ではクエリーでの処理です

次の3段階で如何でしょうか
1.追加クエリでFileAをFileCにコピーする
2.更新クエリでFileBでFileCを更新する
これで
0000001/ 90,000/00090000/AA
0000002/30000/30000/AA
0000003/ 90,000/00090000/BB
と更新されますが更新された2行目の金額1、金額2は有効桁数の左詰め
3.この金額部分をスペース、コンマ、0で成形します

1.追加クエリでFileAをFileCにコピーする
【作成】⇒【クエリデザイン】⇒【FileA】⇒【閉じる】⇒【全フィールドをフィールド行へドラッグ】
⇒【上窓の空白部分で右クリック】⇒【クエリの種類】⇒【追加】⇒【FileC】⇒【OK】⇒【保存】⇒【実行】⇒

2.更新クエリでFileBでFileCを更新する
【作成】⇒【クエリデザイン】⇒【FileC】⇒【追加】⇒【FileB】⇒【追加】
⇒【FileCのKey,金額１、金額2をフィールド行へドラッグ】⇒【Keyの抽出条件行にFileB!Key】
⇒【金額1のレコードの更新行にFileB!金額1】⇒【金額2のレコードの更新行にFileB!金額2】
添付参照
以下省略させていただきます
⇒【】⇒【】⇒【】⇒【】⇒

回答No.14

chayamati

piroin654 様
------------------------------ーーー
FileA
0000002 90,00000090000AA

FileB
0000002,30000,30000

FileC
0000002 30,00000030000AA
-----------------------------------------------
質問者様の補足があればよいのですが
空白文字が除去される
FileBのカンマはデータではなくフィールドの区切り

と考えて推測すると元のデータは次のようではないかと
key000002について
FileA
0000002/* 90,000/00090000/AA

FileB
0000002/***30000/***30000

FileC
0000002/* 30,000/00030000/AA

回答No.13

piroin654

yngwie0112さん、失礼します。

chayamatiさんへ。
ご指摘ありがとうございます。その点につきましては、
1、2、4と繰り返し確認しましたから、大丈夫だろうと
思います。ご指摘の中の
FileB
0000002,30000,30000
のデータにつきましては、FileBのデータは、
一番目の「30000」はFileＡのデータの金額1の
有効桁のみ表示、すなわち、FileAのデータが、
「90,000」の場合、これをFileBの「30000」で
置き換えると、「30,000」となります。FileAは

FileA：固定長のテキストデータ
Key（７桁）＋金額１（８桁）＋金額２（８桁）＋区分（２桁）
※金額１（前ブランク埋めのカンマ編集）
※金額２（前ゼロ埋め）

実際には、
0000001**90,00000090000AA
を
0000001**30,00000090000AA
変換

のように、スペース(*で代用)が入っています。
この場合は、スペースが二つです。

たしかに、「テキストデータ」ということなのですが、
とくに、「ブランク」＝「半角スペース」ということを
前提にして回答していますので、不具合があれば
補足があると、思いますが。

回答No.12

chayamati

piroin654 様 違例ですが、このこーなであなたにお知らせします。
自分の勘違いかもわかりませんが
質問の
------------------------------
FileB：カンマ区切りのテキストデータ
Key（７桁）＋金額１（８桁）＋金額２（８桁）
-----------------------------------
は全てのフィールドのデータ型はテキスト形式のようですね
金額1、金額2も実は固定長のテキストデータと解釈する

しかし質問者様がスペースも含めて記述したが
この解答欄がスペースを除去するため
0000002,30000,30000の様に固定長じゃ無くなってしまったようです。

回答No.10

piroin654

＞項目数は71個あり、修正項目は6個

1　各項目間の区切り
2　各項目の文字数
3　変更する項目（全部の項目かあるいは特定の項目か）
4　変更の内容（修正項目6っつの内容）

このようなことが、最低限必要になります。
。

回答No.9

piroin654

＞正しく作成できました。

よかった。（本音）

＞項目数は71個あり、修正項目は6個

この回答が他の項目に応用が利くのか、
それは、「修正項目は6個」の内容次第です。
修正内容が、たとえば金額の桁が一桁
違うだけで違うコードを書く必要があります。

あるいは、まったく違う処理ならば当然ながら
違うコードを書く必要があります。ただ、
修正項目数が「6」ならば、はやい話が最大で
関数を6個つくればいい、ということです。
回答は、修正項目が一つなので、関数の
形にはしていませんが。

実際のデータの長さや修正内容などがわかり、かつ
可能ならば作りますが。

すぐできあがればいいのですが、もし少し
時間がかかるようなことになれば、最近の
OKWAVEは勝手に時間切れの日数があるみたい
なので、その点は留意する必要はあるかも
しれません。

お礼

回答が遅れ申し訳ございません。
今回の件、色々教えていただいて
有難うございました。

この先はこちらでなんとか進めます。
もしまた追加で質問させていただく
場合は、お手数をおかけしますが、
よろしくお願いします。

回答No.8

piroin654

＞ただ、更新行の金額1が左ブランク1桁多い結果と
＞なりました。

「*」をスペースとすると、
FileC
0000001**90,00000090000AA
0000002***30,00000030000AA
0000003**90,00000090000BB
のようになっているということですか？
それは、、どのようなデータに対してもですか。

あるいは、
確認ですが、質問のデータについてスペースを「*」で表すと、

FileA
0000001**90,00000090000AA　　（25文字）
0000002**90,00000090000AA　　（25文字）
0000003**90,00000090000BB　　（25文字）

そこで、質問のFileBのデータでは、
FileB
0000002,30000,30000

のように、この場合は金額1は30000ですから、
変換すると、

0000002**30,00000030000AA　　（25文字）

となります。もし、FileBが、
0000002,300,3000

ならば、変換すると、
0000002*****30000003000AA　　（25文字）

のように、スペースが5入ります。これは、
固定長のデータですから、カンマも文字数
に数えられるのですが、もしFileBが
0000002,300,3000
ならば、変換すればカンマがないので、桁数
でスペースを入れる、ということであれば、
0000002****30000003000AA　　　（24文字）

のようにスペースは4となる、という
ことであれば、固定長という前提が崩れて
しまいますが、それでもいいというのであれば、
そのようにしますが、そのあたりはどうなのでしょう。

なお、こちらでは、FileBに以下のような
0000002,300000,30000000
データがあったとしても、

0000002 300,00030000000AA　　　（25文字）

のように、変換されているのですが。

お礼

失礼いたしました。
データに問題がありました。
正しく作成できました。
有難うございました。

ひとつ前の質問になるのですが、
これを応用するためには
どのようにすればよろしいでしょうか？

これ以上お付き合いいただくのも恐縮
ですので、お時間あればということで
お願いいたします。

本当に有難うございます。

回答No.7

piroin654

訂正です。いらないIF文が残っていました。

　　　　For j = 0 To UBound(strAry)
　　　　　　If Left(buf1, 7) = Left(strAry(j), 7) Then
　　　　　　　　If Left(buf1, 7) = Left(buf2, 7) Then
　　　　　　　　　　tmp = Split(buf2, ",")
　　　　　　　　　　tmp(1) = Format(tmp(1), "###,###")
　　　　　　　　　　tmp(1) = Right(Space(8) & tmp(1), 8)
　　　　　　　　　　tmp(2) = Format(tmp(2), "00000000")
　　　　　　　　　　ss = Left(buf1, 7) & tmp(1) & tmp(2) & Right(buf1, 2)
　　　　　　　　　　Print #3, ss
　　　　　　　　End If
　　　　　　Else
　　　　　　　　Print #3, buf1
　　　　　　End If
　　　　Next j

のところで、

　　　　If Left(buf1, 7) = Left(buf2, 7) Then

にかかるIF文はいらないので、

　　　　For j = 0 To UBound(strAry)
　　　　　　If Left(buf1, 7) = Left(strAry(j), 7) Then
　　　　　　　　　　tmp = Split(buf2, ",")
　　　　　　　　　　tmp(1) = Format(tmp(1), "###,###")
　　　　　　　　　　tmp(1) = Right(Space(8) & tmp(1), 8)
　　　　　　　　　　tmp(2) = Format(tmp(2), "00000000")
　　　　　　　　　　ss = Left(buf1, 7) & tmp(1) & tmp(2) & Right(buf1, 2)
　　　　　　　　　　Print #3, ss
　　　　　　Else
　　　　　　　　Print #3, buf1
　　　　　　End If
　　　　Next j

のようにしてください。

したがって、全体では、

Sub test()
　　Dim strFile1 As String
　　Dim strFile2 As String
　　Dim strFile3 As String
　　Dim buf1 As String
　　Dim buf2 As String
　　Dim strAry() As String
　　Dim tmp As Variant
　　Dim ss As String
　　Dim i As Long
　　Dim j As Long

　　'ファイルまでのパス
　　strFile1 = CurrentProject.Path & "\" & "FileA.txt"
　　strFile2 = CurrentProject.Path & "\" & "FileB.txt"
　　strFile3 = CurrentProject.Path & "\" & "FileC.txt"

　　Open strFile1 For Input As #1
　　Open strFile2 For Input As #2
　　Open strFile3 For Output As #3

　　i = 0
　　Do Until EOF(2)
　　　　Line Input #2, buf2
　　　　ReDim Preserve strAry(i)
　　　　strAry(i) = buf2
　　　　i = i + 1
　　Loop

　　Do Until EOF(1)
　　　　Line Input #1, buf1
　　　　For j = 0 To UBound(strAry)
　　　　　　If Left(buf1, 7) = Left(strAry(j), 7) Then
　　　　　　　　tmp = Split(buf2, ",")
　　　　　　　　tmp(1) = Format(tmp(1), "###,###")
　　　　　　　　tmp(1) = Right(Space(8) & tmp(1), 8)
　　　　　　　　tmp(2) = Format(tmp(2), "00000000")
　　　　　　　　ss = Left(buf1, 7) & tmp(1) & tmp(2) & Right(buf1, 2)
　　　　　　　　Print #3, ss
　　　　　　Else
　　　　　　　　Print #3, buf1
　　　　　　End If
　　　　Next j
　　Loop
　　Close #1
　　Close #2
　　Close #3
End Sub

と、なります。
不具合があれば補足してください。