Accessで一覧表から抽出のような事出来ますか？

質問

いつも、お世話になります。

エクセルなどにある、A地点からY地点まで斜めに地点が配置された区間一覧早見表で
" C地点からL地点が交わるところがその区間の合計距離です "　のような事を
Accessで、データ形式のフォームで、" 2地点を入力すると自動的に区間距離
を計算してくる "　のような事はできるとは思うのですが、どのように作れば
よいか全く検討がつきません。検索してみますと、検索テーブルなどの言葉があり
ましたが、イメージがつかめません。
エクセルではMACH関数との事ですが、AccessではMach関数は必要ないとの記述も
ありましたが、理解できないまだ、初心者から少し出たようなスキルです。

お教えいただければありがたいと思います。
何卒、よろしくお願いいたします。

回答No.5

hatena1989 · ベストアンサー

> そうです。各地点はひとつのルート上にあります。

> テーブルをいくつか作成して対応したいと考えています。

ということは複数のルートがあり、各ルート上に複数の地点がある。
ルート上の各地点間の距離を参照して表示したいということですね。

そういうことならテーブル設計から変更しましょう。
いままでの回答はすべてリセットしてください。

下記の２つのテーブルを作成します。

テーブル名　ルートマスター

フィールド名　データ型
----------------------------　
ルートNo　数値型　主キー
ルート名　テキスト型

テーブル名　地点間距離

フィールド名　データ型
----------------------------　
ルートNo　数値型　主キー
地点No　数値型　主キー
地名　テキスト型
距離　数値型

主キーが２つありますが、これは複数フィールド主キーというもので、設定法は「Access 複数フィールド主キー」等をキーワードにWEB検索してみてください。

ルートマスターのデータを下記のように入力します。

ルートNo　ルート名
------------------
1　あ
2　い
3　う

地点間距離のデータを下記のように入力します。

ルートNo　地点No　地名　距離
----------------------------
1＿＿＿＿＿1＿＿＿A＿＿＿0
1＿＿＿＿＿2＿＿＿B＿＿＿3
1＿＿＿＿＿3＿＿＿C＿＿＿5
1＿＿＿＿＿4＿＿＿D＿＿＿8
1＿＿＿＿＿5＿＿＿E＿＿＿11
2＿＿＿＿＿1＿＿＿F＿＿＿0
2＿＿＿＿＿2＿＿＿G＿＿＿4
2＿＿＿＿＿3＿＿＿H＿＿＿7
2＿＿＿＿＿4＿＿＿I＿＿＿9

ルートNo1の「あ」ルートの起点はA、終点はEとして、距離には、Aからの距離を入力します。

フォームを作成します。「レコードソース」プロパティは空欄にしておきます。
コンボボックスを3つ配置して下記のように設定します。

名前　cbxルート
コントロールソース　　空欄のまま
値集合ソース　ルートマスター
連結列　1
列数　2
列幅　0cm

列数 2、列幅 0cm とすることでリストには地名が表示されるが、
実際に格納される値は １列目の ルートNo となります。
このへんは理解が難しいですが、列幅を、1cm;3cm と設定してみてどう表示されるか確認して、
次に、0cm;3cm と設定してみてどのように表示が変化したか確認されるといいでしょう。

名前　cbx起点
コントロールソース　　空欄のまま
値集合ソース　SELECT 地点No, 地名 FROM 地点間距離 WHERE ルートNo=[cbxルート];
連結列　1
列数　2
列幅　0cm

名前　cbx終点
コントロールソース　　空欄のまま
値集合ソース　SELECT 地点No, 地名 FROM 地点間距離 WHERE ルートNo=[cbxルート];
連結列　1
列数　2
列幅　0cm

コンボボックス「cbxルート」の更新後処理のドロップダウンリストからイベントプロシージャを選択して、右端のビルドボタンを押して、表示されるVBAウィンドウで下記のように記述します。

Private Sub cbxルート_AfterUpdate()
　　Me.cbx起点 = Null
　　Me.cbx起点.Requery
　　Me.cbx終点 = Null
　　Me.cbx終点.Requery
End Sub

これでcbxルートを選択すると、cbx起点、cbx終点　のリストにはそのルートの地名が表示されるようになります。

テキストボックスを配置して下記のように設定します。

名前　txt距離
コントロールソース
=Abs(DLookup("距離","地点間距離","ルートNo=" & Nz([cbxルート],0) & " AND 地点No=" & Nz([cbx終点],0))-DLookup("距離","地点間距離","ルートNo=" & Nz([cbxルート],0) & " AND 地点No=" & Nz([cbx起点],0)))

