アクセスにおける一覧データ作成時のテクニックについて。。。

質問

アクセスにおける一覧データ作成時のテクニックについて。。。

いつも皆様にはお世話になっています。

只今、アクセスを使用してデータ抽出をしているのですが、
浅学な私に大きな壁が。。。orz

複数商品の在庫を所持している倉庫ナンバーのみを抽出したい場合です。
現在は添付画像：「現在」のようにクロス集計しています。

クロス集計の条件
行見出し：商品名
列見出し：倉庫順番:倉庫ナンバー（後に添付画像用に「倉庫ナンバー」と改名・セル結合しています）
集計の値：倉庫ナンバー（先頭）

大きな壁：
倉庫ナンバーを1セル毎に表示ではなく、
1セルだけで、例えばカンマ区切りに表示させる方法はないでしょうか？
添付画像：「理想」がそれです。

何卒、お力添え宜しくお願い致します。。。

回答No.6

DexMachina · ベストアンサー

No.2です。

> 3464:抽出条件でデータ型が一致しません

「商品名」「倉庫ナンバー」が空白(Null)だった場合にエラーになるのかと
当たりをつけてこちらでサンプルデータを作成して確認してみましたが、
残念ながらエラーにはなりませんでした。

上記以外では、こちらの経験から、
　・当該集計クエリがテーブルからではなく別のクエリから作成していて、
　　そこで「商品名」辺りにエラーが発生している
という可能性が推測されます。
(ユーザー関数に限らず、関数の引数に演算フィールドの演算結果が
　含まれていて、その値がエラー値になっている場合、ご質問と同様の
　型不一致エラーが発生します)

