アクセス リストボックスから選択したデータを抽出条件にするには…

質問

アクセス超初心者です。

フォーム上のリストボックスで選択した項目を条件にデータ抽出しています。
そこで、その抽出されたデータの中に商品コードがあるのですが、
その商品コードを条件にして他のデータを抽出したいと思っています。

（↓説明が長いですが、お願いします↓）

出来ていること
フォームでコードを入力すると、商品名が表示されます。
同じくコードを条件にリストボックスにカテゴリ１表示され、
そのリストのいずれかをクリックすると、別のリストボックスにカテゴリ２が表示され、
そのいずれかをクリックすると詳細に必要情報が表示される。
http://accessclub.info/beginer/cd/part_07.htm
ここを参考に作成しました。

したいこと
ランキング作成。
今回新たにテーブルを追加しました。
リストボックス_カテゴリ２を条件に抽出されたデータにある商品コードを、
条件として、新たに追加したテーブル_ランキングからデータを抽出したい。
また、ランキングの上位順に並び替えした状態で抽出したい。
-------------------
抽出結果はこのようにしたいです。
ランキング順位/商品コード/商品名/生産工場/カテゴリ１/カテゴリ２
-------------------
T_商品名
ID（主キー）
商品コード
商品名
生産工場
カテゴリＩＤ※T_カテゴリ_IDとリレーション
-------------------
T_カテゴリ
カテゴリ１　・・・（大分類カテゴリ）
カテゴリ２　・・・（大分類の中の詳細分類）
ID（主キー）※T_商品名_カテゴリＩＤとリレーション
-------------------
（今回は新たに追加したテーブル）
T_ランキング
ランキング順位
商品コード
商品名
ポイント
ID（主キー）（リレーション無し）
-------------------
F_総合検索
　ヘッダー部分
　　(1)コード入力欄（テキストボックス非連結）
　　(2)商品名（テキストボックス非連結）
　　(3)カテゴリ１（リストボックス）
　　(4)カテゴリ２（リストボックス）
　　(5)ボタン
------
　詳細部分
　　　商品コード/商品名/生産工場/カテゴリ１/カテゴリ２
-------------------
しくみ
　I　(1)にコードを入力する
　　→II　(2)に商品名が表示/(同時に)(3)カテゴリ１が表示（そのいずれかをクリック）
　　→III　その商品が属しているかつ、選択されたカテゴリ１に属す、カテゴリ２が(4)に表示
　　→IV(4)のクリックされたカテゴリを条件に、そのカテゴリに属すレコードが詳細に表示
　　　※(5)ボタンは詳細の表示と同じものを別途クエリを作成し、開くようにしています。
　　　　(今はまだランキングが出来ていないので、クエリをコピーしエクセルでランキングを作っています。)

---補足--------------------
もともとエクセルで管理していたデータをインポートしてテーブルを作成しています。
------------------------------------------------------------
【カテゴリシート】商品コード/商品名/生産工場/カテゴリ１/カテゴリ２
というシート(4500件くらい)
※商品コード重複あり
例：
商品コード　商品名　生産工場　カテゴリ１　カテゴリ２
0003　キャラクターペン　東京　玩具　対象小学生以上
0003　キャラクターペン　東京　文具　筆記用具
0003　キャラクターペン　東京　文具　消耗品
※カテゴリ１　・・・（大分類カテゴリ）/カテゴリ２　・・・（大分類の中の詳細分類）
------------------------------------------------------------
【ランキング2007シート】ランキング順位/商品コード/商品名/ポイント/調査日
というシート（4000件くらい）
※商品コード重複なし
※ランキングは廃盤の商品コード（100件くらい）もあり、その商品に関しては順位とポイントが空欄です。
　　（空欄セルがあるとエラーの原因になるかと思い、念のため補足。）
------------------------------------------------------------
ここから、ある商品はどのカテゴリに属しているか、また同カテゴリに含まれる商品はランキング内で何位にいるのか。
という情報を出すように、と仕事で依頼されます。（同類商品の中でどう評価されているのかを比較するため）
いつもは、エクセルで作業シートを作っており、関数で呼び出しているのですが、
関数が複雑かつ多いせいか、計算時間が長く、効率が悪くなっています。

