アクセス テーブル 列を行にしたいのですが・・・・

質問

アクセス初心者ですが、ご教授頂けませんでしょうか？＜abab123＞

・2つのテーブルがあります。
・入力者の都合上、どうしても行入力にできず、列入力になってしまった為、
テーブルを作り直す必要があります。

＜ 数量 テーブル ＞

機器｜ 商品1｜商品2｜・・ ｜商品100
-----------------------------------
1 ｜ 10 ｜ 20 ｜・・｜ 30
2 ｜ 40 ｜ 50 ｜・・｜ 60
・
・
10000｜ 70 ｜ 80 ｜・・｜ 90

＜ 単価 テーブル ＞

機器 ｜商品1 ｜商品 2｜・・｜商品100
--------------------------------------
1 ｜1,000 ｜ 2,000 ｜・・｜ 3,000
2 ｜ 4,000 ｜ 5,000 ｜・・｜ 6,000
・
・
10000｜ 7,000｜ 8,000 ｜・・｜ 9,000

・こんなテーブルにしたいのですが。

＜ 結合 テーブル ＞

機器　｜　 商品 ｜ 数量 ｜ 単価
----------------------------------------
1 ｜ 1 ｜ 10 ｜ 1,000
1 ｜ 2 ｜ 20 ｜ 2,000
1 ｜ ・ ｜ ・ ｜ 　・
1 ｜ 100 ｜ 30 ｜ 3,000
・ ｜ ・ ｜ ・ ｜ ・
・ ｜ ・ ｜ ・ ｜ ・
10000 ｜ 100 ｜ 90 ｜ 9,000

□ EXCELで、行列を入れ替えようと思ったのですが
機器（10,000） X 商品（100）＝ 1,000,000 行となり、
65,536行 の EXCEL2003では、断念しました。
VBAも考えましたが、16回（=1,000,000/65,536）、
EXCEL を ACCESSテーブルに貼り付けるのも？？？

Webで、アクセスSQL・・ユニオン・・・などがヒットするのですが、
良く解らなくて。。。です。

結合テーブルを効率的に作成する方法を、ご教授頂けませんでしょか？
　 宜しくお願い申し上げます。

□ アクセスはデーター処理で毎日使用しておりますが、
難しい処理をする訳ではなく、
テーブル作成、クロス集計等から、EXCELへ書き出し、
EXCELの資料を作成したりしています。

回答No.7

chayamati

添付を差し替えてください

回答No.6

chayamati

お早うございます。また誤りがありました度々済みません
テーブルは次の4件です

１．T-機器
２．T-商品

３．T-構成
・構成ID（オートナンバー型 主キー）
・機器ID（数値型→長整数型）
... ルックアップ機能を利用してT-機器を参照設定をする

４．T-構成明細
・構成明細ID（オートナンバー型 主キー）
・商品ID（数値型→長整数型）
... ルックアップ機能を利用してT-商品を参照設定をする
・数量

リレーションシップ
リレーションシップはリレーショナルデータベースの基本となる部分です

添付を見てください
[T-機器]と[T-商品]の間に[T-構成]-[T-構成明細]を挟んでいます。
いずれのつながり(リレーションシップと呼ぶ)も一方は主キーになっています。
このリレーションシップで
10000件の機器と100件の商品との1000000件の組み合わせが実現します
実際には各機器は数件～数十件でよい筈ですが。

クエリーは1～複数のテーブルの一部のフィールドを抜き出して定義します
定義したクエリーはフォーム、レポート等のレコードソースになります。
クエリーは論理上のテーブルと理解して良いでしょう

各テーブルには規格、重量、単位、仕切単価等のフィールドを追加、
商品の点数が100を超えても運用中のシステムに影響がありませんので
システムの拡張が容易です。

回答No.5

chayamati

前回の回答まで思い違いがありました
１つの商品は機器Ａにも機器Ｇにも必要で必要数はそれぞれ異なると言うことで
商品と機器はＮ対Ｍ に対応となるわけですね
其の為に追加のテーブル（T-構成）が必要になります。
最初の回答のときテーブル設定に大きな誤りがありました。
申し訳ございません。

テーブルは次の３つになります。

１．T-機器
・機器ID （オートナンバー型 主キー）
・機器名 （テキスト型）
・機器コード（数値型→整数型）

２．T-商品
・商品ID （オートナンバー型 主キー）
・商品名 （テキスト型）
・商品コード（数値型→整数型）
・商品単価 （数値型→長整数型）

３．T-構成
・構成ID（オートナンバー型 主キー）
・機器ID（数値型→長整数型）
... ルックアップ機能を利用してT-機器を参照設定をする
・商品ID（数値型→長整数型）
...ルックアップ機能を利用してT-商品を参照設定をする
・数量

この３つのテーブルをデータとして作成したのが
添付のフォームです。
図中
(1)フィールドセレクターと呼び
...機器Dの位置に 横三角 がありこれの商品構成が下枠になります
...この最後に＊があります、ここが機器構成の追加行です