これでコンボボックスでルート、起点、終点 を選択するとtxt距離に２点間の距離が表示されます。

かなり複雑な設定になりますが、ひとつずつ確実にこの説明通りに作成していけばできるはずです。
もし、行き詰まったらどこが分からないのか質問してください。

お礼

hatena1989様

ご回答、本当にありがとうございます。やってみたいと思います。

よろしくお願い申し上げます。

回答No.4

hatena1989

なんかお互いにうまく伝わってませんね。

まず、テーブル「区間距離」に下記のフィールドを作成しましたか。

ID　オートナンバー型　主キー
起点　テキスト型　インデックス　はい(重複なし)
A　数値型
B　数値型
C　数値型
D　数値型
E　数値型
F　数値型
G　数値型
H　数値型

この「起点」フィールドに起点の地名を入力します。
「A」フィールドにはAを終点とする距離を入力します。
「B」フィールドにはBを終点とする距離を入力します。

ID＿起点＿A＿B＿C・・・←フィールド
----------------------
1＿A＿＿＿0＿5＿8

上記の場合は、起点がAで終点がBの距離は5、起点がAで終点がCの距離は8という意味になります。
つまり、A, B, C ・・・フィールドが終点を意味します。

同様に次の行（レコード）はIDを2、起点をBとして、終点をフィールド名とする場合の距離を入力していきます。

テーブルの入力が終われば、
次は空白のフォームを作成してテキストボックスを３つ配置します。それぞれ名前を、

txt起点、txt終点、txt距離　とします。

txt距離のコントロールソースに下記の式を設定します。

=DLookup(Nz([txt終点],"A"),"区間距離","起点='" & [txt起点] & "'")

DLookup関数は Excelでいう VLookup関数のAccess版と理解してください。

これで、txt起点に起点の地名、txt終点に終点の地名を入力すると、txt距離に、
テーブルから該当の距離を検索してきて表示します。

以上の説明で理解出来ましたでしょうか。

それとも私が想定していることと、southbreezeさんの希望していることが異なっているのでしょうか。
なんかそんな気もしてきました。

例えば、各地点はひとつのルート上にあって、各地点間の距離が下記のようだとして、

A→2→B→3→C→1→D

B→D間＝3+1＝4

というようなことなのでしょうか。

お礼

hatena1989様

度々、申し訳ありません。

実は、あれから色々試してみているうちに、終点と言うフィールドは不要なことが解かりました。

それから、A数値型．．と言うのも地名で良いことが解かりました。

[起点]と言うtxtbox、[終点]と言うtxtbox, [距離]と言うtxtboxをお教えいただいたように配置して、[起点]に地名、[終点]に地名を入力すると、[距離]にテーブルから距離が表示されるようになりました。

本当にありがとうございました。

しかし、コンボboxでは「出来ない」のです。コンボboxでは、地名の一覧が出て来るのでとても検索がしやすいと思います。

お教えいただいたように、起点のコンボboxから地名を選択して、次に終点のコンボboxから地名を選択しても、距離のboxには何も出てこない状態になっています。

コンボboxを作成する時、「テーブルからフィールドを選択する画面」が出て来るのですが、(Access2003を使っています) [起点]のboxも[終点]のboxも"起点"というフィールドを選びました。どこが違っているか、お教えいただけたらと思います。

別の質問ですが、この検索フォームは、複数のテーブルは参照できないのでしょうか？もし、出来なければ、テーブル毎に検索フォームを作ることで対応できると思います。

>例えば、各地点はひとつのルート上にあって、各地点間の距離が下記のようだとして、
>A→2→B→3→C→1→D
>B→D間＝3+1＝4
>というようなことなのでしょうか。

そうです。各地点はひとつのルート上にあります。しかし、今の時点では、テーブルに入力した距離の数値を反映してくれれば良いかな．．．と、それしか思い浮かばないので、テーブルをいくつか作成して対応したいと考えています。

何卒、よろしくお願い申し上げます。

補足

hatena様
すみません、深夜にお手数をお掛けいたします。

＞まず、テーブル「区間距離」に下記のフィールドを作成しましたか。

はい、作成しました。

>この「起点」フィールドに起点の地名を入力します。

縦列に地名が入っています。

>「A」フィールドにはAを終点とする距離を入力します。
>「B」フィールドにはBを終点とする距離を入力します

そのようになっています。

＞同様に次の行（レコード）はIDを2、起点をBとして、終点をフィールド名とする場合の距離を入力していきます