VBA、マクロ、SQLはわかりません。
そんな私でも学習していきたいと思っています。
よろしくお願いします。

回答No.9

noname#140971 · ベストアンサー

補足:商品枝番!カテゴリ1等と分類名テーブルとの関連
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

カテゴリ1!表示コントロール=コンボボックス
値集合ソース=テーブル/クエリ
値集合ソース=SELECT 枝番大分類.ID, 枝番大分類.名称 FROM 枝番大分類 ORDER BY 枝番大分類.並び順;
連結列=1
列数=2
列幅=0cm;3cm

Access では、枝番大分類！名称も名称で各テーブルが記憶することはしません。
あくまでも参照するに必要な索引のみを記憶します。
こうすることで、名称を登録ミスしても自由に訂正できる訳です。
こうすることで、顧客は名称をちと変更したいにも対応できる訳です。

[並び順]はクエリ等で参照した場合の並びを制御するためのもの。
通常は登録順で参照されますが、決して、これは保証の限りではありません。

01:大分類名01
02:大分類名02

と名称そのものが昇順に揃うようにして対応することも可能でしょう。
が、これはあくまでも顧客との話し合いの上で決めること。
私は、この方式を押し通しています。
まあ、「01、02で選択できるよ！」が説得文句です。

補足

ありがとうございます！
番号で簡潔に管理、選択できるという利点ということでしょうか。
確かに分類が多かったりする場合は、目視でもNo.リストがあって、照らし合わせたりしますね。
効率的を第一にアドバイスしてくださって感謝です！
本当にありがとうございました！！

回答No.8

noname#140971

[商品コード]に応じて大分類[リスト0]を更新する。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

商品: テーブル

[ID]_[商品コード]_[商品名___________]_[生産工場]_[ポイント]_[ランキング]
__1___0003_________キャラクタキペン１__東京_______98.5________________1
__2___0004_________キャラクタキペン２__東京_______92.0________________3

商品枝番: テーブル

[ID]_[商品_ID]_[枝番名_____________]_[カテゴリ１]_[カテゴリ２]
__1_________1___キャラクタペン１_01___文具_________その他_1
__2_________1___キャラクタペン１_02___文具_________文具_2
__3_________2___キャラクタペン２_01___玩具_________玩具_1
__4_________2___キャラクタペン２_02___玩具_________玩具_2
__5_________1___キャラクタペン１_03___その他_______その他_1

枝番大分類: テーブル

つまり、’キャラクタキペン１’が選ばれたら大分類は’文具’と’玩具’ということ。

[イミディエイト]
? DBSELECT("SELECT DISTINCT [カテゴリ1], DBLookup('名称', '枝番大分類', 'ID=' & [カテゴリ1]) FROM 商品枝番 WHERE [商品_ID]=1", ";")
1;文具;3;その他;
? DBSELECT("SELECT DISTINCT [カテゴリ1], DBLookup('名称', '枝番大分類', 'ID=' & [カテゴリ1]) FROM 商品枝番 WHERE [商品_ID]=2", ";")
2;玩具;

検索・参照フォームの初手の課題は、＜[商品コード]に応じて大分類[リスト0]を更新する＞だと思います。

リスト?!値集合タイプ=値リスト
リスト?!列数=2
リスト?!列幅=0cm;3cm

Dim lngID　　 As Long

Private Sub リスト0_AfterUpdate()
　　Me.リスト1.RowSource = DBSelect( _
　　　　　　　　　　　　　　　　　　　 "SELECT DISTINCT [カテゴリ2]," & _
　　　　　　　　　　　　　　　　　　　 "DBLookup('名称', '枝番中分類', 'ID=' & [カテゴリ2])" & _
　　　　　　　　　　　　　　　　　　　 " FROM 商品枝番 WHERE [商品_ID]=" & lngID)
End Sub

Private Sub 商品コード_AfterUpdate()

