1レコードのデータを数量条件に応じて分割してレコー

質問

access初心者です。

現在、もともとつくられたアクセスを改修しております。
いただいたデータを取り込んだ基のテーブル「届け先一覧」があります。
項目に住所・電話・名前・品目（1商品のみ）・個数があります。

このデータから、宅配便に発送する出荷データを作成しようと思っております。
ただし、1箱に入る個数が15個までなので15個以上の発送を希望される方には15個づつデータを分割しなくてはなりません

例）　住所・電話・名前・品目（1商品のみ）・個数

1届け先で個数40個の場合　15個・15個・10個というようにもともとあるレコードを15個毎に分割して作りたいのです。
上記の1レコードを15個以上の個数の場合下記のような結果を作り出したい
例）
住所・電話・名前・品目（1商品のみ）・15個
住所・電話・名前・品目（1商品のみ）・15個
住所・電話・名前・品目（1商品のみ）・10個

それをもとに「届先のピッキングリスト」「届け先一覧」
どのような手順でもとのデータを分割するような関数を作って希望するレコードデータを作成したら良いのでしょうか？

回答No.3

kkkkkm · ベストアンサー

No2の追加です。

一意のデータフィールドがない場合に
追加のSQLを使わずに直接tmpテーブルに書き込む方法です。

Private Sub 出荷データ作成_Click()
Dim RTmp As DAO.Recordset
Dim StrSQL As String
Dim i As Integer, j As Integer
Dim Lot As Long

Lot = 15

DoCmd.SetWarnings False

'tmpテーブルのデータ削除
StrSQL = "Delete [tmp].[住所] " & _
"FROM [tmp] " & _
"WHERE ((([tmp].[住所]) Like ""*""));"
DoCmd.RunSQL (StrSQL)

'tmpテーブルへのデータ追加
If Me.[個数] Mod Lot = 0 Then
j = Me.[個数] / Lot
Else
j = Int(Me.[個数] / Lot) + 1
End If
Set RTmp = CurrentDb.OpenRecordset("tmp", dbOpenTable)
With RTmp
For i = 1 To j
If i = Int(Me.[個数] / Lot) + 1 Then
Lot = Me.[個数] Mod Lot
End If
.AddNew
.Fields("住所") = Me!住所
.Fields("電話") = Me!電話
.Fields("名前") = Me!名前
.Fields("品目") = Me!品目
.Fields("個数") = Lot
.Update
Next
End With

RTmp.Close
Set RTmp = Nothing

DoCmd.SetWarnings True

End Sub

お礼

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

書き込みを参考に既に動いているVBAに以下のように加えて
動作確認をいたしましたところ、思うような結果が得られました。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

'出荷データをエクスポートする前に分割する
DoCmd.OpenQuery "累積データ編集集約_work"
DoCmd.OpenQuery "累積データ編集集約_削除"

Dim cnWork As ADODB.Connection
Dim rsWork As ADODB.Recordset
Dim cnDest As ADODB.Connection
Dim rsDest As ADODB.Recordset
Const lotMax As Integer = 15 '1ピッキングのマックス数
Dim cnt As Integer '数量の残数管理
Dim setValue As Integer 'テーブルへの設定値

Set cnWork = CurrentProject.Connection
Set rsWork = New ADODB.Recordset
Set cnDest = CurrentProject.Connection
Set rsDest = New ADODB.Recordset

Call rsWork.Open("累積データ編集集約_work", cnWork, adOpenDynamic, adLockPessimistic)
Call rsDest.Open("累積データ編集集約", cnDest, adOpenDynamic)

Set RWork = CurrentDb.OpenRecordset("累積データ編集集約_work", dbOpenTable)
Set RDest = CurrentDb.OpenRecordset("累積データ編集集約", dbOpenTable)

RWork.MoveFirst
Do Until RWork.EOF
cnt = RWork!数量1
Do While cnt > 0
RDest.AddNew
RDest!ID = RWork!ID
RDest!ご連絡先 = RWork!ご連絡先
RDest!郵便番号 = RWork!郵便番号
RDest!住所 = RWork!住所
RDest!電話番号 = RWork!電話番号
RDest!品目CD1 = RWork!品目CD1
RDest!品目名1 = RWork!品目名1
RDest!数量1 = RWork!数量1
RDest!区分1 = RWork!区分1
If cnt >= lotMax Then
setValue = lotMax
Else
setValue = cnt
End If
RDest!数量1 = setValue
cnt = cnt - setValue
RDest.Update
Loop
RWork.MoveNext
Loop

回答No.2

