Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access初・中級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトルクエリでのグループ集計
記事No171727
投稿日: 2017/02/28(Tue) 13:48
投稿者KUKU
OS:WINDOWS7
Access Version:2000

下記のようなテーブルがあります。

出荷日 荷姿 出荷NO
2/28  AAA 1
2/28 AAA  2
2/28 BBB 3
2/28 CCC 4
2/28 AAA 5
2/28 BBB 6
2/28 BBB 7

とあった場合データとして抽出したいのは

出荷日 荷姿 出荷NO
2/28 AAA 1-2
2/28 BBB 3
2/28 CCC 4
2/28 AAA 5
2/28 BBB 6-7

上記のイメージです。こういった荷姿が同じなのに出荷NOが飛んでいるがために
うまく集計できない場合はどうすればよろしいでしょうか?

タイトルRe: クエリでのグループ集計
記事No171728
投稿日: 2017/03/01(Wed) 12:13
投稿者hatena
連続している値をグループとするということですね。

このように前後のレコードを比較する処理はクエリは苦手で、できないことはないですが複雑で重い処理になりがちです。

テーブルにグループ化フィールドを追加して、VBAでグループ情報を書き込む方法がおすすめです。
テーブルに出荷NO_B という数値型のフィールドを追加します。

下記のVBAを実行します。

Public Sub T出荷NOグループ更新()
    Dim rs As DAO.Recordset
    Dim pre荷姿 As String
    Dim GNO As Long

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 出荷テーブル ORDER BY 出荷NO;")

    Do Until rs.EOF
        If pre荷姿 <> rs!荷姿 Then
            pre荷姿 = rs!荷姿
            GNO = rs!出荷NO
        End If
        rs.Edit
        rs!出荷NO_B = GNO
        rs.Update
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

End Sub

テーブルに下記のように 出荷NO_B が更新されます。


出荷日	荷姿	出荷NO	出荷NO_B
2017/02/28	AAA	1	1
2017/02/28	AAA	2	1
2017/02/28	BBB	3	3
2017/02/28	CCC	4	4
2017/02/28	AAA	5	5
2017/02/28	BBB	6	6
2017/02/28	BBB	7	6

このテーブルから集計クエリを作成します。

SELECT 出荷日, 荷姿, 出荷NO_B, Max(出荷NO) AS 出荷NO_E
FROM 出荷テーブル
GROUP BY 出荷日, 荷姿, 出荷NO_B;

クエリの出力結果

出荷日	荷姿	出荷NO_B	出荷NO_E
2017/02/28	AAA	1	2
2017/02/28	BBB	3	3
2017/02/28	CCC	4	4
2017/02/28	AAA	5	5
2017/02/28	BBB	6	7

タイトルRe^2: クエリでのグループ集計
記事No171729
投稿日: 2017/03/01(Wed) 17:06
投稿者KUKU
hatena様
ご返信ありがとうございます。VBAは触ったことがなく下記の記述をどこに書けばよろしいですか?

下記のVBAを実行します。

Public Sub T出荷NOグループ更新()
Dim rs As DAO.Recordset
Dim pre荷姿 As String
Dim GNO As Long

Set rs = CurrentDb.OpenRecordset("SELECT * FROM 出荷テーブル ORDER BY 出荷NO;")

Do Until rs.EOF
If pre荷姿 <> rs!荷姿 Then
pre荷姿 = rs!荷姿
GNO = rs!出荷NO
End If
rs.Edit
rs!出荷NO_B = GNO
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub




> 連続している値をグループとするということですね。
>
> このように前後のレコードを比較する処理はクエリは苦手で、できないことはないですが複雑で重い処理になりがちです。
>
> テーブルにグループ化フィールドを追加して、VBAでグループ情報を書き込む方法がおすすめです。
> テーブルに出荷NO_B という数値型のフィールドを追加します。
>
> 下記のVBAを実行します。
>
> Public Sub T出荷NOグループ更新()
> Dim rs As DAO.Recordset
> Dim pre荷姿 As String
> Dim GNO As Long
>
> Set rs = CurrentDb.OpenRecordset("SELECT * FROM 出荷テーブル ORDER BY 出荷NO;")
>
> Do Until rs.EOF
> If pre荷姿 <> rs!荷姿 Then
> pre荷姿 = rs!荷姿
> GNO = rs!出荷NO
> End If
> rs.Edit
> rs!出荷NO_B = GNO
> rs.Update
> rs.MoveNext
> Loop
>
> rs.Close
> Set rs = Nothing
>
> End Sub
>
> テーブルに下記のように 出荷NO_B が更新されます。
>
>
> 出荷日 荷姿 出荷NO 出荷NO_B
> 2017/02/28 AAA 1 1
> 2017/02/28 AAA 2 1
> 2017/02/28 BBB 3 3
> 2017/02/28 CCC 4 4
> 2017/02/28 AAA 5 5
> 2017/02/28 BBB 6 6
> 2017/02/28 BBB 7 6
>
> このテーブルから集計クエリを作成します。
>
> SELECT 出荷日, 荷姿, 出荷NO_B, Max(出荷NO) AS 出荷NO_E
> FROM 出荷テーブル
> GROUP BY 出荷日, 荷姿, 出荷NO_B;
>
> クエリの出力結果
>
> 出荷日 荷姿 出荷NO_B 出荷NO_E
> 2017/02/28 AAA 1 2
> 2017/02/28 BBB 3 3
> 2017/02/28 CCC 4 4
> 2017/02/28 AAA 5 5
> 2017/02/28 BBB 6 7