　　lngID = DBLookup("ID", "商品", "[商品コード]='" & Me.商品コード & "'", 0)
　　If lngID > 0 Then
　　　　Me.リスト0.RowSource = DBSelect( _
　　　　　　　　　　　　　　　　　　　 "SELECT DISTINCT [カテゴリ1]," & _
　　　　　　　　　　　　　　　　　　　 "DBLookup('名称', '枝番大分類', 'ID=' & [カテゴリ1])" & _
　　　　　　　　　　　　　　　　　　　 " FROM 商品枝番 WHERE [商品_ID]=" & lngID)
　　　　Me.リスト0.Value = Me.リスト0.ItemData(0)
　　　　リスト0_AfterUpdate
　　End If
End Sub

これでＯＫです。
テストは一回のみしかしていませんが、[商品枝番]に存在する各商品に対応する大分類と中分類をこれで抽出する筈です。

さて、ここではＶＢＡコードを簡略化するために DBSelect関数と DBLookup関数を用いています。

※　Access のプログラミングは正に千差万別です。
※　コンボボックスやリストボックスへの値のセットは常に発生します。
※　で、いちいちプログラムコードを書いていたのでは幾ら時間があっても足りません。
※　ですから、その辺りは関数化する訳です。

以上で下部に対応するデータを表示すれば検索・参照フォームは完成すると思います。
頑張って下さい。

ublic Function DBSelect(ByVal strQuerySQL As String, _
　　　　　　　　　　　　 Optional ByVal strSeparator1 As String = ";", _
　　　　　　　　　　　　 Optional ByVal strSeparator2 As String = ";") As String
On Error GoTo Err_DBSelect
　　Dim I　　 As Integer
　　Dim J　　 As Integer
　　Dim R　　 As Integer
　　Dim C　　 As Integer
　　Dim M　　 As Integer
　　Dim N　　 As Integer
　　Dim rst　 As ADODB.Recordset
　　Dim fld　 As ADODB.Field
　　Dim Datas As String

　　Set rst = New ADODB.Recordset
　　' =================
　　'　Begin With: rst
　　' -----------------
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　M = .RecordCount - 1
　　　　　　N = .Fields.Count - 1
　　　　　　.MoveFirst
　　　　　　For R = 0 To M
　　　　　　　　For C = 0 To N
　　　　　　　　　　Datas = Datas & .Fields(C)
　　　　　　　　　　If C <> N Then
　　　　　　　　　　　　Datas = Datas & strSeparator1
　　　　　　　　　　End If
　　　　　　　　Next C
　　　　　　　　If Len(strSeparator2) Then
　　　　　　　　　　Datas = Datas & strSeparator2
　　　　　　　　End If
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　 End If
　　End With
　　' ---------------
　　'　End With: rst
　　' ===============
Exit_DBSelect:
　　DBSelect = Datas
　　Exit Function
Err_DBSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSelect)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSelect
End Function

Public Function DBLookup(ByVal strField As String, _
　　　　　　　　　　　　 ByVal strTable As String, _
　　　　　　　　　　　　 Optional ByVal strWhere As String = "", _
　　　　　　　　　　　　 Optional ByVal ReturnValue = "") As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT " & strField & " FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

***************************************************************

どうするかは質問されないがいいです。
「こうしようと思うが、ここが判らない！」という質問がいいです。
なりゆきで最後まで答えましたが、噛みあっているか不明。
で、ステップ毎に質問されたがいいです。
回答は、幾つかのステップを段階を追って攻めています。
で、こういうステップ毎に流儀も作法も千差万別。
ですから、自分の流儀でステップ毎に質問。
そうであれば、幾多の回答が集中すると思いますよ！

***************************************************************

お礼

ありがとうございます！

教えていただいたことを試してみます！
試してからだと遅くなってしまうので、
先にお礼まで。

アドバイスの通り、
>「こうしようと思うが、ここが判らない！」という質問がいいです。
という風に、質問からして見直します（＾＾；）

個人レッスンみたいになってしまってすいませんでした。。。
いろいろありがとうございます！
今回の回答が全て理解し、活用できるまで学習頑張ります！

回答No.7

noname#140971

【Microsoft Access のヘルプ】-UPDATE ステートメントの使用例

UPDATE Employees SET ReportsTo = 5 WHERE ReportsTo = 2;"

