売上データに会社毎に請求番号を振る(access

質問

顧客の売上データ（テーブル）があるのですが、
同じ顧客名で、複数のレコードがあります。

売上を１ヶ月毎に、顧客毎に利用分をまとめて請求書を
作成しています。
下記のように、１０月分には、すでに請求書番号（連番）が
入っています。
11月分の売上データの空欄の請求番号のところに、
顧客毎に、自動的に連番（2523～）で請求番号を振る方法がありましたら、
教えてください。どうぞよろしくお願いいたします。

（売上テーブル）
売上日　　　顧客名　金額　　請求書番号
12/10/08 Ｃ社　　\2000　　2511
12/10/09　　Ｃ社　　\5000　　2511
12/10/10　　Ｃ社　　\3000　　2511
12/10/01　　Ｄ社　　\1000　　2522
12/11/01　　Ａ社　　\2000
12/11/06　　Ａ社　　\1000
12/11/01　　Ｂ社　　\1000
12/11/08　　Ｃ社　　\2000

回答No.5

piroin654

＞実は、１ヶ月毎の請求から、四半期毎（３ヶ月毎）の請求に
＞変更になりました。
＞いまのままですと、３ヶ月分の売上データに請求番号をつけると、
＞当然のことながら、同一会社の請求書が３通できてしまいます。
＞１通になるようなよい方法を教えていただければ幸いです。

上記については回答を手直しすればできるとは思いますが、問題は
下記の、

＞さらに、できれば教えていただきたいのですが、たとえば、
＞４～６月分の請求書を発行する時に、早期発行希望の会社には、
＞６月中に請求書を発行し、その他は、７月に入ってから
＞請求書を発行している関係から、会社情報というテーブルの
＞早期発行希望にyesチェックの入っている会社の売上データ
＞だけに請求番号を振る機能もつけられたら、助かります。

以下のところで、

＞４～６月分の請求書を発行する時に、早期発行希望の会社には、
＞６月中に請求書を発行し、

この場合、６月中に請求書を発行した場合、発行後に６月中
に売上が発生することがないのか、もしそのような場合が
起こりえるならば、どのような対応をするのかを明確に
しておかないと当然ながら請求漏れが起こります。
たとえば、毎月の請求の場合、25日締めで、以降同じ月の
月末までの売上は翌月請求にするということはありますが。

このあたりはどのようになっていますか。
条件が複雑になると同じような手法でできるかどうかは
わかりませんが。

回答No.4

piroin654

少し手順を省略できることに気が付かなかったので
No2を手直しします。

No2において、
(1)はそのままです。
(2)は必要ありません。
(3)を一部手直しします。

Sub test()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim lnMax As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("売上テーブル", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Q請求月")
lnMax = DMax("請求書番号", "売上テーブル")

rs2.MoveFirst
Do Until rs2.EOF
　　lnMax = lnMax + 1
　　rs1.MoveFirst
　　Do Until rs1.EOF
　　If IsNull(rs2!請求書番号) Then
　　　　If Format(rs1!売上日, "yyyy/mm") = rs2!売上月 And rs1!顧客名 = rs2!顧客名 Then
　　　　　　rs1.Edit
　　　　　　　　rs1!請求書番号 = lnMax
　　　　　　rs1.Update
　　　　End If
　　Else
　　　　lnMax = lnMax - 1
　　　　Exit Do
　　End If
　　rs1.MoveNext
　　Loop
rs2.MoveNext
Loop
rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

クエリで最大値を求めてそれを参照するという方法ではなく、
DMax関数を用いて求めます。No2のコードの中の

lnMax = DLookup("請求書番号の最大", "Q請求番号")

を、

lnMax = DMax("請求書番号", "売上テーブル")

にしました。他はほとんど変更していません。
以上です。

補足

ご回答ありがとうございます。教えていただいた方法でうまくいきました！感謝いたします。
大変恐縮なのですが、追加で教えていただきたいことがあります。

実は、１ヶ月毎の請求から、四半期毎（３ヶ月毎）の請求に変更になりました。
いまのままですと、３ヶ月分の売上データに請求番号をつけると、当然のことながら、同一会社の請求書が３通できてしまいます。１通になるようなよい方法を教えていただければ幸いです。

さらに、できれば教えていただきたいのですが、たとえば、４～６月分の請求書を発行する時に、早期発行希望の会社には、６月中に請求書を発行し、その他は、７月に入ってから請求書を発行している関係から、会社情報というテーブルの早期発行希望にyesチェックの入っている会社の売上データだけに請求番号を振る機能もつけられたら、助かります。

いろいろお願いしてしまいましたが、よい方法があれば、ご教授お願いいたします。

回答No.3

piroin654

No2です。不要なものを残していました。

Sub test()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim lnMax As Long

のところで

Dim rs3 As DAO.Recordset

は使用していないので、取り除いてください。
あっても問題はないのですが、念のために。
失礼しました。

回答No.2

piroin654

少し準備が必要ですが、以下のように。

(1)
以下のSQL文を新規のクエリのSQLビューに貼り付け、
「Q売上月」という名前で登録します。

SELECT Format([売上日],"yyyy/mm") AS 売上月, 売上テーブル.顧客名, 売上テーブル.請求書番号
FROM 売上テーブル
GROUP BY Format([売上日],"yyyy/mm"), 売上テーブル.顧客名, 売上テーブル.請求書番号
ORDER BY Format([売上日],"yyyy/mm"), 売上テーブル.顧客名;

(2)
新しいクエリのデザインビューに「売上テーブル」を表示し、
フィールドに「売上日」のみを表示し、ツールバーで集計ボタンを
押し、集計で「最大」を選択します。このまま「Q請求番号」
という名前で保存してください。
SQL文は以下のようになっていると思いますが、集計ボタンを
押して一旦グループ化しています。確認をしてください。

SELECT Max(売上テーブル.請求書番号) AS 請求書番号の最大
FROM 売上テーブル;

(3)
以下を標準モジュールに貼り付けるか、ボタンクリックで呼び出すか、
あるいはイミディエイトウィンドウでtestを実行するか、あるいは
Sub test()

End Sub
の中身をボタンクリックのイベントに入れるかのどれかで
実行してください。
なお、DAOを使っているのでコード表のツール→参照設定から
Microsoft DAO xx Object Library　にチェックをいれてください。
xxは3.6のような数字です。

Sub test()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim lnMax As Long

Set db = CurrentDb
Set rs1 = db.OpenRecordset("売上テーブル", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Q請求月")
lnMax = DLookup("請求書番号の最大", "Q請求番号")

rs2.MoveFirst
Do Until rs2.EOF
　　lnMax = lnMax + 1
　　rs1.MoveFirst
　　Do Until rs1.EOF
　　If IsNull(rs2!請求書番号) Then
　　　　If Format(rs1!売上日, "yyyy/mm") = rs2!売上月 And rs1!顧客名 = rs2!顧客名 Then
　　　　　　rs1.Edit
　　　　　　　　rs1!請求書番号 = lnMax
　　　　　　rs1.Update
　　　　End If
　　Else
　　　　lnMax = lnMax - 1
　　　　Exit Do
　　End If
　　rs1.MoveNext
　　Loop
rs2.MoveNext
Loop
rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing
db.Close: Set db = Nothing
End Sub

以上です。分からないところがあれば補足してください。

回答No.1

lv4u

>>顧客毎に、自動的に連番（2523～）で請求番号を振る方法がありましたら、
教えてください。どうぞよろしくお願いいたします。

顧客ごとに請求書番号を請求書番号テーブルを作成して、プログラムで請求書発行するごとに＋１すればいいのではないですか？
プログラミングは必要ですが、それを作れば自動的に処理されます。