Access (VBA)

Access VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Access 2016)
CSVデータ取り込み時に、空欄データを前行の値をセットして取り込む
投稿日時: 21/11/25 23:20:40
投稿者: apple777

お世話になります。
件名そのものの質問になりますが、下図(1)のようなCSVデータ下図(2)のようにしてAccessに取り込むVBAを作りたいです。
Accessのマクロ機能しか利用したことがなく、VBAは全くの素人で申し訳なのですが、よろしくお願いいたします。
 
◆機能要件は次の通りです。
・データの明細行件数は毎回増減します。
・データ更新時には、既にあるデータは全て削除して取り込みします。
・データは、Excelに置き換えると、A〜AM列まで存在し、空欄データを埋めたい列は、「F」と「AB」列になります。
・1行目は項目名で、2行目から実際のデータがセットされたデータです。
 
【下図(1)】
        C        F        AB            AD            AE
                         
1        取引No    会計日        リソース名    カテゴリ名    メニュー名
2        1     2021/10/1    現金     諸費用     出前
3        1                          諸費用     入会セット
4        1                  月会費     割引会員
5        2     2021/10/1    PayPay     用品     スプーン
6        2                  用品     スプーン
7        3     2021/10/1    現金     月会費     割引会員
8        4     2021/10/1    現金     月会費     割引会員
9        5     2021/10/1    PayPay     短期教室    料理教室
10        6     2021/10/1    Airペイ     月会費     割引会員
【下図(2)】
        C        F        AB            AD            AE
                         
1        取引No    会計日        リソース名    カテゴリ名    メニュー名
2        1     2021/10/1    現金     諸費用     出前
3        1     2021/10/1    現金     諸費用     入会セット
4        1     2021/10/1    現金         月会費     割引会員
5        2     2021/10/1    PayPay     用品     スプーン
6        2     2021/10/1    PayPay     用品     スプーン
7        3     2021/10/1    現金     月会費     割引会員
8        4     2021/10/1    現金     月会費     割引会員
9        5     2021/10/1    PayPay     短期教室    料理教室
10        6     2021/10/1    Airペイ     月会費     割引会員

回答
投稿日時: 21/11/26 10:47:13
投稿者: Suzu

ご存じの通り、Accessでは並び順は 特定の条件を指定する事で 再現可能な 並び順になります。
 
今回のCSVデータを、そのまま テーブルに取り込んだ場合、並び順を再現する為の方法がなく
上のレコードのフィールドのデータが空白であれば」という条件を特定する方法が無くなります。
 
簡単なのは
1. インポート先のとは別に作業用のテーブルを作成
2. そのテーブルには オートナンバーフィールド(ID)を追加しておく
3. 空白が含まれるレコードの オートナンバーフィールドの値 から 1を引いた値のレコードの
  フィールドの値を参照するクエリを作成
4. 3. で作成したクエリから 追加クエリを作成し 本来のインポート先のテーブルに追加
 
 
3のクエリは
  
SELECT
 ID,
 IIF(ISNULL(会計日), DLOOKUP("会計日","テーブル名","ID=" & [ID]-1),会計日) AS 会計日2,
 IIF(ISNULL(リソース名), DLOOKUP("リソース名","テーブル名","ID=" & [ID]-1),リソース名) AS リソース名2
FROM テーブル名
  
の様な感じになるかと。
 
 
4.まで出来れば
 
マクロの流れとしては、
1)作業用テーブルのレコード削除
2)本来の追加先のレコードの削除 (必要なら)
3)追加クエリの実行
で出来ますよね。

回答
投稿日時: 21/11/26 14:35:39
投稿者: hatena
投稿者のウェブサイトに移動

提示されたデータ例を見る限りは、同じ[取引No]で共通の値になるようにすればいいようなので、
それでいいなら、オートナンバーはなくても下記の更新クエリでできそうです。
 
 

    フィールド: 会計日      リソース名
    テーブル名: テーブル名  テーブル名
レコードの更新: ※1         ※2
      抽出条件: Is Null
        または:             Is Null

 
※1 DMax("会計日","テーブル名","取引No=" & [取引No])
※2 DMax("リソース名","テーブル名","取引No=" & [取引No])
 
SQLなら、
UPDATE テーブル名 SET
 会計日 = DMax("会計日","テーブル名","取引No=" & [取引No]),
 リソース名 = DMax("リソース名","テーブル名","取引No=" & [取引No])
WHERE
 会計日 Is Null OR リソース名 Is Null;

 
上記の前提条件がないなら、Suzuさんの方法かVBAで取り込む方法になりますね。

投稿日時: 21/11/27 22:49:05
投稿者: apple777

Suzuさん、hatenaさん
ご回答いただき、本当にありがとうございます。
 
hatenaさんの「更新クエリ」を利用する方法が簡単そうだったので、
試してみました。
 
が、下記の通り、うまくいかず、解決策が解らない状況です。
 
大変お手数をかけますが、どなたかご教授いただけると助かります。
 
よろしくお願いいたします。
 
まず、更新クエリの設定は次の通り設定しました。
※テーブル名は「kaikei」です。
 
    フィールド: 会計日 リソース名
    テーブル名: kaikei   kaikei
レコードの更新: ※1     ※2
      抽出条件: Is Null
        または:      Is Null
 
