Microsoft Access 掲示板

もし、やりたいことが

指定したjanに該当するデータの新しい順にならべて2番目のデータを取得して、それをエクセルシートに貼り付けたい。

ということなら、下記のコードでいいでしょう。

Public Sub Sample()
    Dim daoCn As dao.Database 'Databaseオブジェクトを扱う変数（DB）を宣言
    Dim daoRs As dao.Recordset 'Recordsetオブジェクトを扱う変数（RS）を宣言
    Dim strSQL As String
    Dim i2 As Long

    strFileName = "出勤 - コピー.accdb"
    Set daoCn = DBEngine.Workspaces(0).OpenDatabase("C:\Users\tasuk\Desktop\VBA\経理\出勤\" & strFileName)

    jan = "1000000000016"
    i2 = 2

    Cd = "個人データ.氏名,出勤データ.月日,出勤データ.番号"
    Tn = "個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan"
    Sc = " 出勤データ.jan = '" & jan & "'" '検索条件の確定

    strSQL = "SELECT TOP " & i2 & Cd & " FROM " & Tn & " WHERE " & Sc & " ORDER BY 出勤データ.月日 DESC" '降順でi2番目までのデータを取得
    strSQL = "SELECT TOP 1 * FROM (" & strSQL & ") ORDER BY 出勤データ.月日" '上記のデータから昇順で1番目のデータを取得(結局降順でi2番目のデータ)
    Set daoRs = daoCn.OpenRecordset(strSQL, dbOpenDynaset) 'DAOレコードセットオブジェクトを作成
    
    Worksheets(4).Range("A1").CopyFromRecordset daoRs
    daoRs.Close

End Sub

hatena様

ご連絡ありがとうございます。

ページ数のリセットのやり方で、出来ました。

無理だと思っていたことが実現できてよかったです。

hiroton様もありがとうございました。

回答の前に

前の質問のやりとりでも感じたのですが、質問のキャッチボールがうまくできていない印象です。
質問内容があいまいなので、こちらが不足部分を推測しないと回答できないことが多いです。また、推測が入ると実際とは相違があり的確に回答にならない場合があります(つまり無駄骨になる確率が高い)
相手に正確に状況、ご希望のことが正確に伝わるようになるべく詳細に説明するように心がけてください。
回答者が質問する場合、回答に必要な情報ですので、それにも正確かつ詳細に回答してください。

回答の前の確認

1
最初に、番号フィールドの全レコードを削除、もしくはNullに変更したいのですが、方法が分かりません

レコードの削除と、フィールドの更新はまったく別の処理です。
やりたいことは、レコードの削除ですか。
(レコードを削除すると、他のフィールドのデータも失われますが大丈夫ですか)
それともフィールドの更新ですか。

レコードの削除なら、どのようなレコードを削除するのか、
フィールドの更新なら、どのフィールドを何に変換するのか、
詳細に説明してください。

あと、ADOにこだわっているようですが、ADOでもDAOでも基本的にできることに大差はありません。

2
1度番号を付けてから、再度janと番号で検索して、対象データを取得しているのですが、
該当する番号がヒットした時に、対象データだけを取得してLoopを抜けて、終了する方法は無いのでしょうか？

これもやりたいことを詳細に正確に説明してください。

コードをざっとみて、推測した感じでは、やろうとしていることは下記のようなことだ思いましたが、あってますか。

指定したjanに該当するデータの新しい順にならべて2番目のデータを取得して、それをエクセルシートに貼り付けたい。

UPDATE Aテーブル, Bテーブル
SET Aテーブル.[1フィールド] = [2フィールド]
WHERE Aテーブル.[1フィールド] Like "*" & [3フィールド] & "*";

でどうだろう。

下記の簡単なサンプルで確認した限りではうまく変換されました。

Aテーブル

Bテーブル

　
3フィールドが複数該当したら、後の方で上書きされるようです。

りんごさんの指摘のうえに、さらに、
Aテーブル、Bテーブルのサンプルデータを提示してもらえると、助かります。

hirotonさんから既に指摘がありますが、サブレポートではページヘッダー、フッターは表示されないです。
メインレポートの方のページヘッダー/フッターにページ情報を表示するように修正する必要があります。

ページ数表示は、サマリー、明細データで連続せずに、それぞれでのページ数にしたいということなら、明細データの先頭レコードでページ数をリセットすればいいでしょう。

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
    If Me.CurrentRecord = 1 Then Me.Page = 1
End Sub

ひょっとすると、一括処理は無理なのでしょうか？
accessのSQLビューを見ても、役に立たなかったです。
となると、Loopで１件づつということになりますが。

ADOのstrSQL = "DELETE FROM 出勤データ where 番号>0"にしても、
結局は１件づつ処理してるわけですし。

１件づつ処理するしかないのであれば、コードは長くなるけどDAOの方が有利になります。

テーブルすべてのデータは一括で削除できるので、
フィールドを指定すれば済む話のような気がして
モヤモヤが治まりません。

日付を降順でソートした順番です。

hiroton様

たびたびありがとうございます。

ページ表記の処理がされないということで理解いたしました。

popplerで模索してみます。

書き換えるルールを説明して下さい。3フィールドが複数該当したらどうなるの？

回答の前に、番号について説明をしてほしい。Aさんの8/23の番号1、2、3みたいになるとして、違いは何？

あいまいな値で結合はできないので、交差結合から、フィールドをLike比較して更新するレコードのキーと変更後の値のデータを生成し、その後、レコードの更新の値としてDlookup関数で変換語句を拾い出す更新クエリを作成するとかすれば行けると思います

前提の話入れるのすっかり忘れてました
1ページに収まる、または、改ページの制御がないならそのレポートをサブレポートとして埋め込めばいいです

ただ、サブレポートはコントロールなので、その内部ではページの概念がないようです。（情報検索してたらhatenaさんが回答している案件がありました）
ページ表記も同様、ページ表記のための処理はされないのでしょう

ページの概念がないので、ページヘッダーやページフッターをページごとに出力するということもできないと思われます

サマリーの方のページ数が表示されないですね。
データにもよりますが、2～3ページです。

サマリーデータのページ数が表示じされないということでしょうか。
サマリーデータは何ページぐらいになりますか。

hatena様

お世話になります。

ちょっとやってみたのですが、ページフッターでページ数が表示されないのですが、そういうものなのでしょうか？

明細データのレポートヘッダーにサマリーデータをサブレポートしてと埋め込めばどうですか。

私はよくこの方法を使います。

hiroton様

ご返答ありがとうございます。

やはり普通にはできなさそうですね。

popplerを試してみます。

標準機能ではできなさそうなので、PDF出力後にファイル操作するしかないんじゃないですかね

いろいろ方法はあるようですが、popplerは簡単そうでした

読ませていただいて、早速役に立ちました。
SELECT以下の部分に、出勤データ.番号が必要だったのですね。
EXCELL VBAを使ってSQLを動かす情報は、極端に少なくなるので、本当に助かりました。
引き続き、ヒットしたデータを取得してLOOPを抜けるコードを追加してみます。

ありがとう!!!!!
実は、とりあえず連番を振ろうと思い、コードをひとつ書いてみたのですが、

Sub dao()

    Dim DB As dao.Database 'Databaseオブジェクトを扱う変数（DB）を宣言
    Dim rst As dao.Recordset 'Recordsetオブジェクトを扱う変数（RS）を宣言
    Dim strSQL As String
    Dim i As Long

    strFileName = "出勤 - コピー.accdb"
    Set daoCn = DBEngine.Workspaces(0).OpenDatabase("C:\Users\tasuk\Desktop\VBA\経理\出勤\" & strFileName)

    jan = "1000000000016"

    strSQL = "SELECT 個人データ.氏名,出勤データ.月日 FROM 個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan WHERE  出勤データ.jan = '1000000000016' ORDER BY 出勤データ.月日 DESC" '古い順はASC

    Set daoRs = daoCn.OpenRecordset(strSQL, dbOpenDynaset) 'ADOレコードセットオブジェクトを作成

    If daoRs.RecordCount <> 0 Then
        i = 1
         Do
          daoRs.Edit
          daoRs!番号 = i
          daoRs.Update
          i = i + 1
          daoRs.MoveNext
         Loop Until daoRs.EOF = Rtrue
    End If
     daoRs.Close
End Sub

daoRs!番号のところで、このコレクションには項目がありませんのメッセージが、
出勤 - コピー.accdbには、番号のフィールドを追加しておいたのに
ということで、困っていたのです。

さっそく読ませていただきます。

なので、DAOで日付を降順で連番をふりつつ、
ターゲットになる順位がでてきたら、LOOPを抜けるようにした方が効率的なのかと思ってコードを書き始めたのですが、
DAOは、省エネにつながるのでしょうか？

段違いに高速化されるはずです。

自動でDAOを使用して連番をふる汎用関数を下記で紹介してますので、ご参考に。

グループ毎連番を自動入力する関数

クエリで連番を表示する場合、DCount関数やサブクエリを利用する方法はあちこちで紹介されています。 しかし、この方法は自分より前のレコード件数をカウントするというロジックなのでレコード件数が多くなると幾何級数的に重くなります。また、グループ毎に連番を振るという仕様になると、条件式も複雑になってきます。 クエリは使わずにテーブルに連番フィールドを持たせて、そこに VBA で連番を書きこむようにすると高速...

fc2

ありがとうございます。
TOP 5 をDESCで取得して、TOP 1はASCという感じですね。
さっそく、試してみますが、ひとつ気になる事があります。
Dcountなどで、クエリでカウントしようとすると、データによってはドッコイショという感じになるのです。
サブクエリを使っても同じ感じです。

なので、DAOで日付を降順で連番をふりつつ、
ターゲットになる順位がでてきたら、LOOPを抜けるようにした方が効率的なのかと思ってコードを書き始めたのですが、
DAOは、省エネにつながるのでしょうか？

ACCESSはよく分からないので、EXCELLにしがみついている状態なのですが、
最近EXCELLから離れろという幻聴が聞こえる回数が増えてきて、
尻込みしております。

回答ありがとうございます！
移行期間として更新の必要がある住所録データは今までのExcelファイルを使いたかったので、この方法が一番いいです。
作成段階で絶対につまづくと思うので、その時にはまたよろしくお願いします。

現在、〇番目のデータを取得する方法に取り組んでいます。

TOP 1 で1番目のレコードを取得する方法は既に回答してますので、それの応用で、
例えば５番目のレコードを取得するなら、TOP 5 で上位５番目までのレコードを取得するSQLを記述して、
それをサブクエリにして Top 1 で１番目を取得するようにすればいいでしょう。

おかげさまで、解決いたしました。
現在、〇番目のデータを取得する方法に取り組んでいます。
一度、EXCELに書き出してしまえば造作もないことなのですが、それは避けたいと思っております。
解決できなかったら、またお願い致します。

本題は解決しているようですが回答すると、
Group By句は集計の範囲を設定します。対応するHaving句もその範囲内で処理を行います
'Max(出勤データ.月日)'をSELECT句に含めてみてください。各グループ（出力レコード）ごとに、'Max(出勤データ.月日)'フィールドの値が変わるはずです。そのほかのグループ化の設定によっては'Max(出勤データ.月日)'フィールドが一致するレコードも出力されるかもしれません

いずれにせよ、月日でグループ化したデータで月日の最大値をとっても、それは常に同じ値になり、出勤データ.月日 = Max(出勤データ.月日)は月日がNULLでない限り常にTrueになります

あと、最初の質問

1000000000016で登録してある最新の出勤データを取得しようとしたところ、

については解決したということでよろしいのでしょうか。

最初の質問、クエリで下記をしたい

1000000000016で登録してある最新の出勤データを取得しようとしたところ、

途中から、VBAの話になって、

Max関数が、VBAのコード内では使用できないことは分かりました。

今度は、集計についての質問。

今、思いついたのですが、個人データに在職のフィールドを用意しておけば、
GROUP BYで在職をグループ化して、一括でデータを集計する事が可能になるのでしょうか？

話がどんどんとんでいって、こちらから見たらなんのことか、という状態です。

別質問なら、新規投稿で内容を詳細に説明して質問してください。

セルのデータをテーブルとしてリンクすることができます。
このリンクテーブルをもとにフォームやレポートを作ればいいでしょう。
リンクテーブルだと更新できないので まずは 閲覧(フォーム)と印刷(レポート)部分から作成するといいでしょう。
その間はエクセル側でデータ更新します。
ある程度できたらリンクテーブルは削除して、エクセルデータをテーブルとしてインポートします。
それ以後はアクセス側でデータ更新します。

ざつとこんな感じで移行することになります。

ただエクセルデータがデータベースとして扱える形になっていることが前提です。

追加の質問で申し訳ないです。
今、思いついたのですが、個人データに在職のフィールドを用意しておけば、
GROUP BYで在職をグループ化して、一括でデータを集計する事が可能になるのでしょうか？

おさわがせしました。
タイプミスでした。
hatenaさんはじめ、有難うございました。

Excelワークシート関数かもしれないので、WorksheetFunction.を試してみましたが、
型が一致しませんになりました。
月日は、ACCESSに日付/時刻型で登録してあります。

Max関数が、VBAのコード内では使用できないことは分かりました。
ところが、Dmaxでもmaxでも、
Sub または Function が定義されていません。
と表示されて、先に進みません。

単純に
Dim myMax As Variant

     myMax = DMax("月日", "出勤データ", "jan='1000000000016'")
      MsgBox myMax
としても、同じエラーが出ます。

EXCEL2016で、Ofiice 16.0 objectと、Active X　6.1はには、チェックが入っている状態です。

何故なのでしょうか？

hirotonさんから回答がある通り、集計関数(Max)を使う場合は、すべてのフィールドに対して、集計の指定をするか、グループ化する必要があります。それがしてないのでエラーになりますね。

1000000000016で登録してある最新の出勤データを取得しようとしたところ、

この要件なら、サブクエリかDMax関数で月日の最大を取得してそれを抽出条件にするか、月日で降順に並べ替えて先頭レコード(TOP 1)を取得することになります。

DMax関数を使う場合のコード例

SELECT TOP 1 個人データ.氏名,個人データ.住所,出勤データ.時間, 出勤データ.月日 AS 月日
FROM 個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan
WHERE
 個人データ.jan = '1000000000016'
 AND 出勤データ.月日 = DMax("月日","出勤データ","jan='1000000000016'")

TOP 1 を使う場合のコード例

SELECT TOP 1 個人データ.氏名,個人データ.住所,出勤データ.時間, 出勤データ.月日 AS 月日
FROM 個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan
WHERE  個人データ.jan = '1000000000016'
ORDER BY 出勤データ.月日 DESC;

jan = '1000000000016' and 出勤データ.月日 = Max(出勤データ.月日)は、１件しかデータが無いはずだと思うのですが

SELECT 個人データ.氏名,個人データ.住所,出勤データ.時間, Max(出勤データ.月日) FROM 個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan GROUP BY 出勤データ.jan,個人データ.氏名,個人データ.住所,出勤データ.時間 HAVING 出勤データ.jan = '1000000000016'

とするとエラーは出ませんが、すべてのデータを取得してしまうので、意味がありません。

SELECT 個人データ.氏名,個人データ.住所,出勤データ.時間, 出勤データ.月日 FROM 個人データ INNER JOIN 出勤データ ON 個人データ.jan=出勤データ.jan GROUP BY 出勤データ.jan,個人データ.氏名,個人データ.住所,出勤データ.時間, 出勤データ.月日 HAVING 出勤データ.jan = '1000000000016' and 出勤データ.月日 = Max(出勤データ.月日)

としてみても、エラーは出ませんが全データを取得してしまいます。

どうしたら、よいのでしょうか？

クエリのデザインビューで目的のクエリを作成して、SQLビューに切り替えて確認してみるといいですよ

エラーメッセージが謎すぎますよねぇ
元（英語）のエラーメッセージはタブン「You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.」で、Google翻訳かけてさらに意訳すると、「集計関数に<name>が存在しないため、クエリを実行できません」といったところでしょう

クエリで集計を行う場合、出力する全てのフィールドで何らかの集計の指定、またはグループ化の指定が必要になります
問題のクエリはグループ化の指定がないため、「氏名」を何らかの集計方法だとして処理しようとして「『氏名』という集計方法はないですよ」とエラーを出しているわけです

これです、これです。
どうもありがとうございました。

BuildCriteriaメソッドのことでしょうか。

Application.BuildCriteria メソッド (Access) | Microsoft Learn