もしも上記の推測が当たっているようでしたら、当該集計クエリは、恐らく
エラー発生時も一応結果は表示されると思いますので、エラーの発生して
いるデータ(「倉庫」の表示が「#Error」となっているレコード)を確認の上、
元となっているデータを見直してみてください。
(修正方法としては、元となっているクエリで、
　　・0で除算しているようなら分母にあたるフィールドに「>0」を、
　　・「Left([フィールド名],Len([フィールド名])-1」といった式を使用している
　　　なら、同様に「Is Not Null」を、
　それぞれ抽出条件に指定する、といった形になります：
　【参考】Len関数の引数がNullだった場合、その戻り値は0ではなくNullになります)

なお、エラーになる件数が余りに多く、MsgBoxを毎回閉じるのが面倒なよう
でしたら、DMembers関数の末尾の方を以下のように修正してみてください。
(いつもの癖で「:」を使用して1行に纏めてしまっていたので、併せてそこも
　修正しました。なお、「終了処理:」「エラー処理:」の「:」は必須です(念のため))

【現在】
終了処理:
　 DMembers = Rsl: Set DRS = Nothing: Set DBS = Nothing: Exit Function
エラー処理:
　 Rsl = "#Error": MsgBox Err & "：" & Error$, , "DMembers(関数)": Resume 終了処理
End Function

【修正】
終了処理:
　　DMembers = Rsl
　　Set DRS = Nothing
　　Set DBS = Nothing
　　Exit Function
エラー処理:
　　Select Case Err.Number
　　　　Case 3464
　　　　　　'「型不一致」エラー時はMsgBoxを表示しない
　　　　Case Else
　　　　　　'上記以外のエラー時は、従来通りMsgBoxを表示
　　　　　　MsgBox Err & "：" & Error$, , "DMembers(関数)"
　　End Select
　　Rsl = "#Error"
　　Resume 終了処理
End Function

・・・上記の推測が外れていたとすると、申し訳ありませんが原因の推測が
つきません(汗)

補足

ありがとうございました！
無事抽出できました＞＜

理想の表が出来そうです。
駄目だった原因は、
「当該集計クエリがテーブルからではなく別のクエリから作成していて、
そこで「商品名」辺りにエラーが発生している」
だったようです。

きちんと注意して作り直したら大丈夫でした。
本当に、皆さん有難うございましたｍ（＿＿）ｍ
大変感謝感激でございます。

回答No.5

shinkami

現在のテーブルを基にクエリーで式を追加してこれを表示します

倉庫No.: IIf(IsNull([No.1]),"",[No.1]) & IIf(IsNull([No.2]),"","," & [No.2]) & IIf(IsNull([No.3]),"","," & [No.3])

倉庫の数が多いようで式が長くなりますが式を2重3重に重ねれば何とかなると思うのですが

回答No.4

nicotinism

ADOは良く知らないのですが、(^^ゞ
予め新規にテーブルを作成します。
フィールド名は適当に付けてください。仮にF1・F2とします
F1→テキスト型
F2→テキスト型（フィールドサイズは、255）
このテーブル名を、仮に、T1 とします。
標準モジュールに下記をコピペ
Q1のところは実際のクロス集計クエリ名に変更してください。
テーブル T1 がある場合もテーブル名と下記の T1 を変更。
Sub test()
Dim cn As ADODB.Connection
Dim rsFrom As ADODB.Recordset
Dim rsTo As ADODB.Recordset
Dim i As Integer
Dim s As String
Set cn = CurrentProject.Connection
Set rsFrom = New ADODB.Recordset
Set rsTo = New ADODB.Recordset

rsFrom.Open "Q1", cn, CursorType:=adOpenStatic 'Q1 を変更
rsTo.Open "T1", cn, adOpenKeyset, adLockPessimistic '必要に応じて
Do Until rsFrom.EOF
　　s = ""
　　For i = 1 To rsFrom.Fields.Count - 1
　　　　If Not IsNull(rsFrom.Fields(i)) Then
　　　　　　s = s & CStr(rsFrom.Fields(i)) & Chr(44)
　　　　End If
　　Next i
　　s = Left(s, Len(s) - 1)
　　rsTo.AddNew
　　rsTo.Fields(0) = rsFrom.Fields(0)
　　rsTo.Fields(1) = s
　　rsTo.Update
　　rsFrom.MoveNext
Loop
rsFrom.Close: Set rsFrom = Nothing
rsTo.Close: Set rsTo = Nothing
MsgBox "テーブル T1 を開いてみてください。どうかしらん？"

End Sub

当方WinXP & Access2002
※テーブル T1 に追記されますのでご注意を

回答No.3

layy

前提で、
倉庫に関わるなら列がどのくらいあるかの最大は物理的に決まっていると思います。

クロス集計クエリの列が実行する度にデータによって変動していると思いますが、
クロス集計の「クエリ列見出し」プロパティにて固定列にできます。

列名を倉庫にして
１，２，４，・・・・・・・・，３２
と途中抜ける番号も必ず出るようにする
１，２，３，４，・・・・・・・・，３１，３２

こうしておけば、すべての列を結合するもう１つの項目を
準備できると思います。
まとめ："0"＆[１]＆","＆[２]＆","＆[３]＆","＆[４]＆","・・・・＆[３１]＆","＆[３２]

ここまでできれば
「0,2,3,,,,,,,31,32」
みたいな形になるのでは、と思います。
「0,」「,,」は置換します。

考えてみただけですので
実際少し違うかもしれませんが、参考で。

回答No.2

DexMachina

No.1です。

> こちらが黒塗りつぶしになります↓↓↓
>「, DBS As DAO.Database」

Accessのバージョンによっては、初期状態では
DAOが有効になっていないのを失念していました(汗)
(代わりに「ADO」が有効になっています)
http://www.accessclub.jp/dao/01.html

VBEのメニューで、「ツール(T)→参照設定(R)」を
選択して『参照設定』ダイアログを呼び出したら、
「Microsoft DAO 3.x Object Library」のチェックを
オンにして、『Ok』ボタンで閉じてください。

これで、DAOへの参照設定が有効になりますので、
「ユーザー定義型は～」のエラーは発生しなくなります。

大変失礼致しました(汗)

補足

こちらから質問をしたのに、返答が遅くなり申し訳ありません。

↓↓↓準備↓↓↓
----------------------------------------------------
アクセスver2000
モジュール作成OK
「Microsoft DAO 3.x Object Library」のチェックOK
テーブル名：Sheet1
フィールド1名：商品名
フィールド2名：倉庫ナンバー
----------------------------------------------------

集計クエリ
商品名：グループ化
倉庫: DMembers("[倉庫ナンバー]","Sheet1","[商品名]='" & [商品名] & "'")：演算

上記のクエリを実行すると、小さなエラーウィンドウが表示されます。
ウィンドウ名：DMembers(関数)
内容：3464:抽出条件でデータ型が一致しません

もう一歩だと思うのですが、、
もう何が間違っているのかさっぱりになってきてしまいました。。

回答No.1

DexMachina

Access2007以降なら、新規追加された『複数値フィールド』を使用する
方法があるかもしれませんが(追加クエリでの追加?)、試してみたことが
ないので・・・(汗)
http://office.microsoft.com/ja-jp/get-started-with-2007/HA010024185.aspx#BMdatatypes

Access2003以前の場合は、組込機能では対応できないと思いますので、
以前、他の方の質問への回答時に作成した、DMembers関数をご紹介
します(Access2007以降でも使用は可能です)：
http://okwave.jp/qa/q5168984.html
(回答No.1の『<第2段階>』とした部分以降を、特に参照)

上記関数を標準モジュールに貼付・保存したら、後は集計クエリで
以下のような式を指定した演算フィールドを作成すれば、ご希望の
結果が得られるかと思います：

倉庫ナンバーズ: DMembers("[倉庫ナンバー]", "テーブル名", "[商品名]='" & 商品名 & "'", "[倉庫ナンバー]")

・・・以上です。
(既存フィールドと同じ名前を演算フィールド名にするとエラーに
　なるため、「倉庫ナンバー『ズ』」としています。念のため。)

補足

(1)モジュールを作成
(2)集計クエリを作成
(3)実行するとエラー

コンパイルエラー「ユーザー定義型は定義されていません」とウィンドウが表示され。
モジュール画面になります。そして、

こちらが黄色塗りつぶしになり↓↓↓
「Public Function DMembers(sExpr As String, sDomain As String, _
　　　Optional sCriteria As String, Optional sOrder As String) As String」

こちらが黒塗りつぶしになります↓↓↓
「, DBS As DAO.Database」

で、出来ません。。