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.6

piroin654

たぶん、これでいいのでは、と思いますが。
以下のコードを標準モジュールに貼り付け、
実行してみて確認してみてください。
速い、遅いは考慮していません。

なお、テキストファイルがAccessファイルと同じ
ディレクトリにあるとしています。

また、FileBに
0000002,300,3000
のような、データがあっても
0000002・・・・・30000003000AA
（・はスペース)
のように、金額1は文字分ほど前をブランクで埋め、
金額2は0で文字文ほど埋めるようにしています。

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
　　　　　　　　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
　　Loop
　　Close #1
　　Close #2
　　Close #3
End Sub

不具合があれば補足してください。

お礼

VBAのコードまで教えていただきありがとうございます。
正常に動きました。
ただ、更新行の金額1が左ブランク1桁多い結果と
なりました。どこをどう直せば良いのでしょう？

ただ、これを応用して、どのように修正すればよいのか
簡単にできるでしょうか？

ちなみに、項目数は71個あり、修正項目は6個です。

先に教えていただいた追加クエリと更新クエリを
試す方が良いでしょうか？

何度もご質問することになり恐縮です。
よろしくお願いします。

回答No.5

chayamati

10万件 やはりACCESSですね

・FileAからFileCへの追加クエリ
・FileBからFileCへの更新クエリ
の2ステップで対処します。

・FileAからFileCへの追加クエリ
1.クエリウィザードでFileCをソースデータとして選択クエリーを作成
2.このクエイを右クリック→デザインビュー
3.FileCのある FileCの表示のある枠内の任意の場所を右クリック
4.クエリーの種類ー追加と進みFileA-OK で レコードの追加行が表示
5.追加行の各列をクリックすると、フィールドリストより対応フィールド
6.デザインビューを閉じて、実行するFileCと同じものがFileCに追加される
※ Ckey が主キー設定されている為重複レコードは弾荒れます。

・FileBからFileCへの更新クエリ
1.クエリウィザードでFileCをソースデータとして選択クエリーを作成
2.このクエイを右クリック→デザインビュー
3.FileCのある FileCの表示のある枠内の任意の場所を右クリック
4.クエリーの種類ー更新と進みFileB―OK で レコードの更新行が表示
5.CKeyの抽出条件行に、[FileB]![Key] と入力
6.C金額１、C金額2のレコードの更新行に [FileB]![金額1]、[FileB]![金額2]
7.デザインビューを閉じて、実行するFileCと同じものがFileCに追加される
この更新クエリを添付しておきます。

以上ですが、
10万件以上の処理は経験がありません。どの程度時間が必要か興味があります
差し障りなければ,FileAのフィールド数と所要時間を押しええください。

回答No.4

piroin654

遅くなりましたが、もう一点だけ確認させてください。

金額1は、

＞90,000(左2桁ブランクとカンマ付9万)

となっていますが、左2桁ブランクは常にブランクのまま
ですか。つまり、金額1は8桁まで拡大できる、ということですから

12,345,678

という金額も出てくる可能性がありますが、この場合は
金額1の先頭のブランクはどうなりますか。あるいは
金額1のデータには限界がありますか。というのも、
VBAコードの中では、桁数と文字数と桁切りとブランクの扱い
を厳密にしておかないと、VBAコードが成立しません。
たとえば、

　　1234567

をカンマも含めて変換すると、

　　1,234,567

のように桁取りをするようになりますが、このとき
文字数はこれで、8文字になります。同様に

　　12345678

は、

　　12,345,678

となり、文字数は10となります。
このような変換をしてもいいのでしょうか。あるいは
金額1には表示される桁数に限界があるのでしょうか。
文字数が、「左2桁ブランクとカンマ付9万」の合計9文字
で表現されるならば、

　　1234,567

のように、100万のところ、あるいは

　　1,234567

のように、1000のところにはカンマをいれられません。
このあたりは、どうなのでしょう。ブランクの扱いと、
桁数と桁切りと文字数の関連はどのようになりますか。