【テーマ】テーブル[商品]の列[ランキング]の値を更新。

そのためには、Microsoft Access のヘルプのUPDATE ステートメントの使用例を参照。
その例にならって以下のクエリを作成。

クエリ3: クエリ

UPDATE 商品 SET [ランキング]= _
　　IIf(Len([ポイント] & ""),DBCount("*","商品","[ポイント]>" & [ポイント])+1,-1)

[ポイント]が空値でなければ--->DBCount()で現行より上位のポイントを持つレコード数を求めて＋１．
[ポイント]が空値であるなら--->-1

空値かどうかを＜Len([ポイント] & ""＞で判定するやり方は Microsoft のヘルプで見られるやり方。
＜[ポイント] is not null＞と書くかどうかは趣味と好みの問題。
ともあれ、以下のように[ランキング]列が更新されます。

商品: テーブル

[ID]_[商品コード]_[商品名]____________[生産工場]_[ポイント]_[ランキング]
__1___0003_________キャラクタキペン１__東京_______98.5________________1
__2___0004_________キャラクタキペン２__東京_______92.0________________3
__3___1085_________香り消しゴム________東京_______96.3________________2
__4___7563_________下敷き______________東京_______32.0________________5
__5___4826_________シャープペン________東京_______廃盤_______________-1
__6___9325_________卓上ホルダー________東京_______廃盤_______________-1
__7___0005_________キャラクタキペン３__東京_______92.0________________3

※SQL文に IIf関数やDBCount関数を組み込んで安直なUPDATE文を作成するのは一種の邪道。
※Access だから許される手法。

【標準モジュールにDBCount()をコピペ】

Public Function DBCount(ByVal strField As String, _
　　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　　Optional ByVal strWhere As String = "", _
　　　　　　　　　　　　Optional ByVal ReturnValue = 0) As Variant
On Error GoTo Err_DBCount
　　 Dim N
　　 Dim strQuerySQL As String
　　 Dim rst　　　　 As ADODB.Recordset

　　 Set rst = New ADODB.Recordset
　　 strQuerySQL = "SELECT COUNT(" & strField & ") FROM " & strTable
　　 If Len(strWhere) > 0 Then
　　　　 strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　 End If
　　 With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = .Fields(0)
　　　　 End If
　　 End With
Exit_DBCount:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBCount = IIf(N <> 0, N, ReturnValue)
　　 Exit Function
Err_DBCount:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBCount)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 Resume Exit_DBCount
End Function

※Access には DBCount関数と同じ働きをするDCount関数が用意されています。
※ですから、別に DBCount関数を用いる必要はありません。
※ただ、クエリやSQL文の実行速度は１：３程度と思われます。
※高速処理を目指さなければDCount関数でも宜しいと思います。

回答No.6

noname#140971

＜既に完成した部分の確認＞

商品大分類: テーブル

[ID]_[並び順]_[名称]
__1________1___文具
__2________2___玩具
__3________3___その他

商品中分類: テーブル

[ID]_[商品大分類_ID]_[並び順]_[名称]
__1_______________1________1___文具_1
__2_______________1________2___文具_2
__3_______________2________1___玩具_1
__4_______________2________2___玩具_2
__5_______________3________1___その他_1
__6_______________3________2___その他_2

リスト0!値集合ソース=SELECT 枝番大分類.ID, 枝番大分類.名称 FROM 枝番大分類 ORDER BY 並び順;
リスト1!値集合ソース=SELECT 商品中分類.ID, 商品中分類.名称 FROM 商品中分類 WHERE (((商品中分類.商品大分類_ID)=Forms!フォーム1!リスト0)) ORDER BY 商品中分類.並び順;

Private Sub Form_Load()
　　Me.リスト0.Value = Me.リスト0.ItemData(0)
　　リスト0_AfterUpdate
End Sub

Private Sub リスト0_AfterUpdate()
　　Me.リスト1.Requery
End Sub

一応、これで大分類のリストが選ばれると中分類のリストが対応するものにチェンジします。

質問１、[カテゴリ1]は、テーブル[商品大分類]![ID]なのか？
質問２、[カテゴリ2]は、テーブル[商品中分類]![ID]なのか？