タイトルRe^3: クエリでのグループ集計
記事No171731
投稿日: 2017/03/01(Wed) 23:27
投稿者hatena
> hatena様
> ご返信ありがとうございます。VBAは触ったことがなく下記の記述をどこに書けばよろしいですか?

標準モジュールに記述してください。

実行するのは、テーブルデータを更新したり、追加したりした後がいいでしょう。

例えば、フォームで入力するとして、フォームの更新後処理で呼び出せばいいでしょう。


Private Sub Form_AfterUpdate()

    Call T出荷NOグループ更新()

End Sub

タイトルRe^4: クエリでのグループ集計
記事No171736
投稿日: 2017/03/03(Fri) 16:48
投稿者KUKU
hatena様
お世話になります。

rs As DAO.Recordset←ここの部分でコンパイルエラーとなりましたがどこか
おかしいでしょうか?

ご教授頂いた通りテーブルも数値型にしております。

Public Sub T出荷NOグループ更新()

Dim rs As DAO.Recordset
Dim pre荷姿CD As String
Dim GNO As Long

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TBL_出荷指示 ORDER BY 始ケースNO;")

Do Until rs.EOF
If pre荷姿CD <> rs!荷姿CD Then
pre荷姿CD = rs!荷姿CD
GNO = rs!始ケースNO
End If
rs.Edit
rs!出荷NO_B = GNO
rs.UPDATE
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

*フィールド名は一部変更しております。




> > hatena様
> > ご返信ありがとうございます。VBAは触ったことがなく下記の記述をどこに書けばよろしいですか?
>
> 標準モジュールに記述してください。
>
> 実行するのは、テーブルデータを更新したり、追加したりした後がいいでしょう。
>
> 例えば、フォームで入力するとして、フォームの更新後処理で呼び出せばいいでしょう。
>
>
> Private Sub Form_AfterUpdate()
>
> Call T出荷NOグループ更新()
>
> End Sub

タイトルRe^5: クエリでのグループ集計
記事No171737
投稿日: 2017/03/03(Fri) 20:46
投稿者hatena
> rs As DAO.Recordset←ここの部分でコンパイルエラーとなりましたがどこか
> おかしいでしょうか?

下記を参考に、
参照設定の
Microsoft DAO 3.6 Object Library
にチェックをいれてください。
(3.6の部分はバージョンによって違う場合があります。)

参照設定について:AccessTips032
http://www.accessclub.jp/actips/tips_32.htm

タイトルRe: クエリでのグループ集計
記事No171730
投稿日: 2017/03/01(Wed) 21:02
投稿者mayu
こんばんは。

> こういった荷姿が同じなのに出荷NOが飛んでいるがために
> うまく集計できない場合はどうすればよろしいでしょうか?

クエリで表現する場合は、旅人算メソッドを用います。

考え方としては
あらかじめ 出荷日・荷姿 ごとにグループ連番を振り
その後に

 ・ 出荷日
 ・ 荷姿
 ・ { 出荷No }の数から { グループ連番 }の数 を引いた値

の3つでグループ化します。

■ SQL
----------------------------------------------------------------------

SELECT 出荷日
     , 荷姿
     , IIf( Count(1) = 1
          , CStr( Min( q.出荷No ) )
          , CStr( Min( q.出荷No ) ) & '-' & CStr( Max( q.出荷No ) )
       ) As 出荷No
FROM
(
    SELECT t1.出荷日
         , t1.荷姿
         , t1.出荷No
         , Count(1) As gnum 
    FROM テーブル名 t1
       , テーブル名 t2
    WHERE t1.出荷日 = t2.出荷日
      AND t1.荷姿   = t2.荷姿
      AND t1.出荷No >= t2.出荷No
    GROUP BY t1.出荷日
           , t1.荷姿
           , t1.出荷No
) q
GROUP BY 出荷日
       , 荷姿
       , q.出荷No - q.gnum
ORDER BY 出荷日
       , Min( q.出荷No ) ;

----------------------------------------------------------------------

※ テーブル名は 実際のものに書き換えるのをお忘れなく。

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る