Access2003 vbaでの正規化

質問

エクセルで作った表を、データが1万件近くなって重くなってきたので
アクセスに移行しようと考えています。
エクセルのフォーマットのままではアクセスでは扱いにくいので正規化したいのですが、
データ量が多いのでvbaでできたらなと思います。

【Excel】
商品｜原料１│原料２│原料３・・・
ああ│＿×＿│＿＿＿│＿×＿
いい│＿×＿│＿×＿│＿＿＿　

【Access】
商品マスターと原料マスターを作っておいて

商品ID｜原料ID｜×
＿１＿｜＿１＿｜×
＿１＿｜＿２＿｜
＿１＿｜＿３＿｜×
＿２＿｜＿１＿｜×
＿２＿｜＿２＿｜×
＿２＿｜＿３＿｜

というようにしたいです。

よろしくお願いします。

回答No.3

piroin654 · ベストアンサー

Excelのシートの先頭行は、質問のように
商品｜原料１│原料２│原料３・・・
となっているものとします。先頭行が
ああ│＿×＿│＿＿＿│＿×＿
からいきなり始まっている場合はコードを
変更する必要があるのでその場合は補足
してください。

手順を以下に説明します。

(1)テーブルの用意
使用原料：
商品　テキスト型
原料　テキスト型
使用　テキスト型

使用原料一覧：
商品ID　数値型
原料ID　数値型
使用　　テキスト型

(2)ExcelのSheetのリンク設定
シートをテーブルとしてリンクします。
以下に方法を記述しています。
http://oshiete1.watch.impress.co.jp/qa6524626.html
途中の設定で、「先頭行をフィールド名として使う」
にチェックをいれます。

(3)標準モジュールに以下を貼り付ける。
DAOを使っているので、コード表のツールから
参照設定を選択して、
Microsoft DAO xx Object Library
にチェックを入れて、OKとします。xxは3.6のような数字です。
このとき、
Microsoft ActiveX Data Objects xx Library
にチェックが入っていたらはずしてください。
なお、Sheet1はリンクしたExcelのシート名ですが、
シート名が設定してある場合は変更してください。

Sub test()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim fld As Field
Dim i As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Sheet1")
Set rs2 = db.OpenRecordset("使用原料", dbOpenDynaset)

If rs1.RecordCount > 0 Then
rs1.MoveFirst
Do Until rs1.EOF
For i = 1 To rs1.Fields.Count - 1
rs2.AddNew
rs2!商品 = rs1!商品
rs2!原料 = rs1.Fields(i).Name
rs2!使用 = rs1.Fields(i).Value
rs2.Update
Next i
rs1.MoveNext
Loop
End If

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

(4)クエリの設定
以下のSQL文をあたらしいクエリの
SQLビューに貼り付け保存してください。
名前は、「Q変換」としておきます。

INSERT INTO 使用原料一覧 ( 商品ID, 原料ID, 使用 )
SELECT 商品マスタ.商品ID, 原料マスタ.原料ID, 使用原料.使用
FROM (使用原料 INNER JOIN 商品マスタ ON 使用原料.商品 = 商品マスタ.商品) INNER JOIN 原料マスタ ON 使用原料.原料 = 原料マスタ.原料;

(5)実行手順
新しいフォームにボタンを二つ設定します。
ボタンのクリック以下を設定します。

Private Sub コマンド0_Click()
Call test
End Sub

Private Sub コマンド1_Click()
DoCmd.OpenQuery ("Q変換")
End Sub

実行はコマンド0、コマンド1の順で行ないます。
コマンド0を実行し、テーブルを確認し、コマンド1
を実行しテーブルを確認してみてください。

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

お礼

できました！
ご丁寧にどうもありがとうございました。

回答No.2

piroin654

商品マスタ、原料マスタを作り、商品別原料詳細の
ようなものに、Excelのデータを移植したい、という
ことですか。

たとえば、

商品ID｜原料ID｜使用
＿１＿｜＿１＿｜×
＿１＿｜＿２＿｜
＿１＿｜＿３＿｜×
＿２＿｜＿１＿｜×
＿２＿｜＿２＿｜×
＿２＿｜＿３＿｜

あるいは、

商品ID｜原料ID｜使用量
＿１＿｜＿１＿｜10
＿１＿｜＿２＿｜
＿１＿｜＿３＿｜20
＿２＿｜＿１＿｜50
＿２＿｜＿２＿｜30
＿２＿｜＿３＿｜

のようなテーブルを作成したい、
あるいは、フォームやクエリで
表示したい、ということですか。

補足

説明がわかりにくく、すみません。

商品ID｜原料ID｜使用
＿１＿｜＿１＿｜×
＿１＿｜＿２＿｜
＿１＿｜＿３＿｜×
＿２＿｜＿１＿｜×
＿２＿｜＿２＿｜×
＿２＿｜＿３＿｜

はい、まさにこういうテーブルが作りたいです。

回答No.1

imogasi

もう少し質問内容を丁寧に説明のこと。
自分のようには他人にはわからない。
ーー
商品マスター・・・商品ＩＤ、商品名＋あと項目は何？
原料マスタ・・・　ＩＤ、原料名＋あと項目何？
商品と使用原料のリストはつくる？
両ペアーの対で持つ？
その方が融通性はあるが、どういう使い方をするかで、固定的かどうかで、ベターかどうか決る。これは
他人には判らない。
ーー
ＶＢＡでやるなら、こんなところへ聞いても妙案は無い。アイデアを出すだけ。
適当なソート列でソートして、重複を省いてマスタといったファイルを作れば良い。アイデアよりも力仕事。
ーー
質問のＡｃｃｅｓｓ側のＸはなに？
使うというサイン？
Ｘでやるより
＿１＿｜＿１＿
＿１＿｜＿３＿
＿２＿｜＿１＿
＿２＿｜＿２＿
の方が良くは無いのか？
これなら、エクセルから商品と原料ペアーを右端列まで、および最終行まで書き出せば仕舞いでは？。

補足

うまく説明できないので、整理してからまた質問することにします。
ありがとうございました。