アクセスでデータを集計するには

質問

例として、下記のデータテーブルを受け取りました。
テーブル1
ID　名前　項目　値
01　ＡＡ　住所　○○
01　ＡＡ　電話　○×
01　ＡＡ　月給　○△円(テキスト)
01　ＡＡ　手当　○□円(テキスト)
02　ＢＢ　住所　□□
02　ＢＢ　電話　□×
02　ＢＢ　月給　□△円(テキスト)
02　ＢＢ　手当　□○円(テキスト)
03　ＣＣ　住所　×☆
03　ＣＣ　電話　×○
03　ＣＣ　月給　×△円(テキスト)
03　ＣＣ　手当　×□円(テキスト)
　以下続く

上記表を、下記表
テーブル2
ID　名前　住所　電話　月給　　　　手当
01　ＡＡ　○○　○×　○△(数値)　○□(数値)
02　ＢＢ　□□　□×　□△(数値)　□○(数値)
03　ＣＣ　×☆　×○　×△(数値)　×□(数値)
　以下続く
のように編集された表を新たに作成したいのですが、どのようにしたらよろしいのでしょうか(質問1)。
　また、テーブル2から、月給+手当が□△以上の人のIDと名前を抽出できるようにするには、どのような操作が必要なのでしょうか(質問2)。
　データはテーブル1の形式で定期的に送られて来た上、データは全てテキスト形式故、テーブル2の形式に再編集したテーブルがあった方がいいと思いました。
　アクセスは今まで使ったことがありません。
　恐らくご回答の解説が長くなってしまうでしょうから、作り方の概要と流れだけでも構いません。もちろん詳細にご回答賜れば誠に幸いです。
　以上、よろしくご回答のほど、お願い申し上げます。

回答No.2

Euglena · ベストアンサー

厳密にいうと正確ではないのですが、
ID、名前、項目で重複したレコードがないことを前提とすると、
ACCESSのクロス集計クエリーを使用すればできると思います。

・クロス集計クエリーで、
　　ID、名前を行見出し
　　項目を列見出し
　　値を先頭の値
　で定義

このままだと月給と手当がテキストのままなので、
テーブル作成クエリーを新たに作成して数値に変換。

テーブル作成クエリーを実行させれば目的のテーブルができると思います。

データ抽出は、条件文に
「月給＋手当が***円より大きい」
と記述してやればできるはずです。

SELECT ID,名前 FROM 作成テーブル WHERE 月給+手当 > ***

お礼

すぐご報告できず、申し訳ありません。
　おかげさまで、ご教示下さいました方法でできました。
　11/15にアクセスの関数辞典を購入し、やり方を理解しました。手当名とテキストの数値の間に空白文字があることを利用し、選択クエリーのデザインビューフィールドに、
Val(Replace(Replace(Mid([○手当],InStr([○手当]," ")+1,Len([○手当])),",",""),"%",""))
としたら、「,」が取れた数値だけ抽出され、計算可能になりました。
　そして、やっと11/16に目的の抽出一覧表を作成することができました。
　ここまでの段階までお導き下さり、誠にありがとうございました。

補足

ご回答下さりありがとうございます。
　ご回答のとおり、クロス集計クエリーを新規に構成し、ご指示どおりの定義を行い、質問のテーブル2に相当するクロス集計クエリーを作成することができました。
　ご指摘のとおり、値がテキストのままですので、テーブルを作成するためのクエリーを、どうやって作ったらよいのかわからぬまま、とりあえず選択クエリーで構成しようと作りましたが、どのようにしたらテキストを数値に変換できるのかわかりません。
　例えば、値の覧に、「資格　28000円」、「時間外　30027円」と入ってしまっている場合、どのように数値だけを抽出したらよろしいのでしょうか。

回答No.6

Euglena

忘れてました。

モジュールの作成画面で「ツール」→「参照設定」を選び、
「Microsoft DAO *** Object Library」
という項目にチェックを入れてください。
そうしないと、
dim DB as Database
の行でエラーが出ます。

お礼

私の疑問点に一つ一つご丁寧にご教示下さり、誠にありがとうございました。
　今から思うと、別の質問として項目を立てた方がよかったと、申し訳なく思います。
　今後、私にとってのアクセスの先生として、心にとどめておきたく思います。
　ありがとうございました。

回答No.5

Euglena

とりあえずこんな感じでしょうか・・

Public Function 集計()

Dim DB As Database
Dim TBL1 As DAO.Recordset
Dim TBL2 As DAO.Recordset
Dim 月給 As Long
Dim 手当 As Long
Dim 住所 As String
Dim 電話 As String

'データベース指定
Set DB = CurrentDb

'元テーブルオープン
Set TBL1 = DB.OpenRecordset("テーブル1", DB_OPEN_SNAPSHOT)

