Accsee初心者 テーブル変換について

質問

お世話になります。

Accessのテーブルについて教えてください。
テーブルAをテーブルBに変換したいのですが、いろいろとやってみましたが上手くできませんでした。
(Ａｃｃｅｓｓ2003です)

テーブルA
コード　2011前　2010後 　2010前　・・・・・・・・・・まだまだ続きます。
A001　　　10　　　　　　　　　　1
B002　　　　　　　　　3
C003　　　 5
・
　・
　・
まだまだ続きます。

テーブルB
年/月　　　　コード　　数
2011前　　　A001 10
2010後　 　 B002 3
2010前　　　C003　 5

レコード数が何十万とあるので、変換するのに時間があまりかからない方法を教えて頂ければ助かります。
よろしくお願いします。

回答No.4

m3_maki

> コード　2011前　2010後 　2010前　・・・・・・・・・・まだまだ続きます。

まず問題点と疑問点

(1)　「コード」と変換対象の「2011前」のようなフィールド以外のフィールド（例えば「合計」のような）はないか？
(2)　現時点で変換するフィールド数はいくつ？
(3)　変換は定期的に行うものではなく、今回１度のみで良いか？
(4)　出来あがったテーブルのデータを年月で並べ替えると
　　　　「前」と「後」が前後します。　（文字コードは 前 ＞ 後 ですから。）
　　　　　2001後、2001前、2002後、2002前、・・・・ のように。

上記のうち問題点をクリアする方法はいろいろありますが、とりあえずすべて無視。

> レコード数が何十万とあるので、変換するのに時間があまりかからない方法を教えて頂ければ助かります。

一般に、SQL で処理するほうが、レコードセットで順に読み書きしていくより高速です。

レコードセットの開くモードにもよりますが
大きなテーブル全体を読む場合など、メモリ上で処理しきれない場合に
ワークファイルに書き出してから処理されますので
ある程度以上のサイズになると、極端に遅くなりますね。

私の低スペックマシン （Celelon 1.46GHz、メモリ　2ギガ、Vista、 Access2007、 2000形式MDB） で テストしてみました。

条件：
　　変換するフィールド数　10
　　入力レコード数　10万 （Null値 はテストのための 1個 のみ。 出力レコード数 99万9999 になる）
　　出力テーブルを作成しておく。（インデックス なし）
　　　　フィールド：　年月（テキスト型）、コード（テキスト型）、数（長整数型）
　　入力テーブル、出力テーブルとも閉じておくこと。
　　MDB を開いた直後に実行する。
　　（繰り返し実行していると、だんだん遅くなっていきます。メモリの解放等、いまく行ってないのでしょうね）

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Sub TabConv()
　　Dim FieldNameAra() As String
　　Dim strSQL As String
　　Dim dbs As DAO.Database
　　Dim tdf As DAO.TableDef
　　Dim i As Integer

　　Set dbs = CurrentDb
　　'フィールド名の配列を作成
　　With dbs.TableDefs("テーブルA")
　　　　If .Fields.Count > 1 Then
　　　　　　ReDim FieldNameAra(1 To .Fields.Count - 1)
　　　　　　For i = 1 To .Fields.Count - 1
　　　　　　　　FieldNameAra(i) = .Fields(i).Name
　　　　　　Next
　　　　Else
　　　　　　MsgBox "テーブルが不正です"
　　　　　　Exit Sub
　　　　End If
　　End With
　　'変換開始
　　Debug.Print Time$()
　　For i = LBound(FieldNameAra) To UBound(FieldNameAra)
　　　　strSQL = "INSERT INTO テーブルB ( コード, 年月, 数 ) " & _
　　　　　　　　"SELECT コード, '" & FieldNameAra(i) & "' AS 年月, [" & FieldNameAra(i) & "] AS 数 " & _
　　　　　　　　"FROM テーブルA " & _
　　　　　　　　"WHERE [" & FieldNameAra(i) & "] Is Not Null"
　　　　 dbs.Execute strSQL
　　Next
　　Debug.Print Time$()
End Sub
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

結果：
　　約１３秒
　　１フィールドごとの処理ですから、時間はフィールド数に比例すると思われます。
　　同じ条件で、No2 さんのコードを実行すると 約４０秒かかりました。
　　こちらは、No3 さんのテストでもわかると思いますが、
　　フィールド数が多くなると極端に遅くなります。
　　（私の低スペックマシンでは、やってみる気にもなれない）

　　なお、出力テーブルに、「年月、コード」で 主キーを作成しておくと
　　３分以上かかりました。
　　主キーなしのテーブルに出力してから、そのテーブルに主キーを作成するのは、ほぼ瞬時。

質問者さんがまだ見ているか分かりませんが、
おもしろそうなのでやってみました。

回答No.3

nora1962

とりあえず、項目を255(コードと254フィールド)定義して、10万件データを埋めてDAOとADOでテーブルA→テーブルBに変換してみました。NULL値の項目はありません。コード例はすでに提示されているのと同様レコードセットをAddNew→Updateで実行しました。
一応DAOでほぼ1時間、ADOで45分程度です。
（実行環境はQuod Core 2.66GHz メモリ 4GB ACCESS 2007 ）

