Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2016)
縦横追加のつづき(再)
投稿日時: 18/12/04 19:22:33
投稿者: sim2018

お世話になります。
縦横追加の投稿の3回目の質問です。
 
前回、前々回のほぼ思いどおりの作成ができました。ほんとうにいろいろとありがとうございました。
縦のデータを横のデータに追加してレポートに表示するところまで出来たのですが、後でカナ昇順で番号
をグループ毎に連番で振り(クエリで)なおして、その後、縦→横に追加してます。
 
 
ほとんどのレポートでは昇順で並び替え出来たのですが、一部だけ途中(33番から)が先に来て最初(1番)
が次頁になります。
そこで、
Sub sample()
    Const HORIZON_LIMIT As Long = 32
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim x As Long
    Dim iRow As Long
    Dim iCol As Long
    Dim itemCount As Long
    Dim strSQL As String
    Dim v As Variant
    Dim items As Variant
     
    If (DCount("*", "TEST", "番号 Is Not Null") = 0) Then Exit Sub
     
    Set db = CurrentDb
    db.Execute "DELETE FROM TESTWK ;"
     
    strSQL = "SELECT 番号, Count(1) " _
           & "FROM TEST " _
           & "WHERE 番号 Is Not Null " _
           & "GROUP BY 番号 " _
           & "ORDER BY 番号 ;"
     
    Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
    rs1.MoveLast
    rs1.MoveFirst
    v = rs1.GetRows(rs1.RecordCount)
    rs1.Close
     
    items = Array("氏名", "カナ", "参加", "日付")
    itemCount = UBound(items) + 1
     
     strSQL = "SELECT 番号, " & Join(items, ",") & " " _
            & "FROM TEST " _
            & "WHERE 番号 Is Not Null " _
            & "ORDER BY 番号, " & items(0) & " ;"
     
    Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set rs2 = db.OpenRecordset("TESTWK", dbOpenTable)
     
    For i = 0 To UBound(v, 2)
        iRow = -Int(-v(1, i) / HORIZON_LIMIT)
         
        For j = 1 To iRow
            iCol = HORIZON_LIMIT - 1
            If (j = iRow) Then
                iCol = (v(1, i) - 1) Mod HORIZON_LIMIT
            End If
             
            rs2.AddNew
            rs2.Fields(0).Value = v(0, i)
            For k = 0 To iCol
                For x = 0 To itemCount - 1
                    rs2.Fields(1 + (k * itemCount) + x).Value _
                        = rs1.Fields(1 + x).Value
                Next x
                rs1.MoveNext
            Next k
            rs2.Update
        Next j
    Next i
     
    rs1.Close
    rs2.Close
    db.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub
の構文に縦→横追加の時に、番号またはカナで並び替えを記述できないでしょうか?
 
また、2ページ、3ページとページを振るにはどうしたらいいでしょうか?
いろいろとお世話になりますが、宜しくお願い致します。

回答
投稿日時: 18/12/05 12:36:53
投稿者: mayu.

引用:

縦→横追加の時に、番号またはカナで並び替えを記述できないでしょうか?

ソースコード中では、番号で並び替えしてますので
何のことかわかりませんでしたが、もしかして縦のデータは
https://www.moug.net/faq/viewtopic.php?t=77716
の構造になっているということでしょうか。
関連スレッドがある場合はその URL も掲載するようにしましょう。
 
引用:

また、2ページ、3ページとページを振るにはどうしたらいいでしょうか?

横のテーブルにページ番号を格納するフィールド( 数値型 )を作成し、
そこに値を入力したいということでしょうか。
 
テーブル: TEST

 班 名前 カナ 参加 日付 番号
-----------------------------------
 1  あ  ア  〇  11/16  1
 1  い  イ  〇  11/17  2
 1  う  ウ  〇  11/18  3
 2  え  エ  〇  11/01  1
 2  お  オ  〇  11/11  2
 3  か  カ  〇  12/02  1
 3  き  キ  〇  12/03  2
 4  く  ク  〇  11/30  1

テーブル: TESTWK

 班 ページ番号 名前1 カナ1 参加1 日付1 名前2 カナ2 参加2 日付2 ・・・ 日付32
-------------------------------------------------------------------------------------

だとして
 