[カテゴリ1][カテゴリ2]が具体的な分類名だとすると全てが文字列検索になるかと思います。
主マスターの主キーは長整数、純データテーブルの主キーは整数ないしバイト型(0-255)。
そうすれば、検索速度も改善すると思います。

※テーブル[商品大分類]と[商品中分類]とをメインとサブフォームで管理。
※これは、顧客の要求の核心部に応えるには必須な作業かと推察しました。
※で、[商品大分類]![ID]と[商品中分類]![ID]とをキーにテーブル[商品]を[ランキング]付きで高速表示。
※こういうことかと思います。
※ならば、邪道ではありますが[商品]![ランキング]という列を生成するのも手だとは思います。
※次の回答は、その要領に関してです。

補足

現在完成しているテーブルについて。

T_商品
ID　商品コード　商品名　　　　　　　生産工場　　ポイント
1　　0003　　　　キャラクターペン　　東京　　　　　98.5
2　　1085　　　　香り消しゴム　　　　東京　　　　　97.6

T_商品枝番
ID　商品ID　枝番　カテゴリ１　カテゴリ２
1　　1　　 1-1　 玩具　　　　対象小学生以下
2　　1　　 1-2　 文具　　　　筆記用具
3　　1　　 1-3　 文具　　　　消耗品
4　　2　　 2-1　 文具　　　　筆記用具
5　　2　　 2-2　 文具　　　　消耗品

ですが、これとは別に、
検索用リストボックスのために
商品大分類: テーブル
商品中分類: テーブル
を作るということでいいのでしょうか？
>質問１、[カテゴリ1]は、テーブル[商品大分類]![ID]なのか？
>質問２、[カテゴリ2]は、テーブル[商品中分類]![ID]なのか？
というのは、上記の解釈でいうと、新しく作った商品各分類の各テーブルのIDということでしょうか？
[並び順]というのは上から下への連番でしょうか？
先日作成した商品枝番はどのような役割になりますか？

その場合、商品コードとのリレーションはどのようになりますか？
商品コードを元に商品大分類（カテゴリ１）を選抜、そこから商品中分類（カテゴリ２）を選抜。
という検索フォームで行きたいと思っています。

>※で、[商品大分類]![ID]と[商品中分類]![ID]とをキーにテーブル[商品]を[ランキング]付きで高速表示。
今、自分で既に作成済みのフォームで、[詳細]に表示される元データテーブル自体にランキングとポイントデータを追加(エクセルで作成しインポート)して、表示させ、並べ替えさせてしまうというのがいいのでしょうか。

回答No.5

noname#140971

次は、連休明けに・・・。

１、ランキングはポイント順に、１、２、３、３、５となるのは先の回答の通りです。
２、その生成方法は別に難しいことではありません。

ここまでくれば、その生成方法は誰かが答えてくれると思います。
そして、全て、当初の問いに答えることが可能な状況が出現。
ですから、連休明けはお呼びではないでしょう。

補足

ありがとうございます!

>１、ランキングはポイント順に、１、２、３、３、５となるのは先の回答の通りです。
>２、その生成方法は別に難しいことではありません。
>ここまでくれば、その生成方法は誰かが答えてくれると思います。
お助けください!!
SQLとかVBAとかでしょうか？

>そして、全て、当初の問いに答えることが可能な状況が出現。
どんな状況でしょうか？

>ですから、連休明けはお呼びではないでしょう。
およびしたい限りですが。。。
>次は、連休明けに・・・。
私の自宅のPCにはアクセスが入っていないので、
是非連休明けによろしくお願いします。

回答No.4

noname#140971

＜ランキングシートに関する確認＞

商品: テーブル

[ID]_[商品コード]_[商品名_____________]_[生産工場]_[ポイント]
__1___0003_________キャラクタキペン１____東京___________98.5
__2___0004_________キャラクタキペン２____東京___________92.0
__3___1085_________香り消しゴム__________東京___________96.3
__4___7563_________下敷き________________東京___________32.0
__5___4826_________シャープペン__________東京___________廃盤
__6___9325_________卓上ホルダー__________東京___________廃盤