※1 DMax("会計日","kaikei","取引No=" & [取引No])
※2 DMax("リソース名","kaikei","取引No=" & [取引No])
 
クエリを実行すると、黄色の△ビックリマークで、次のメッセージが表示されて、
「はい」とします。
-------------------
10件のレコードが更新されます。
 
[はい]をクリックするとレコードは更新され、元に戻すことはできなくなります。
レコードを更新してもよろしいですか?
-------------------
次に、以下のメッセージが表示されます。
※テーブルのデータ型は全て「短いテキスト」です。
-------------------
10 個のフィールドで型変換エラー、0 件のレコードでキー違反、0 件のレコードでロック違反、0 件のレコードで入力規則違反が発生したため、レコードを更新できませんでした。
このアクション クエリの実行を続行しますか?
このエラーを無視し、アクション クエリを実行するときは [はい] をクリックしてください。
これらのエラーが発生する原因については、[ヘルプ] をクリックし、表示されるヘルプ トピックを参照してください。
--------------------

回答
投稿日時: 21/11/28 06:17:15
投稿者: ねこふんじゃった

「取引No」のフィールドを数値型にできれば解決しそうですが、未確認です

回答
投稿日時: 21/11/28 09:20:14
投稿者: hatena
投稿者のウェブサイトに移動

ねこふんじゃったさんの回答にあるように取引Noを数値型にすればいいでしょう。
 
なんらかの事情で数値型にできない場合は、レコードの更新の式を下記のようにすればいいでしょう。
 
※1 DMax("会計日","テーブル名","取引No='" & [取引No] & "'")
※2 DMax("リソース名","テーブル名","取引No='" & [取引No] & "'")

投稿日時: 21/11/28 16:55:46
投稿者: apple777

ねこふんじゃったさん、hatenaさん
 
早速ありがとうございます。
 
取り込み時に数値型とのことで、試してみました。
 
CSVデータ取り込み時のデータ型が、
「整数型」「長整数型」などあると思いますが、
この両方は、試しましたが取り込みエラーとなり
正常に取り込みできませんでした。
どのデータ型で取り込みすれば良いでしょうか?
大変お手数ですが、よろしくお願いいたします。

回答
投稿日時: 21/11/29 13:15:54
投稿者: hatena
投稿者のウェブサイトに移動

CSVデータ取り込み時にエラーになるということは、取引Noに数値型にできないデータが含まれている可能性があります。その場合はテキスト型のまま取り込んで、前回の回答の数値型にできない場合の式で試してみてください。

投稿日時: 21/11/29 22:54:50
投稿者: apple777

hatenaさん
 
お世話になります。度々ありがとうございます。
 
うまく数値型にできないので、
 
※1 DMax("会計日","テーブル名","取引No='" & [取引No] & "'")
※2 DMax("リソース名","テーブル名","取引No='" & [取引No] & "'")
 
に変更して試しました。
 
しかし、クエリを実行すると、前回と同様の下記のメッセージとなり、うまくいきません。
 
本当に申し訳ありませんが、なにか対応方法がありましたら、ご教授ください。
 
よろしくお願いいたします。
 
10 個のフィールドで型変換エラー、0 件のレコードでキー違反、0 件のレコードでロック違反、0 件のレコードで入力規則違反が発生したため、レコードを更新できませんでした。
このアクション クエリの実行を続行しますか?
このエラーを無視し、アクション クエリを実行するときは [はい] をクリックしてください。
これらのエラーが発生する原因については、[ヘルプ] をクリックし、表示されるヘルプ トピックを参照してください。

投稿日時: 21/11/29 23:05:26
投稿者: apple777

すみません、「取引No」項目の数値が、実際のものと異なっていたため、
実際のデータを貼り付けます。
前ゼロがあるなど、そういったところで、問題なのでしょうか?
引き続き、よろしくお願いいたします。
 
取引No
000120211001113630188
000120211001113630188
000120211001113630188
000120211001113731901
000120211001113731901
000120211001114111242
000120211001114146860
000120211001114647176
000120211001115023451

回答
投稿日時: 21/11/30 02:48:25
投稿者: hatena
投稿者のウェブサイトに移動

当方のサンプルでは、下記のSQLで問題なく更新できています。
 

UPDATE テーブル1 
SET
 会計日 = DMax("会計日","テーブル名","取引No='" & [取引No] & "'"),
 リソース名 = DMax("リソース名","テーブル名","取引No='" & [取引No] & "'")
WHERE
 会計日 Is Null Or リソース名 Is Null;

 
実験したテーブルデータは下記です。
テーブル1
取引No                会計日	 リソース名 カテゴリ名 メニュー名
000120211001113630188 2021/10/01 現金       諸費用     出前
000120211001113630188                       諸費用     入会セット
000120211001113630188                       月会費     割引会員
000120211001113731901 2021/10/01 PayPay     用品       スプーン
000120211001113731901                       用品       スプーン
000120211001114111242 2021/10/01 現金       月会費     割引会員
000120211001114146860 2021/10/01 現金       月会費     割引会員
000120211001114647176 2021/10/01 PayPay     短期教室   料理教室
000120211001115023451 2021/10/01 Airペイ    月会費     割引会員

 
上手くいかないなら、実際のSQLを提示してください。
 

トピックに返信