実際に運用される場合は多分桁数なり、文字数は変っているのだろうと
おもいますが。このあたりの法則を確認しておかないと、VBAコード中の
桁数や文字数を変更しやすいように設定できません。

お礼

遅くまでご確認いただきありがとうございます。

ご質問の件ですが、

金額１は８桁までの制約があります（固定長のため）。
ですので、結局「△999,999」１０万の位までしか表示できません。

ちなみに金額２も８桁の制約がありますが、
カンマが入らないので、「99999999」1000万の位まで表示できます。

このような回答で大丈夫でしょうか？
よろしくお願いします。

回答No.3

chayamati

このような変換はをEXCELでは比較的簡単です。
Accessクエリにこだわる理由があるのですか

EXCElで変換した後accessへインポートされては

お礼

色々とご検討ありがとうございます。

実際にはFlieAは１０万件を超えるデータで、修正したい項目も１０個程度あります。
そこで単純にFileBのデータの項目のみスリカエたいのですが、FileAのフォーマットが
印刷イメージだったりするので、Accessの方が簡単にできるのではないかと思い込んで
いました。Excelでもやり方次第で簡単にできるのでしょうか？

確かにAccessにこだわっているわけではないので、よい方法があればご教示ください。

よろしくお願いします。

回答No.2

piroin654

テキストファイルならば、いずれにしても
ファイル間のデータ移動なのでVBAで
ゴリゴリするようになります。

ファイルAの金額1と金額2を合わせた桁数は、
金額１（８桁）＋金額２（８桁）　＝　16桁
ですが、表示されているデータは、

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

で、13桁となっています。これは、実際には
表示されているデータは変化するということですか？
FileA：固定長のテキストデータ
と、記述してあるのでこのあたりが不明なのですが。

それと、補足をいただいた中で、

＞FileBのことだと思いますが、
＞FileAに合わせた桁数を最大桁数としており、
＞データは有効桁数分だけとなります。

となっていますが、質問の中のデータだけを
例にすると、

0000002 90,00000090000AA

を、

0000002 30,00000030000AA

に書き変える場合、

90,00000090000

を

30,00000030000

への変更は、

90,000000

を

30000+00

に。

90000

を

30000

にそれぞれ置き換え、合わせて、

90,00000090000

を、

30,00000030000

へ変更するということですか。それとも
FileAの金額２（８桁）の処理には、
他に法則性というものがあるのでしょうか。

金額２（前ゼロ埋め）となっているので、
FileAの
0000002 90,00000090000AA
の金額2は、
90000
ではなく、
00090000
ということですか？
そうすると、金額1は、
90,000000
ではなく、
90,000
ということでしょうか。
何となく、FileA：固定長のテキストデータ
ということと折り合いがつかないような気が
するのですが。

お礼

ご確認いただき有難うございます。

まずFileAは25桁のデータになります。

項目を縦に並べると
0000002(7桁のkey番号)
90,000(左2桁ブランクとカンマ付9万)
00090000(左前ゼロの9万)
AA(2桁の区分値)

FileBのkey番号がマッチする上記データを

0000002(FileAとマッチ)
30000(金額1をスリカエ)
30000(金額2をスリカエ)

上記スリカエ時、FileAのフォーマットに
従い、それぞれ

30,000(左2桁ブランクと3万)
00030000(左前ゼロの3万)

とする。

これをFileCに出力する感じです。

いかがでしょうか？

回答No.1

piroin654

確認ですが、

1　FileA、FileB、FileCというのはテキストファイル
　のことですか、あるいはテーブル名ですか。

2　表示のデータと桁数があっていませんが
　実際はどちらが正しいのですか。

お礼

ご回答ありがとうございます。
ご質問について、回答します。

1について
テキストファイルです。
2について
FileBのことだと思いますが、
FileAに合わせた桁数を最大桁数としており、
データは有効桁数分だけとなります。

よろしくお願いします。