hatena様が添付していただいているような図になっています。

が、ここで気がつきました。テーブルをデザインビューで作る時、A,B,Cを地名にしていました。今、直しました。

そして、フォームに戻って、起点の欄に起点の地名を入力すると、距離の欄に「0」と表示されるのですが、終点の欄に地名を入力すると、距離の欄が「エラー」になります。

回答No.3

hatena1989

> 横１行と縦１列以外の空のフィールドを計算して埋め宅思って、データビューのフォームを作成しました。どうにか計算してそれを再度テーブルとして保存する事が出来レバと思っています。

前回の回答は各地点間の距離にとくに規則性がないという前提での回答です。
計算で求めることができるのなら、前回の回答とはまったく異なったものになります。

> 横B=[A地点]-2　としますと、斜め「0」の並びから上の部分が「-(マイナス)」となってしまいます。下の部分は目的の計算の通りになっています。
>
> それで、横B=[A地点]-2　だけど、 0を下回ったら　2の倍数を足す．．．のような計算式をコントロールにいれたいと思うのですが、その式の書き方が解かりません。

この文章からは具体的にどのような計算規則なのかちょっと理解できません。

例を上げて具体的な計算式を提示してもらえますか。

補足

hatena様

すみません、ちょっとまぎらわしい事を書きました。

テーブルには手入力にて、距離の入力が終りました。

「フォーム」の手順のところまで来ましたが、[終点]は何処にあるんでしょうか?
あるいは、何処に入れたら良いでしょうか？

＞下図のように起点と終点の交差点に距離を入力します。

の起点は表で見えますが、「終点」が見えないものですから、すみません。

回答No.2

hatena1989

下のようなテーブルを作成します。

テーブル名　区間距離
フィールド
ID　オートナンバー型　主キー
起点　テキスト型　インデックス　はい(重複なし)
A　数値型
B　数値型
C　数値型
D　数値型
E　数値型
F　数値型
G　数値型
H　数値型

下図のように起点と終点の交差点に距離を入力します。

フォームを作成して、コンボボックスを2つ配置して下記のように設定します。

名前　txt起点
コントロールソース　空白

名前　txt終点
コントロールソース　空白

名前　txt距離
コントロールソース　=DLookup(Nz([txt終点],"A"),"区間距離","起点='" & [txt起点] & "'")

これで、txt起点とtxt終点に地点名を入力するとtxt距離に2点間の距離が表示されます。

お礼

hatena様
ご覧頂き、本当にありがとうございます。早速試してみたいと思います。その結果は再度UPさせて頂きますので、何卒よろしくお願い申し上げます。

補足

hatena様
ご教示のようにテーブルを作成しました。フィールド数が少し多くなりまして(最初は少なめにしたら良かったですが)、そうすると、距離をそれぞれのフィールドに入力するのが結構手間になっています。

今の時点では横に[地点名A],B,C,D,E,F...と縦の起点に[地点名A],B,C.D.E.F...を入れてそれぞれの距離を横一行と縦1列に数字(距離数)を入力しました。

横1行と縦1列以外の空のフィールドを計算して埋めたく思って、データビューのフォームを作成しました。どうにか計算してそれを再度テーブルとして保存する事が出来ればと思っています。

そこで、添付してくださった表で例えれば、

横B=[A地点]-2　としますと、斜め「0」の並びから上の部分が「-(マイナス)」となってしまいます。下の部分は目的の計算の通りになっています。

それで、横B=[A地点]-2　だけど、 0を下回ったら　2の倍数を足す．．．のような計算式をコントロールにいれたいと思うのですが、その式の書き方が解かりません。
又は、それ以外に良い方法があればお教えくださいませんか？

また、テーブルが複数になると思いますが、その場合、検索フォームから複数のテーブルを参照することが出来るようになりますか?

お忙しいところ、申し訳ございませんがよろしくお願い申し上げます。

回答No.1

superdbtool

ExcelでSQLでテーブルを検索する方法があります。
書かれている内容だけで詳しく説明でいないですが、
エクセルで直接SQLを使ってデータを抽出する方法をご参考ください

お礼

superdbtool様

ご回答ありがとうございます。書面などのほとんどの事務処理をAccessに集約していて、Excellはあまり利用していません。
でも、お教えいただいた方法はとても解りやすいです。MSのOfficeもかなり最近のものですネ？私が利用しているものは2003ですから、お教えいただいたような機能がついていないと思います。
せっかくご回答くださったのに、このようなご返事しか出来なくて申し訳なくおもいます。ありがとうございます。