(2)機器名欄に機器名が表示されますがここには機器IDが入っています
...テーブル定義でルックアップを設定しましたのでコンポボックスです
(3)各種移動ボタンの組で中央の3/6は機器構成の総数6中3番目、
...1/2これの構成商品の総数2中1番目を示しています。
...一見取り違えそうですが機器構成の枠の中に商品構成の枠があります
...レコードの移動はこれで行いますがレコード数が画面からあふれると
...右端にスクロールバーが出てスクロール移動も可能になります

図中白字部分は自動表示するので入力の必要は有りません
図は上下に分かれています。

上枠
・コード 次の機器選択が確定すると表示
・機器の選択入力（右端の▼をクリックすると登録されている
...機器の一覧がリストされますこの中から選択します。）
...ただし機器の種類が10,000件となるとスクロールだけでは無理があります。
...事前の絞込の仕掛けが必要です。
...絞込は機器コード、機器の種類、得意先、フリガナ等

下枠
・コード 次の商品名選択が確定すると表示
・商品名選択入力
▼で３０件程度のリストが可能でので商品100点以内とすると
,,,３回以内のスクロールで見つかります
・数量 入力
・単価 商品名確定 でT-商品の単価を表示
・金額 ＝数量×単価

以上ですが、今のところACCESSの基本部分だけでマクロ、ＶＢＡは考えていません

Accessでは基本部分から初めても拡張が簡単です。
１、フィールドの追加
商品ではふりがな、規格、重量、購入単価 機器では ふりがな
２．商品の必要量の集計
．．在庫管理も可能ですが色々な取引が関わりますので先の課題になります。

運用は先ず機器名、商品名をそれぞれ登録してからこのフォームで
商品構成を登録することになります。

今回は以上ですが、システム変更の決心されましたらコメント下さい
自分は定年後の独学ですので中途半端な知識しか持ち合わせていませんが
出来るだけお手伝いしたいと思っています。

回答No.4

chayamati

添付忘れてました

回答No.3

chayamati

決心がつきましたか

フィールドの詳細設定をする一つの方法はテーブルを開いた状態で
１．［テーブル名のタグを右クリック］
２．［デザインビュー］

これでデザインビューになります。デザインビューは上下に分かれています
・上の枠：フィールドとデータ型を登録
・下の枠：上枠で選ばれたフィールドの詳細

［ルックアップタグ］をクリック
＿未設定のときは表示コントロールのみですがこれの
＿右端をクリックすると表示コントロールの種類がリストされ［コンボボックス］を選ぶと
＿必要な項目が表示されます
以下の項目は（数値項目以外）入力枠の右端クリックでリスト選択になります

・表示コントロール
・値集合タイプ
・値集合ソース
＿定義したテーブルのリストから選択
＿表示するフィールドを選択、表示順の設定

・連結列（既定値は1）
＿参照テーブルの 主キーのフィールド位置

・列数（既定値は1）
＿参照するフィールドの数 通常テーブルは
＿主キー、名称と配置するので 2

・列見出し（既定値は いいえ）
＿列見出しの表示、非表示選択

・列幅
＿表示する列の幅をセミコロン区切りで定義、最後の列幅は省略可
＿主キーはオートナンバーのため参照の意味がないので 0
＿参照テーブルのフィールドには主キーが入いるが2番目の商品名を表示

・リスト行数（ 既定値は16）
＿商品の種類が32で2回のスクロールでほとんど参照できます

・リスト幅（規定値は自動）

以下省略
10/3迄留守にします。

回答No.2

chayamati

Excelの処理をAccessでも可能ですが限界があります。
・商品の種類は100を超えないですか。
・入力する画面には空白のままのセルが多くスクロールを頻繁にしなければならない

勘違いかもしれませんが、商品は機器の構成要素のような気がします。

ならば次のような2つのテーブルで可能だと思います。

１．T-商品
・商品ID （オートナンバー型 主キー）
・商品名 （テキスト型）
・商品コード（数値型→整数型）
・商品単価 （数値型→長整数型）

１．T-商品
・機器ID （オートナンバー型 主キー）
・機器名 （テキスト型）
・機器コード（数値型→整数型）
・商品ID（数値型→長整数型）
ルックアップ機能を利用してT-商品を参照設定をする

以上で添付のような入力フォームが可能になります。
ここで入力するのは 機器コードと機器名を入力して
商品名をプルダウンメニューから選択、数量を入力すると
商品コード、単価は商品テーブルから自動で呼び出し
金額は数量×単価 より求めてくれる
この自動表示のフィールドはタブsトップを［いいえ］にしてあります

テーブルの定義ができたら
・リレーションシップの設定
・フォームの作成と進んで下さい

追伸 テーブル設定で各フィールドに既定値、IME入力モードの設定
等ありこれらを丁寧に設定しておくとクエリー、フォーム、レポートに反映しますので丁寧にチェックして下さい。