テーブル[商品]に列[ポイント]を追加してみました。
ほぼ、ランキングシートそのものですよね。

列[ポイント]の書式=0.0;0.0;0.0;"廃盤"

ランキングクエリ: クエリ

[ランキング]_[商品コード]_[商品名]____________[生産工場]_[ポイント]
__________1___0003_________キャラクタキペン１__東京_______98.5
__________3___0004_________キャラクタキペン２__東京_______92.0
__________2___1085_________香り消しゴム________東京_______96.3
__________4___7563_________下敷き______________東京_______32.0
______________4826_________シャープペン________東京_______廃盤
______________9325_________卓上ホルダー________東京_______廃盤

冒頭の新しいテーブル[商品]をクエリで参照してみました。
列[ランキング]はクエリで生成しています。
仮に、[ランキング]が幾つかの条件でもっての[ポイント]の並び順だとすればこのように。

さて、今回のテストで＜ランキングシート＞は不要で[商品]！[ポイント]さえ更新できれば良い。
そういう可能性もあることが判ると思います。
先の回答にSQLのUPDATE文を更に１行追加するだけかも知れませんね。

先ずは、お知らせしておきます。

補足

ありがとうございます！

ひとまずエクセルから作ったテーブル
T_商品
にポイント列を増やしました。
クエリは[ランキング]の生成がわからず作成していません。。。
ランキングは同点の場合は同位にしたいです。

>仮に、[ランキング]が幾つかの条件でもっての[ポイント]の並び順だとすればこのように。

実はポイントの出し方が、3項目あるチェック条件（項目１．項目２．項目３．）の得点を元に算出されています。
実はその3項目も表示したいのですが、これを言うとややこしくなるかと思って出していませんでした。
ポイントの高い順にランキングをつけるのですが、同点の場合、同位になりますが、データの並び順においては、エクセルで言う並べ替えの優先順位が項目１．→２．→３．としたいと思います。
ランキング（順位）を自動的に与える方がいいのか、既にエクセル上で付与されている順位列を用いた方がいいのか、どうでしょうか？

回答No.3

noname#140971

As　キーワードが一部欠落していました！

Private Sub コマンド0_Click()
　　Dim strSQLDELETE As String
　　Dim strSQLINSERT As String

　　strSQLDELETE = "DELETE FROM 新商品枝番"
　　strSQLINSERT = "INSERT INTO 新商品枝番 " & _
　　　　　　　　　 "SELECT ID, " & _
　　　　　　　　　 "DBLookup('ID', '商品', '商品コード=''' & [商品コード] & '''') AS [商品_ID]," & _
　　　　　　　　　 "'' AS 枝番名," & _
　　　　　　　　　 "[カテゴリ１]," & _
　　　　　　　　　 "[カテゴリ２] FROM クエリ1"
　　CnnExecute (strSQLDELETE)
　　CnnExecute (strSQLINSERT)
End Sub

最善のやり方は、高度（？）なそれではなく自分と相性の良いそれです。
相性が悪いと思われたら止められたがいいです。

回答No.2

noname#140971

＜テーブル再作成要領＞

商品枝番: テーブル

[ID][商品_ID][枝番名][カテゴリ1][カテゴリ２]
__0________0_________________________________

このような空のテーブル[商品枝番](仮称)を用意します。

クエリ1: クエリ

[ID]_[商品コード]_[商品名______]_[枝番名_____________]_[生産工場]_[カテゴリ１]_[カテゴリ２]
___1__0003_________キャラクタ１___キャラクタ１_枝番01___東京_______玩具_________幼児用

こういうテーブルは既に出来ていますね。
で、これから一気にテーブル[商品枝番]を作成することも可能です。

新商品枝番: テーブル

[ID]_[商品_ID]_[枝番名]_[カテゴリ１]_[カテゴリ２]
__1_________1____________玩具_________幼児用
__2_________1____________文具_________筆記具
__3_________2____________玩具_________小学生用
__4_________2____________文具_________消耗品