'レコードがあったら処理開始
If TBL1.RecordCount <> 0 Then
　　'1番目のレコードを取得
　　TBL1.MoveFirst

　　'読み取るレコードがなくなるまで回す
　　Do Until TBL1.EOF

　　　　'書き込むテーブルに同一IDのデータがあるか？
　　　　SQL = "select * from テーブル1集計 where ID = '" & TBL1![ID] & "'"
　　　　Set TBL2 = DB.OpenRecordset(SQL, DB_OPEN_DYNASET)
　　　　If TBL2.RecordCount <> 0 Then
　　　　　　'レコードがあったので更新処理

　　　　　　'初期値として登録されているデータをセット
　　　　　　住所 = TBL2![住所]
　　　　　　電話 = TBL2![電話]
　　　　　　月給 = TBL2![月給]
　　　　　　手当 = TBL2![手当]

　　　　　　If TBL1![項目] = "住所" Then
　　　　　　　　'項目が「住所」ならば住所のデータを書き換え
　　　　　　　　住所 = TBL1![値]
　　　　　　ElseIf TBL1![項目] = "電話" Then
　　　　　　　　'項目が「電話」ならば電話のデータを書き換え
　　　　　　　　電話 = TBL1![値]
　　　　　　ElseIf TBL1![項目] = "月給" Then
　　　　　　　　'項目が「月給」ならば月給のデータを書き換え
　　　　　　　　月給 = 数値抽出(TBL1![値])
　　　　　　ElseIf TBL1![項目] = "手当" Then
　　　　　　　　'項目が「手当」ならば手当のデータを書き換え
　　　　　　　　手当 = 数値抽出(TBL1![値])
　　　　　　End If

　　　　　　'レコード更新
　　　　　　TBL2.Edit
　　　　　　TBL2![住所] = 住所
　　　　　　TBL2![電話] = 電話
　　　　　　TBL2![月給] = 月給
　　　　　　TBL2![手当] = 手当
　　　　　　TBL2.Update
　　　　Else
　　　　　　'レコードがないので追加処理

　　　　　　'初期値セット
　　　　　　住所 = ""
　　　　　　電話 = ""
　　　　　　月給 = 0
　　　　　　手当 = 0

　　　　　　If TBL1![項目] = "住所" Then
　　　　　　　　住所 = TBL1![値]
　　　　　　ElseIf TBL1![項目] = "電話" Then
　　　　　　　　電話 = TBL1![値]
　　　　　　ElseIf TBL1![項目] = "月給" Then
　　　　　　　　月給 = 数値抽出(TBL1![値])
　　　　　　ElseIf TBL1![項目] = "手当" Then
　　　　　　　　手当 = 数値抽出(TBL1![値])
　　　　　　End If

　　　　　　'レコード追加
　　　　　　TBL2.AddNew
　　　　　　TBL2![ID] = TBL1![ID]
　　　　　　TBL2![名前] = TBL1![名前]
　　　　　　TBL2![住所] = 住所
　　　　　　TBL2![電話] = 電話
　　　　　　TBL2![月給] = 月給
　　　　　　TBL2![手当] = 手当
　　　　　　TBL2.Update
　　　　End If
　　　　TBL2.Close

　　　　'次のレコードを取得
　　　　TBL1.MoveNext
　　Loop
End If
TBL1.Close
DB.Close

End Function

Public Function 数値抽出(対象文字列 As String) As Long

'「×× 99999円」というパターンオンリーで・・
Dim 文字列長 As Long
Dim i As Long
Dim 検査文字 As String
Dim 抽出文字列 As String

抽出文字列 = ""
文字列長 = Len(対象文字列)
For i = 1 To 文字列長
　　検査文字 = Mid(対象文字列, i, 1)
　　If Val(検査文字) > 0 Then
　　　　抽出文字列 = 抽出文字列 + 検査文字
　　End If
Next

数値抽出 = Val(抽出文字列)

End Function

お礼

上記プログラムを組むと、後で更新時に楽になり、大変よいかとは思いますが、エクセルであれば理解しやすかったのですが、アクセスでは、私のレベルではまだ難しいようです。
　今後、勉強して、よりスマートなものを作成できるよう、これから頑張りたいと思います。
　ありがとうございました。

回答No.4

Euglena

あ～、すみません。
先頭に「資格」などの文字が入っているのですね。
そうするとVal()は使えませんね・・
モジュールで関数を作らないといけないですかねぇ。

話はそれますけど、
「資格」とか「残業」って、「手当」の項目になると思いますけど、
提示されている表ですと、どちらかしか入らない気がしますが・・

補足