kkkkkm

方法のひとつです。

以下の説明での名前は適当なのでプロシージャの中も含めて適宜変更してください。

届け先一覧をコピーして貼り付けの時に「テーブル構造のみ」で「tmp」というテーブルを作成します。
届け先一覧をもとにした単票フォーム「届け先一覧フォーム」を作成します。
フォームにボタンを作成します。プロパティ→その他の「名前」を「出荷データ作成」にします。

ボタンのイベントプロシージャを作成したら

Private Sub 出荷データ作成_Click()

End Sub

というのができますから以下のように変更します。

IDは届け先一覧テーブルの中でレコードの中で一意のデータがあるフィールドを指定します。注文番号とか。
作成したフォームに表示する必要があります。

ボタンをクリックしたらフォームに表示されているデータが分割されて「tmp」に出力されます。

Private Sub 出荷データ作成_Click()
Dim StrSQL As String
Dim i As Integer, j As Integer
Dim Lot As Long

Lot = 15

DoCmd.SetWarnings False

'tmpテーブルのデータ削除
StrSQL = "Delete [tmp].[ID] " & _
"FROM [tmp] " & _
"WHERE ((([tmp].[ID]) Like ""*""));"
DoCmd.RunSQL (StrSQL)

'tmpテーブルへのデータ追加
If Me.[個数] Mod Lot = 0 Then
j = Me.[個数] / Lot
Else
j = Int(Me.[個数] / Lot) + 1
End If

For i = 1 To j
If i = Int(Me.[個数] / Lot) + 1 Then
Lot = Me.[個数] Mod Lot
End If
StrSQL = "INSERT INTO [tmp] ( [住所], [電話], [名前], [品目], [個数] ) " & _
"SELECT [届け先一覧].[住所], [届け先一覧].[電話], [届け先一覧].[名前], [届け先一覧].[品目], " & Lot & " AS [定数]" & _
"FROM [届け先一覧] " & _
"WHERE ((([届け先一覧].[ID])=[Forms]![届け先一覧フォーム]![ID]));"
DoCmd.RunSQL (StrSQL)
Next

DoCmd.SetWarnings True

End Sub

回答No.1

bardfish

私なら・・・ということで参考までに。

作業用の中間データを入れておく一時作業テーブルを作成します。
そのテーブルは1レコード＝1個とします。ですから個数は持ちません。
そしてデータのムダを省くために発送先をコード化するための発送先テーブルを作成し、一時テーブルには発送先の住所や電話番号は持たずに発送先テーブルの発送先コードだけを用意します。
で、発送先コードと品目があれば出荷テーブルを作成すときに一時テーブルから15レコードずつ読み込んで出荷テーブルに1レコード書き出す。

一時テーブルから読み込むレコードがなくなったら15件に満たなくても出荷テーブルに書き出す。

というアルゴリズムを基本としてコードを書き始めるかな？

初心者ならば、いきなりAccessに向き合うのではなく、フローチャートでも箇条書きでもなんでもいいですが、どういう流れにするかを考えることから始めたほうがいいです。

私の場合は、AccessやExcelを使用していてもクエリとかVLOOKUPなどの便利な関数は使用せずに、代替をSQLで済ませてしまいます。
その際のメリットというのは、Visual Basicに移行しやすいからというのが理由。
Accessがインストールされているパソコンならmdb、accdファイルをVisual Studio VB.NETにアタッチして比較的簡単に利用することができるからです。更にいうと、VB .NETでは配列変数にDataTable型というものがあり、変数内でかんたんなクエリを実行してデータを抽出したりソートすることができるし、DataGriViewというExcelのワークシートみたいなコントロールにバインドするとかんたんにAccessのテーブルを開いたときに似たような感じで変数のデータを見ることができます。
このあたりはもう少しスキルを高めてからのほうがいいのかな？それともAccessは捨てていきなりVB.NETで開発を始めてもいいのかな？
Visual Studio .NETは無料で利用できるExpressエディションがあります。
Windowsデスクトップソフトを作りたいならVisual Basic 2017が最新かな？2019もあって無利用版もダウンロードできるけどWindowsストアアプリみたいなものしか作れないかもしれない。
無難なところで「Visual Basic Desktop」で検索してみてください。

お礼

ありがとうございます。
本当は、いただいたアドバイスように最初から考えて作るのがまっとうなのですが、運用方法もいただくデータ等も同じもので今回流用のが軽微改修と判断されたためです。話があってから動きがなく、年始からすぐにはじまるということなので、今あるものを直して使えないかと思いご相談した次第です。