これが、たった今、クエリ1から生成した[新商品枝番]です。
（[新商品枝番]というのはクエリが[商品枝番]を使用しているため！）
（実際は、質問者の環境でアレンジを）

これは、フォーム[商品枝番更新]を用意してコマンドボタンをクリックしただけです。

Private Sub コマンド0_Click()
　　Dim strSQLDELETE As String
　　Dim strSQLINSERT As String

　　strSQLDELETE = "DELETE FROM 新商品枝番"
　　strSQLINSERT = "INSERT INTO 新商品枝番 " & _
　　　　　　　　　 "SELECT ID, " _
　　　　　　　　　 "DBLookup('ID', '商品', '商品コード=''' & [商品コード] & '''') AS [商品_ID]," _
　　　　　　　　　 "''," & _
　　　　　　　　　 "[カテゴリ１]," &_
　　　　　　　　　 "[カテゴリ２] FROM クエリ1"
　　CnnExecute (strSQLDELETE)
　　CnnExecute (strSQLINSERT)
End Sub

DELETE文はみたまんまです。
INSERT INTO 文がチトややこしいです。
SELECT 文で取得したデータをインサートする命令です。
注意１、列名を ’As 列名’で指定する必要があります。（同じ列名同士は指定不要）
注意２、文字列内のシングルクォーテーションなどは２つ続けて書く必要があります。

さて、ここで、私は CnnExecute()、DBLookup() という自作関数を使っています。
こういうやり方ですと、苦も無く[商品枝番]は生成できます。

１、取りあえず既存のテーブルからフォーム[商品枝番更新]で[商品枝番]を更新。
２、いやいや、エクセルそのものを[商品枝番]と[クエリ]とに分割する。
３、で、Access では[商品枝番]を最初から使う。

で、１ならば、CnnExecute()、DBLookup()を補足します。

補足

ありがとうございます！
>１、取りあえず既存のテーブルからフォーム[商品枝番更新]で[商品枝番]を更新。
にチャレンジしてみたいと思います。

>商品枝番: テーブル
>[ID][商品_ID][枝番名][カテゴリ1][カテゴリ２]
>このような空のテーブル[商品枝番](仮称)を用意します。
用意しました！

>クエリ1: クエリ
>[ID]_[商品コード]_[商品名______]_[枝番名_____________]_[生産工場]_[カテゴリ１]_[カテゴリ２]
>___1__0003_________キャラクタ１___キャラクタ１_枝番01___東京_______玩具_________幼児用
>こういうテーブルは既に出来ていますね。
ここで、[枝番名_____________]がわかりません。
「空のテーブル[商品枝番](仮称)」の、「枝番名」フィールドということでしょうか？
と、仮定してT_商品とT_カテゴリとT_商品枝番から、上記項目でクエリを作成しました。
しかしデータシートビューで表示した際、レコードは表示されません。
私は正しく作業できているでしょうか？

※このご回答を頂く前にエクセルで枝番をつけたシートを作成し、新規アクセスへインポートしてテーブルとクエリを作成しました。
T_商品
ID　商品コード　商品名　　　　　　　生産工場
1　　0003　　　　キャラクターペン　　東京
2　　1085　　　　香り消しゴム　　　　東京

T_商品枝番
ID　商品ID　枝番　カテゴリ１　カテゴリ２
1　　1　　 1-1　 玩具　　　　対象小学生以下
2　　1　　 1-2　 文具　　　　筆記用具
3　　1　　 1-3　 文具　　　　消耗品
4　　2　　 2-1　 文具　　　　筆記用具
5　　2　　 2-2　 文具　　　　消耗品

Q_クエリ１
T_商品_ID/T_商品_商品コード/T_商品_商品名/T_商品枝番_枝番/T_商品_生産工場/T_商品枝番_カテゴリ１/T_商品枝番_カテゴリ２

元のアクセスがごちゃごちゃしちゃっているので新規にしました。
必要フォームとかはコピペすればいいかと思って。

高度な方を学びたい気持ちは山々です。
が、なんだか教える方が大変なんじゃないかと思えたり…
もし、このエクセルのデータを使った方が説明し易いようでしたら。
Husky2007さんのやり易い形でお願いします。

回答No.1