ご懇切なご回答誠にありがとうございます。
>話はそれますけど
　例として出した表は、あくまでも説明のために簡便に表したまでで、実は1つのIDに対してもっと多数の項目があります。手当の項目についても、各種手当があり、それらに対応する項目名は各IDごとに全て覧が設定され、それぞれの行に入っています。手当受取対象者でなくとも値には「-円」と入力されています。ごく一部の項目で、値の覧に各種手当名と金額が併記されているのです。
　val()で変換できそうな項目は多数あります。
　先程の捕捉を記入後確認したら、本回答の他、モジュール関数の定義方法等、極めて詳細かつご懇切なご回答を賜り、深く感謝いたします。今日はもうできないので、後日検討させていただきます。

回答No.3

Euglena

数値への変換は、

Val([※※※].[月給]) AS 月給
（※※※はクロス集計クエリー名）

と、Val関数を使用すればいいと思います。
Val関数は先頭から変換を行っていって、
変換できない文字が出てきたら終了しますので、
後ろに「円」がついていても大丈夫でしょう。

お礼

こちらの疑問に早速ご回答下さりありがとうございました。No.2礼に上げた式で計算可能な数値を取り出すことができました。式を入力する場所もわかりました。
　本当にありがとうございました。

補足

早速のご回答ありがとうございます。
　とりあえず作成したクロス集計クエリーの選択クエリーの抽出条件の覧に、上記変換式を入力(そのままでは式の型が一致しないと言われたので、Val関数内の.は,に変えました)してみましたが、式の最後のカッコの位置で、「指定した式に含まれる関数で、引数の数が一致しません」とまたまた怒られてしまいました。
　Val関数のヘルプを探してみましたが、見つけられませんでした。とても悲しいです。
　たびたび大変申し訳ございませんが、Val関数の書式をもう一度ご解説願いたいのと、式をどこに入力すればいいのか、ご教示下さいますでしょうか。

回答No.1

souta_n

ちょっと項目を減らして解説します。

ID 名前　項目　値
01　山田　住所　大阪
01　山田　電話　06
01　田中　住所　京都
01　田中　電話　075
03　加藤　住所　神戸
03　加藤　電話　078

というテーブルを元テーブルを（T_元）とします。
コンバート先のテーブルを次のようにフィールドを設けて作成します。これを（T_コンバート）とします。

ID　名前　住所　電話　

１．下記の様な標準モジュールを１つ作成し実行します。
これはDAOという機能を使っていますが、Access97は既定で使用できますが、Access2000の場合は、ツールメニューの参照設定でMicrosoftDAO*.*ObjectLibraryを参照させておく必要があります。Access2002はそのまま出来たと思いますが、ちょっと記憶が定かじゃないです。

Sub Conv()
Dim DB As Database
Dim RS1 As Recordset
Dim RS2 As Recordset

Set DB = CurrentDb
Set RS1 = DB.OpenRecordset("T_元", dbOpenTable)
Set RS2 = DB.OpenRecordset("T_コンバート", dbOpenTable)

Do Until RS1.EOF
RS2.AddNew
RS2!ID = RS1!ID
RS2!名前 = RS1!名前
If RS1!項目 = "住所" Then
RS2!住所 = RS1!値
ElseIf RS1!項目 = "電話" Then
RS2!電話 = RS1!値
End If
RS2.Update
RS1.MoveNext
Loop
RS1.Close
RS2.Close
DB.Close
End Sub

２．実行後のT_コンバートには下記の様なデータが入ります。
ID　名前　住所　電話　
01　山田　大阪
01　山田　　　　06
02　田中　京都
02　田中　　　　075
03　加藤　神戸
03　加藤　　　　078

３．あとはクエリーなどを使って集計してみたらと思います。

お礼

ご教示下さいましたとおり組むと、後で更新時にも楽になり、大変よいかとは思いますが、エクセルであれば理解しやすかったのですが、アクセスでは、私のレベルではまだ難しいようです。
　ご回答を下さいましたときは、アクセスのデータベース構築の流れさえもわかっておらず、おかげさまで勉強するとっかかりをつかむことができました。
　また、あれやこれやで目的の集計をすることができるようになりました。
　今後、勉強して、上記のようなものを作成できるよう、これから頑張りたいと思います。
　ありがとうございました。

補足

早速のご回答ありがとうございます。
　コンバート先のテーブルを作成し、モジュールも訳がわからないながらご説明下さったように(テーブル名を当方の様式に替えて）作成しました。実行をかけてみましたが、上から2行目のDim DB As Databaseの行で止まってしまい、コンパイルエラー　ユーザ定義型は定義されていませんと怒られてしまいました。
　ヘルプを見ても、訳がわかりません。
　アクセスはver.2002です。
　どのように直していったらいいのか、ご指導下さいますと幸いです。