結果2,540,000件のレコードが生成されました。

しかし、これはテーブルBに主キー（コード＋年/月）を定義していない状況での数値です。

ここで、やや心配事があります。

・テーブルAでNULLでないフィールド、すなわちテーブルBのレコード数はどれぐらいになるのでしょうか。
・10万件というのはテーブルAの件数ですか、それとも予想されるテーブルBの行数でしょうか。

すなわち、ACCESSでコードに索引をつけても百万件を超えるデータで、今後も処理が可能なのかという疑問です。
いらぬ心配ならばよいのですが。

回答No.2

piroin654

回答をアップしようと思ったらすでに
回答が寄せられていたのですが、ついでに
アップしておきます。

ユニオンクエリも考えたのですが、連結できる
クエリの数に限りがあるので、もし年/月が
4年とか5年分だと多分エラーになるのでは
ということで抜きにしました。
ユニオンクエリの方法は、No1のnicotinismさんの
方向でいいのではと思います。

(1)
フォームにボタンの設定します。たとえばボタンの名前を
「コマンド0」とします。

(2)
ボタンのクリック時のイベントに、以下を
設定します。

DAOを使っているので、コード表のツールから
参照設定を選択して、
Microsoft DAO xx Object Library
にチェックを入れて、OKとします。xxは3.6のような数字です。
このとき、
Microsoft ActiveX Data Objects xx Library
にチェックが入っていたらはずしてください。

Private Sub コマンド0_Click()
Dim db As Database
Dim rsA As Recordset
Dim rsB As Recordset
Dim i As Long

Set db = CurrentDb
Set rsA = db.OpenRecordset("テーブルA")
Set rsB = db.OpenRecordset("テーブルB", dbOpenDynaset)

If rsA.RecordCount > 0 Then
Do Until rsA.EOF
For i = 1 To rsA.Fields.Count - 1
If Not IsNull(rsA.Fields(i).Value) Then
rsB.AddNew
rsB![年/月] = rsA.Fields(i).Name
rsB![コード] = rsA.Fields(0).Value
rsB![数] = rsA.Fields(i).Value
rsB.Update
End If
Next i
rsA.MoveNext
Loop
End If

rsA.Close: Set rsA = Nothing
rsB.Close: Set rsB = Nothing
db.Close: Set db = Nothing
MsgBox "終了"
End Sub

以上です。わからないところがあれば補足してください。

回答No.1

nicotinism

ユニオンクエリで地道に連結して、そのクエリを元にテーブル作成クエリを
作って行うのが速いかもしれないが
http://www.sk-access.com/Syo_Query/SqA013_Union.html

select "2011前" as 年月 ,コード,2011前 as 数 from テーブルA
union all
select "2010後" as 年月 ,コード,2011後 as 数 from テーブルA
union all
select "2010前" as 年月 ,コード,2010前 as 数 from テーブルA
・・・・・・・以下フィールドの最後まで同様に・・・・・・・

↑で出来たクエリの名前をQ1としたらテーブル作成クエリは
SELECT Q1.* INTO テーブルB FROM Q1

あるいはレコードセットを回して。
DAOに参照設定がされていなければ・・
Alt + F11 を押してVBEの画面にして
メニュー→ツールから参照設定を選択
参照可能なライブラリから、Microsoft DAO 3.6 Object Library を探して
チェックボックスにチェックを入れます

メニュー→挿入から標準モジュールを選択
新たな窓が出てくるので、下記をコピペ
Ctrl ＋ G を押してイミディエイトウィンドウを出して
そこに chgtbl と入力し Enter
(入れ物(テーブルB)は作っておいてね）

Sub chgTbl()
　　Dim db As DAO.Database
　　Dim rsFrom As DAO.Recordset
　　Dim rsTo As DAO.Recordset
　　Dim fC As Integer

　　Set db = Application.CurrentDb
　　Set rsFrom = db.OpenRecordset("テーブルA", dbOpenSnapshot)
　　Set rsTo = db.OpenRecordset("テーブルB", dbOpenDynaset)
　　fC = CurrentDb.TableDefs("テーブルA").Fields.Count
　　　　Do Until rsFrom.EOF
　　　　　　For fC = 1 To fC
　　　　　　　　rsTo.AddNew
　　　　　　　　　　rsTo!年月 = CurrentDb.TableDefs("テーブルA").Fields(fC).Name
　　　　　　　　　　rsTo!コード = rsFrom!コード
　　　　　　　　　　rsTo!数 = rsFrom.Fields(fC)
　　　　　　　　rsTo.Update
　　　　　　Next fC
　　　　　　rsFrom.MoveNext
　　　　Loop
　　rsFrom.Close: Set rsFrom = Nothing
　　rsTo.Close: Set rsTo = Nothing
　　db.Close: Set db = Nothing
　　MsgBox "おしまい"
End Sub

どちらも方向性は間違ってはいないと思いますが、ボケをかましていそうで一抹の不安 (^^ゞ
１００レコード位ので試してみて問題なさそうなのを確認してから本番をお勧め。
おかしな点があったら詳しく状況を説明してください。
※年/月のスラッシュは取り去って年月とした方が後々の幸せかと思う（Accessが/を演算子と誤解）