noname#140971

少し、確認したいことがあります。
テーブル名は、私流に変更しています。

商品: テーブル

[ID]_[商品コード]_[商品名_______]_[生産工場]
__1___0003_________キャラクタ１____東京
__2___0004_________キャラクタ２____東京

商品枝番: テーブル

[ID]_[商品_ID]_[枝番名______________]_[カテゴリ１]_[カテゴリ２]
__1_________1___キャラクタ１_枝番01____玩具_________幼児用
__2_________1___キャラクタ１_枝番02____文具_________筆記具
__3_________2___キャラクタ２_枝番01____玩具_________小学生用
__4_________2___キャラクタ２_枝番02____文具_________消耗品

まず、[カテゴリーシート]ですが、これはいわゆる＜商品枝番＞なのでは？
「販売管理」では、このように[商品マスター]と[商品枝番一覧]とを有するのは常識。
この場合、[商品]![ID]と[商品枝番]![商品_ID]とが連結するのが常道。
エクセルのシートも本来はそうあるべきかと・・・。

クエリ1: クエリ

[ID]_[商品コード]_[商品名______]_[枝番名_____________]_[生産工場]_[カテゴリ１]_[カテゴリ２]
___1__0003_________キャラクタ１___キャラクタ１_枝番01___東京_______玩具_________幼児用
___2__0003_________キャラクタ１___キャラクタ１_枝番02___東京_______文具_________筆記具
___3__0004_________キャラクタ２___キャラクタ２_枝番01___東京_______玩具_________小学生用
___4__0004_________キャラクタ２___キャラクタ２_枝番02___東京_______文具_________消耗品

さて、ここでテーブル[商品]と[商品枝番]を参照してみましょう。
[カテゴリーシート]は、正に、この形になっていますね。

私的には、[商品枝番]![枝番名]が必須だとは思いますが、それはさておいて・・・。
テーブルとクエリとの混同という不具合をどうしますか？

そもそも、データには３種類あります。

１、カテゴリー名等のセットアップデータ。
２、商品等のマスターデータ。
３、履歴、伝票等の記録データ。

この３タイプのデータをそのタイプ毎に保持し管理されたがいいです。
で、Access では上記のようにキー列を連結することでデータの重複記録を避けます。
それを[カテゴリーシート]のように参照するのはクエリ（SQL文）の仕事です。

ここの不味さを放置したまま進みますか？
※不具合は、まあ、考え方がややこしいに尽きます。
※不具合は、まあ、処理速度に影響がでるかも位でしょう。

＜お願い＞

テーブル[ランキング]も具体的なデータを。
逐一、このようにテーブルを作成してテストしていますので自分で考えるのはややこしいです。

補足

ありがとうございます!!
>ここの不味さを放置したまま進みますか？
いいえ、私が会社に入る前からあるデータだったので、手を付けるのは良くないかと思っていましたが、今回用に新たにしてみようと思います。
>テーブルとクエリとの混同という不具合をどうしますか？
これは枝番テーブルを作ることで解消しますか？

>そもそも、データには３種類あります。
今回は３の履歴・伝票は無いので2種にしてみます。
枝番シートを作成し、インポートして教えていただいた
商品: テーブル
商品枝番: テーブル
を2つ作成してみます。

>ここの不味さを放置したまま進みますか？
>※不具合は、まあ、考え方がややこしいに尽きます。
放置せずに進みたいと思います。
テーブルの作り直しをします。
基盤修正は他にありますでしょうか？

ランキングシートについて
順位　商品コード　商品名　 ポイント
1 　　 0003　 　キャラクターペン　 98.5
2　 　 1085　 　香り消しゴム　　 96.3
｜
3800　 7563　　 下敷き　　 　32.0
｜
－　　 4826　　 シャーペン芯LD3 廃盤
－　　 9325　　　卓上ホルダー　　　 廃盤

※廃盤商品はエクセル上順位が"－"となっています。
アクセスにインポートすると空欄になります。

>逐一、このようにテーブルを作成してテストしていますので自分で考えるのはややこしいです。
本当にありがとうございます。
とても心強く感じております。

よろしくお願いします!!