補足
数値型に色々種類がありますが、それぞれ最大値の制限があります。
バイト型 2の８乗ー１＝255以内
整数型 2の15乗ー１＝32,767以内
長整数型 2の31乗ー１＝2,147,483,647 以内
これ以上の数値は通貨型にして桁数指定します。

回答No.1

30246kiku

一気にやろうと思ったら、確かに UNION 等でてきます。
初心者といえども、簡単にやりたいんですよね。

※ 以下を確かめられる時は、テスト用の環境で行ってください。

まず、考え方から

「商品1」について考えてみます。
・数量 テーブル のテーブル名を「T数量」
・単価 テーブル のテーブル名を「T単価」
・結合 テーブル のテーブル名を「T結合」
とします。

クエリのSQLビューに以下を記述します。

SELECT Q1.機器, Q1.商品, Q1.数量, Q2.単価 FROM
(SELECT 機器, 1 AS 商品, 商品1 AS 数量 FROM T数量) AS Q1 INNER JOIN
(SELECT 機器, 1 AS 商品, 商品1 AS 単価 FROM T単価) AS Q2 ON Q1.機器=Q2.機器 AND Q1.商品 = Q2.商品;

この状態で表示を確認してみます。
「商品2」も表示したい、となったら

SELECT Q1.機器, Q1.商品, Q1.数量, Q2.単価 FROM
(SELECT 機器, 1 AS 商品, 商品1 AS 数量 FROM T数量) AS Q1 INNER JOIN
(SELECT 機器, 1 AS 商品, 商品1 AS 単価 FROM T単価) AS Q2 ON Q1.機器=Q2.機器 AND Q1.商品 = Q2.商品
UNION ALL
SELECT Q1.機器, Q1.商品, Q1.数量, Q2.単価 FROM
(SELECT 機器, 2 AS 商品, 商品2 AS 数量 FROM T数量) AS Q1 INNER JOIN
(SELECT 機器, 2 AS 商品, 商品2 AS 単価 FROM T単価) AS Q2 ON Q1.機器=Q2.機器 AND Q1.商品 = Q2.商品;

の様に UNION ALL で必要分記述していきます。
（";" は一番最後にあれば良いので、UNION ALL 前の ";" は削除します）
「商品100」まで表示したい、となったら
　　100 AS 商品, 商品100 AS 数量
　　100 AS 商品, 商品100 AS 単価
になるまで、数字を変更しながら同じようにドンドン追加していきます。
後はそれを、テーブル作成クエリに変更すればテーブルは出来上がります。

これ手作業でやっていきますが、面倒くさい・・・と思ったら VBA に挑戦してみます。
以下を標準モジュールに記述し実行すれば、テーブル「T結合」が出来上がります。

Public Sub TableMake()
　　Dim sSqlBase(1) As String
　　Dim iPhase As Integer
　　Dim sSql As String
　　Dim i As Long

　　sSqlBase(0) = "SELECT Q1.機器, Q1.商品, Q1.数量, Q2.単価 INTO T結合 FROM " _
　　　　& "(SELECT 機器, {%1} AS 商品, 商品{%1} AS 数量 FROM T数量) AS Q1 INNER JOIN " _
　　　　& "(SELECT 機器, {%1} AS 商品, 商品{%1} AS 単価 FROM T単価) AS Q2 ON Q1.機器=Q2.機器 AND Q1.商品 = Q2.商品;"

　　sSqlBase(1) = "INSERT INTO T結合(機器, 商品, 数量, 単価) " _
　　　　& "SELECT Q1.機器, Q1.商品, Q1.数量, Q2.単価 FROM " _
　　　　& "(SELECT 機器, {%1} AS 商品, 商品{%1} AS 数量 FROM T数量) AS Q1 INNER JOIN " _
　　　　& "(SELECT 機器, {%1} AS 商品, 商品{%1} AS 単価 FROM T単価) AS Q2 ON Q1.機器=Q2.機器 AND Q1.商品 = Q2.商品;"

　　On Error Resume Next
　　DoCmd.DeleteObject acTable, "T結合"
　　On Error GoTo 0
　　iPhase = 0
　　For i = 1 To 100
　　　　sSql = Replace(sSqlBase(iPhase), "{%1}", i)
　　　　CurrentDb.Execute sSql
　　　　iPhase = 1
　　Next
　　RefreshDatabaseWindow
End Sub

商品１つの基本となるSQL文を、テーブル作成用、追加用の２種類用意しておきます。
1 ～ 100 の数字が変わる部分は、置換えしやすいような文字列にしておきます。
（ 上記の例では {%1} を ）

「T結合」テーブルがあったら削除してから、
・商品1 の時は、テーブル作成
・商品2 以降は、追加
処理が終わったら、データベースウィンドウの再表示

「T結合」が既にあって、内容を変更するだけ・・・なら、記述は変わってきます。

※ 転記、確認される場合があれば、教えてgoo ではない提携サイトから
　　コピー＆貼り付けしてください。
　（現在の教えてgoo では、不要な半角スペースが行頭に付加されるようです）