Sub sample()
    Const HORIZON_LIMIT As Long = 32
    Dim db        As DAO.Database
    Dim rs1       As DAO.Recordset
    Dim rs2       As DAO.Recordset
    Dim i         As Long
    Dim j         As Long
    Dim k         As Long
    Dim x         As Long
    Dim iRow      As Long
    Dim iCol      As Long
    Dim itemCount As Long
    Dim strSQL    As String
    Dim v         As Variant
    Dim items     As Variant
    
    If (DCount("*", "TEST", "班 Is Not Null") = 0) Then Exit Sub
    
    Set db = CurrentDb
    db.Execute "DELETE FROM TESTWK ;"
    
    strSQL = "SELECT 班, Count(1) " _
           & "FROM TEST " _
           & "WHERE 班 Is Not Null " _
           & "GROUP BY 班 " _
           & "ORDER BY 班 ;"
    
    Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
    rs1.MoveLast
    rs1.MoveFirst
    v = rs1.GetRows(rs1.RecordCount)
    rs1.Close
    
    items = Array("名前", "カナ", "参加", "日付")
    itemCount = UBound(items) + 1
    
     strSQL = "SELECT 班, " & Join(items, ",") & " " _
            & "FROM TEST " _
            & "WHERE 班 Is Not Null " _
            & "ORDER BY 班, " & items(1) & " ;"
    
    Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set rs2 = db.OpenRecordset("TESTWK", dbOpenTable)
    
    For i = 0 To UBound(v, 2)
        iRow = -Int(-v(1, i) / HORIZON_LIMIT)
        
        For j = 1 To iRow
            iCol = HORIZON_LIMIT - 1
            If (j = iRow) Then
                iCol = (v(1, i) - 1) Mod HORIZON_LIMIT
            End If
            
            rs2.AddNew
            rs2.Fields(0).Value = v(0, i)
            rs2.Fields(1).Value = j
            For k = 0 To iCol
                For x = 0 To itemCount - 1
                    rs2.Fields(2 + (k * itemCount) + x).Value _
                        = rs1.Fields(1 + x).Value
                Next x
                rs1.MoveNext
            Next k
            rs2.Update
        Next j
    Next i
    
    rs1.Close
    rs2.Close
    db.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub

投稿日時: 18/12/05 18:59:50
投稿者: sim2018

mayuさん早速の回答ありがとうございます。
また、返事がおそくなりすみません。
 

引用:
ソースコード中では、番号で並び替えしてますので
何のことかわかりませんでしたが、もしかして縦のデータは
https://www.moug.net/faq/viewtopic.php?t=77716
の構造になっているということでしょうか。
関連スレッドがある場合はその URL も掲載するようにしましょう。

 
おっしゃる通りクエリにて番号フィールドに班ごとのグループで並び替えを行う更新クエリを作成して
それを先ほどの縦横追加の構文に組み込んで実行しています。
 
 
引用:
横のテーブルにページ番号を格納するフィールド( 数値型 )を作成し、
そこに値を入力したいということでしょうか。
 

 
これもおっしゃる通りです。
下にmayuさんが構文を記述してくださっていますが、最初とどこがかわったのでしょうか?
わかりにくい質問になってしまいすみません。また、わかりにくいところがあれば指摘してください。
よろしくお願い致します。[/quote]

回答
投稿日時: 18/12/05 22:16:21
投稿者: mayu.

引用:

最初とどこがかわったのでしょうか?

& "ORDER BY 班, " & items(1) & " ;"
 
名前 → カナ での並び替えに変更
 
rs2.Fields(1).Value = j
 
この行を追加
 
rs2.Fields(2 + (k * itemCount) + x).Value
 
1列目は「 班 」、2列目は「 ページ番号 」を記入するため、
繰り返し項目の入力位置を3列目から始まるように列インデックスの数値を変更
 
以上の3ヶ所になります。

投稿日時: 18/12/06 09:27:25
投稿者: sim2018

お世話になります。
 
mayuさん回答ありがとうございます。
 
この構文なら番号を並び替えるクエリを使わなくても並び替えができました。
 
また、ページ番号も追加されました。ありがとうございます。
 
ただ、TESTWKをレポートに代入?したときに、全部のページではないのですか以下のような
 
並び替えになります。
   1班                           2ページ          
-----------------------------------------------------------------------------
番号 名前33 カナ33 参加33 日付33 名前34 カナ34 参加34 日付34  ・・・64
・   ・   ・   ・   ・   ・   ・   ・   ・
・   ・   ・   ・   ・   ・   ・   ・   ・
・   ・   ・   ・   ・   ・   ・   ・   ・
   1班                           1ページ          
-----------------------------------------------------------------------------
番号 名前1  カナ1 参加1  日付1 名前2 カナ2  参加2 日付2  ・・・32
・   ・   ・   ・   ・   ・   ・   ・   ・
・   ・   ・   ・   ・   ・   ・   ・   ・
・   ・   ・   ・   ・   ・   ・   ・   ・
-----------------------------------------------------------------------------
 
このようにレポートにすると一部のページが1ページと2ページが逆に表示されてしまいます。
 
どこかで昇順の制御はできるのでしょうか?
 
たびたび申し訳ありません。よろしくお願い致します。

回答
投稿日時: 18/12/06 09:52:36
投稿者: mayu.

引用:

どこかで昇順の制御はできるのでしょうか?

並び替えを指定したクエリがレポートのレコードソースであっても
レコードソースでの並び順がレポートには反映されませんので
レポートでの並び替えは、レポートのプロパティで個別に設定します。
 
https://hamachan.info/win10/access/report_sort.html
あたりを参考にして
[班]、[ページ番号] の順で並び替えを指定するとよろしいでしょう。

投稿日時: 18/12/06 10:35:30
投稿者: sim2018

お世話になります。
mayuさん早速の回答ありがとうございました。
すべて思い通りレポート表示までできました。
本当に最後までお付き合いいただきありがとうございました。感謝々