クエリの重複する出力結果を一行にまとめたい。

よろしくお願いいたします。

タイトルの通り、クエリの重複する結果を一行にまとめる方法を知りたく伺います。
可能な処理なのかわかりませんが、お知恵を拝借させていただきますと幸いです。

◎作成しているDB概要:
ある組織の業務内容を管理するDBです。
業務内容テーブル、作業者マスタの二つのテーブルで構成されています。
業務内容テーブルには、「業務名」と、「主担当」「副担当」フィールドがあります。
業務内容テーブルは主担当、副担当を作業者マスタから参照(リレーション)します。
また、副担当フィールドはAccessの機能で「複数値フィールド」を用いて、
データの入力を行っています。

◎テーブル定義:
【業務内容テーブル】
[フィールド名] [データ型]
業務内容ID オートナンバー型※主キー
業務内容名 テキスト型
主担当 数値型※リレーション有,作業者マスタ[作業者ID]と多対一
副担当 数値型※リレーション有,作業者マスタ[作業者ID]と多対一,複数値フィールド


【作業者マスタ】
[フィールド名] [データ型]
作業者ID オートナンバー型※主キー,リレーション有,主担当、副担当と一対多
作業者名 テキスト型

◎テーブルデータ
【業務内容テーブル】
業務内容ID 業務内容名 主担当 副担当
1 業務A 1 2;3
2 業務B 2 1;3
3 業務C 3 1;2
5 業務D 3 1




【作業者マスタ】
作業者ID  作業者名
1  佐藤
2  田中
3  鈴木

◎クエリの構成(SQL):
SELECT 作業者マスタ.作業者名 AS 作業者名, 業務内容テーブル.業務内容名 AS 主担当, 業務内容テーブル_1.業務内容名 AS 副担当
FROM (作業者マスタ LEFT JOIN 業務内容テーブル ON 作業者マスタ.作業者ID = 業務内容テーブル.主担当) LEFT JOIN 業務内容テーブル AS 業務内容テーブル_1 ON 作業者マスタ.作業者ID = 業務内容テーブル_1.副担当.Value;


◎現状のクエリ(SQL)の出力結果:
作業者名|主担当|副担当
佐藤|業務A|業務B
佐藤|業務A|業務C
佐藤|業務A|業務D
田中|業務B|業務A
田中|業務B|業務C
鈴木|業務C|業務A
鈴木|業務C|業務B
鈴木|業務D|業務A
鈴木|業務D|業務B


◎本来出したかった出力結果:
作業者名|主担当|副担当
佐藤|業務A|業務B,業務C,業務D
田中|業務B|業務A,業務C
鈴木|業務C,業務D|業務A,業務B


・上記の「現状のクエリ(SQL)の出力結果」を、「本来出したかった出力結果」のようにするにはどうすればよろしかったでしょうか(SQL、その他Accessの標準機能で
対応可能でしょうか)?
SQL文の他、その場合のクエリの設定方法、またはそのほかのご提案等ございましたらご教示いただけますと幸いです。


◎目的概要(参考):
「業務内容にひもづく主担当者、副担当者」以外に、その逆(?)の「担当者各位にひもづく主担当、副担当の業務内容」の情報を取得し、
台帳を作成したいと考えています。
その際、一つのレコードで、一目で「どれが主担当で、どれが副担当の業務なのか」がわかるように表現したいのですが、上記の「現状のクエリ(SQL)の結果」のように
現状は業務内容が重複して表示されてしまっています。



以上につきまして、何卒よろしくお願い申し上げます。

現状のクエリからDJoin関数で副担当を結合した結果を出力する。

DJoin 関数 - リスト文字列を取得する方法 | YU-TANG's MS-Access Discovery 

結果をレポートに出力するのでよければ、下記の方法で。

グループ内のデータを横連結する - hatena chips


この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

早々に誠にありがとうございます。

早速DJoin2関数を以下のようにして試してみましたが、エラーが出力されてしまいました。

もしよろしければ不足点を指摘いただけますと幸いです。

=============================================

・Moduleにコードを挿入

・クエリを集計クエリにする

・フィールドに以下を挿入する

主担当: DJoin2("業務内容名","業務内容テーブル","作業者名='" & [作業者名] & "'")

副担当: DJoin2("業務内容名","業務内容テーブル_1","作業者名='" & [作業者名] & "'")

以下のエラーが出力される

-2147217904:パラメーターが少なすぎます。1 を指定してください。

-2147217865:入力テーブルまたはクエリ '業務内容テーブル_1' が見つかりませんでした。そのテーブルやクエリが存在していること、または名前が正しいことを確認してください。

※http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12127599902

こちらも参考にさせていただきました。

=============================================

実際の設定画面は以下の通りです。

以上、なにとぞよろしくお願い申し上げます。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

前回の投稿で「現状のクエリから」と回答しましたが、必要なかったですね。

DJoin関数をリンク先からコピーしておいて、下記のクエリで希望の結果になります。

SELECT

 作業者名,

 DJoin("業務内容名","業務内容テーブル","主担当=" & [作業者ID]) AS 主担当,

 DJoin("業務内容名","業務内容テーブル","副担当.Value=" & [作業者ID]) AS 副担当
FROM 作業者マスタ;

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

希望の結果が出力できました。大変助かりました、誠にありがとうございます!

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

hatena19様

申しわけありません、もう一点、追加でお教えいただければと存じます。

◎【主担当、副担当いずれも担当していない担当者】がいた場合のエラー回避方法

【主担当、副担当いずれも担当していない担当者】がいた場合、以下のようなエラーが出力されてしまいます。

"3021:BOF と EOF のいずれかが True になっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。"

※以下出力の例です。作業者佐藤は副担当を一つも担当しておらず、また作業者田中は主担当を一つも担当していないというようにデータを作成した場合、以下のようなエラーが出力されいます。

作業者名 主担当 副担当
佐藤 業務A,業務B 3021:BOF と EOF のいずれかが True になっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。
田中 3021:BOF と EOF のいずれかが True になっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。 業務A,業務C,業務D
鈴木 業務C,業務D 業務A,業務B

DJOINの関数をIIFで空白としたりISEERORでくくってみたりエラー文を文字列で判定したり等して回避しようとしましたが、うまくいきませんでした。

上記のようなケースの場合の回避方法(もしよろしければ先にお教えいただいたSQLに修正するような形で)を別途お教えいただけますと幸甚です。

※回避方法としては、"エラーになった場合空白を入力する"、"主担当(副担当)なしと入力する"などで十分です(それが難しいのかもしれませんが・・・)。


以上何卒よろしくお願いいたします。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

DJoin関数はリンク先のものそのままコピーして使用してますか。

クエリのSQLは前回の回答のままですか。

当方でのサンプルでは特にエラーにならずに空白表示になりますが。

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

申しわけございません、高速化版(DJoin2関数)を使用しておりました。
(出力結果が最初同じだったので、せっかくならと高速化版を選んでしまっていました。。。)
DJoin関数で試したところご指摘の通りの表示となりました。
DJoinを使用してみたいと思います。お手数をおかけしましたm_ _;m

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

申しわけございません、高速化版(DJoin2関数)を使用しておりました。
(出力結果が最初同じだったので、せっかくならと高速化版を選んでしまっていました。。。)

そちらを使用していたのですね。

コードを見てみたら、対象レコードがない場合(レコード件数が0のとき)の分岐がしていないのが原因ですね。

下記のように2行追加するだけで、今回のエラー表示は回避できます。

Function DJoin2( _
         fieldname As String _
         , tablename As String _
           , Optional wherecondition As String _
             , Optional maxlength As Integer = 255 _
               , Optional delimiter As String = "," _
                 , Optional isdistinct As Boolean = False _
                   , Optional orderby As String _
                     ) As String
    On Error GoTo ErrorHandler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strData As String
    Dim strSql As String
    Dim strResult As String

    If maxlength <= 0 Then Exit Function

    strSql = "SELECT " & IIf(isdistinct, "DISTINCT ", "") & fieldname & " " _
             & "FROM " & tablename & " " _
             & IIf(Len(wherecondition) > 0, "WHERE " & wherecondition, "") & " " _
             & IIf(Len(orderby) > 0, "ORDER BY " & orderby, "")

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    With rst
        .Open strSql, cnn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
        If Not .EOF Then '対象レコードがある場合のみ(2015/12/01 hatena追加)
            strResult = .GetString(adClipString, , , delimiter)
            strResult = Left(strResult, Len(strResult) - 1)
        End If
        .Close
    End With

    If Len(strResult) > maxlength Then
        strResult = Left(strResult _
                         , InStrRev(strResult, delimiter, maxlength - 2, vbBinaryCompare) - 1) _
                         & "..."
    End If

ExitProcedure:
    On Error Resume Next
    Set rst = Nothing
    cnn.Close: Set rst = Nothing
    DJoin2 = strResult    ' 戻り値をセットします。
    Exit Function

ErrorHandler:
    ' クエリー内での使用を考慮して、エラーのダイアログ表示は
    ' 行なわず、エラーをそのまま戻り値に入れます。
    strResult = Err & ":" & Err.Description
    Resume ExitProcedure

End Function

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

ソースまで改良してくださり誠にありがとうございます!

こちらを使用してみようと思います、多謝です!

この回答で問題は解決しましたか?

役に立ちませんでした。

回答としてマークしていただきありがとうございます。

この返信の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。今後のサイト改善に役立てて参ります。

この回答の満足度をお教えください。

フィードバックをお送りいただきありがとうございます。

 
 

質問情報


最終更新日 2020年9月12日 表示 6,894 適用先: