Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Home Premium : Excel 2013)
ADOでAccessに接続 クロス集計をする
投稿日時: 18/04/05 09:28:28
投稿者: mmiwa

jetSQLで
 
TRANSFORM min ###
SELECT €€€
FROM
(select %%% from @@@ where £££
union
select \\\ from @@@ where $$$)
GROUP BY €€€
PIVOT Pivotfield名
 
という構文でクロス集計を行います
 
この時、具体的なPivotfield名ではなく、unionを用いたレコードソースの「*番目のフィールド」という書き方を用いて列見出しを指定したいのですが記載法が分かりません 教えてください

回答
投稿日時: 18/04/05 13:22:17
投稿者: sk

引用:
この時、具体的なPivotfield名ではなく、unionを用いたレコードソースの
「*番目のフィールド」という書き方を用いて列見出しを指定したいのですが
記載法が分かりません

Jet SQL の記述だけで解決する方法はないか」というのが
ご質問の趣旨ならば、「Jet SQL では列番号を指定して
任意のフィールドを参照することが出来ないので不可能である」
という回答になります。
 
引用:
select %%% from @@@ where £££
union
select \\\ from @@@ where $$$

ADO を使用されているのであれば、上記のユニオンクエリの結果を
Recordset として参照し、Fields プロパティ(コレクション)を介して
n番目のフィールドの名前( Name プロパティの値)を取得した上、
 
引用:
TRANSFORM min ###
SELECT €€€
FROM
(select %%% from @@@ where £££
union
select \\\ from @@@ where $$$)
GROUP BY €€€
PIVOT Pivotfield名

取得したフィールド名を PIVOT 句で指定する
列見出しとして文字列連結するようになさればよいはず。

投稿日時: 18/04/05 18:14:58
投稿者: mmiwa

ありがとうございます
  
unionクエリの結果をrecordsetとして参照し
field名を取得してクロス集計を試みましたが
transformステートメントの構文エラーになりました
  
unionクエリを
  
select %%% as AA from @@@ where £££
union
select \\\ as AA from @@@ where $$$
  
と書き換えて
対象のfieldにAAという名前を設定し
pivot句にAAを指定したのち
クロス集計を試みましたが
こちらもtransformステートメントの構文エラーになりました
  
transform句などの問題を否定するために
selectstatementを
select %%% from @@@ where £££ だけ
ないしは
select \\\ from @@@ where $$$ だけにすると
問題なくクロス集計が出来ました
  
unionクエリに問題があるのでしょうか
アドバイスいただけると助かります

回答
投稿日時: 18/04/06 09:19:16
投稿者: sk

引用:
unionクエリの結果をrecordsetとして参照し
field名を取得してクロス集計を試みましたが
transformステートメントの構文エラーになりました

実際にどのようなコードを記述されているのでしょうか。

投稿日時: 18/04/06 09:28:44
投稿者: mmiwa

昨晩、access内に
 
下記SQL文を記載して
 
select %%% from @@@ where £££
union
select \\\ from @@@ where $$$
 
ユニオンクエリを作成 Q_Union
これをレコードソースとして
 
TRANSFORM min ###
SELECT €€€
FROM Q_Union
GROUP BY €€€
PIVOT Pivotfield名
 
及び
 
TRANSFORM min ###
SELECT €€€
FROM Q_Union
GROUP BY €€€
PIVOT 何番目かのフィールド名
 
を用いてクロス集計を行ったところ
問題なく集計できました
 
特別記載方法を変更していないので
構文エラーではないと思います
 
推測される問題箇所をどなたか指摘して頂けると幸いです
 
新たな書き込みがなければ一週間程度を目途に
解決済みにさせて頂きます

回答
投稿日時: 18/04/06 10:14:18
投稿者: sk

引用:
推測される問題箇所をどなたか指摘して頂けると幸いです

「 n番目のフィールドの名前」を取得する際の SQL 文と
クロス集計クエリの FROM 句に記述されている SQL 文(サブクエリ)が
一致していないからではないですかね。
 
(実際に記述されたコードを見れば分かることなのですが)
 
引用:
unionクエリを
   
select %%% as AA from @@@ where £££
union
select \\\ as AA from @@@ where $$$
   
と書き換えて

例えば、このステートメント単体を String 型の変数に
代入するような処理になっているかどうかなど。

投稿日時: 18/04/09 11:58:22
投稿者: mmiwa

SK様
 
ご指導ありがとうございます
自宅がau回線のためmougに接続できず返信が遅くなったことをお詫びいたします
 
実際のコードは下記のようです
ご指導お願いいたします
 
myTable = _
"(select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & ' A' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'A' " & _
"and [result].[iname] in (select 表示項目 from 項目名)) " & _
"union " & _
"(select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & 'B' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'B' " & _
"and [result].[iname] in (select 表示項目 from 項目名)) "
 
mySQL = _
"transform min(val(result)) " & _
"select sname, rdate " & _
"from " & myTable & " " & _
"group by sname, rdate " & _
"pivot [iiname];"

回答
投稿日時: 18/04/09 13:14:13
投稿者: Suzu

こんにちは。
 
まず、直接の回答としては、
FROM句 に サブクエリを使う場合、括弧の使い方にミソがあると言うかテクニックが必要な場合があります。
(法則性は判りません。。経験則と思ってください)
 
・FROM句に サブクエリを使う場合には、エイリアス名を設けてみる。
・サブクエリ内のサブクエリは避ける。
・() AS Q_Tmp でダメなときは、[]. AS Q_Tmp を試してみる。
   特にサブクエリ内でどうしてもサブクエリを使用しないといけない時。
  [select %%% from @@@ where £££
   union
   select \\\ from @@@ where $$$]. AS Q_Tmp
 
 
今回の提示されたSQLは サブクエリ内でサブクエリを指定しています。
これ。。外せますよね??
WHERE句に持ってくるからサブクエリになりますが、FROM句で JOINすれば良いのでは?
 
また、なぜ、UNIONクエリにしないといけないの?
 
and [hik].[tikind] = 'A'
  ↓
and ([hik].[tikind] = 'A' or [hik].[tikind] = 'B')

 
[result].[iname] & ' A' as [iiname]
 ↓
[result].[iname] & [hik].[tikind] as [iiname]
あたりを使えば、UNION しなくて済みませんか?
 
もっとも、
(select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & ' A' as
 
このスペースがワザとなのであれば、IIF使うとか一工夫必要ですが。。

投稿日時: 18/04/09 15:43:49
投稿者: mmiwa

Suzu様
 
ありがとうございます
 
まず最初に少し訂正させてください
myTableの8行目は
[result].[iname] & 'B' as [iiname] ⇒ [result].[iname] & ' B' as [iiname]
 
Bの前にもスペースを設けるつもりでした
ですからご指摘のあった [result].[iname] & ' A' as [iiname] のスペースも意図的です
 
カッコの使い方については、うすうす感じてはいましたが
法則性はないのですか 法則性がないと初心者には辛いです
 
さて
 
myTable = _
"(select [result].[rdate], [result].[smpno], [hik].[sname], " & _
"[result].[iname] & ' ' & [hik].[tikind] as iiname, [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'A' or [hik].[tikind] = 'B' " & _
"and [result].[iname] in (select 表示項目 from 項目名)) as [AAA] "
 
でクエリ集計をすると何故か
 
1. clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01")
2. [result].[iname] in (select 表示項目 from 項目名)
 
が効かなくなるため
全rdate 全iiname が表示されます
そこで慣れ始めたunionを使うことにしました
 
結局、こちらも思うように集計できなかったので
現在はAccess内にunionクエリを作成し
そのクエリをレコードソースとして集計しています
こちらは問題なく集計できています
 
当初、新たなフィールド名を設定したことが問題と考えました
そこで新たなフィールド名の代わりに何番目のフィールドと
pivot句に記載しようと考えて質問させていただきました
問題の方向が代わってきてしまい申し訳ありません

回答
投稿日時: 18/04/09 15:59:29
投稿者: sk

引用:
myTable = _
"(select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & ' A' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'A' " & _
"and [result].[iname] in (select 表示項目 from 項目名)) " & _
"union " & _
"(select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & 'B' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'B' " & _
"and [result].[iname] in (select 表示項目 from 項目名)) "

・各 SELECT 文を囲んでいる () が余計。
 
------------------------------------------------------------------------------
myTable = _
"select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & ' A' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'A' " & _
"and [result].[iname] in (select 表示項目 from 項目名) " & _
"union " & _
"select [result].[rdate], [result].[smpno], [hik].[sname], [result].[iname] & ' B' as [iiname], [result].[result] from result " & _
"left join hik on [result].[smpno] = [hik].[smpno] and [result].[rdate] = [hik].[rdate] " & _
"where clng([result].[rdate]) > " & Val(UserForm1.ComboBox1.Value & "01") & " " & _
"and [hik].[tikind] = 'B' " & _
"and [result].[iname] in (select 表示項目 from 項目名)"
 
引用:
mySQL = _
"transform min(val(result)) " & _
"select sname, rdate " & _
"from " & myTable & " " & _
"group by sname, rdate " & _
"pivot [iiname];"

・メインクエリ側の FROM 句のサブクエリ全体を囲む () がない。
 
・FROM 句のサブクエリには出来れば別名を付ける。
 
------------------------------------------------------------------------------
mySQL = _
"transform min(val(qry1.result)) " & _
"select qry1.sname, qry1.rdate " & _
"from (" & myTable & ") qry1 " & _
"group by qry1.sname, qry1.rdate " & _
"pivot qry1.[iiname];"

回答
投稿日時: 18/04/09 16:54:40
投稿者: Suzu

Accessに接続という事は、Accessを使用できる環境はお持ちと推測し回答します。
Acccesのクエリ SQLビューに貼り付ける文字を考えます。
 
select
    [result].[rdate],
    [result].[smpno],
    [hik].[sname],
    [result].[iname] & " " & [hik].[tikind] as iiname,
    [result].[result]
from
    result
        left join
            hik
        on
            [result].[smpno] = [hik].[smpno]
            and
            [result].[rdate] = [hik].[rdate]
where
    clng([result].[rdate]) > Val(UserForm1.ComboBox1.Value & "01")
    and
    [hik].[tikind] = 'A'
    or
    [hik].[tikind] = 'B'
    and
    [result].[iname] in (select 表示項目 from 項目名)
   
こうなります。
ここから、デザインビューに変えましょう。
   
抽出条件、変ですよね?
   
括弧抜けてます。
なので
      (
    clng([result].[rdate]) > Val(UserForm1.ComboBox1.Value & "01")
    and
    [hik].[tikind] = 'A'
      )
    or
      (
    [hik].[tikind] = 'B'
    and
    [result].[iname] in (select 表示項目 from 項目名)
       )
これと同じ扱いになっています。
   
    clng([result].[rdate]) > Val(UserForm1.ComboBox1.Value & "01")
    and
       (
    [hik].[tikind] = 'A' or [hik].[tikind] = 'B'
        )
    and
    [result].[iname] in (select 表示項目 from 項目名)
 
この括弧が必要ですよね。
括弧が無いから、意図した結果にならないのです。
 
[result].[iname] in (select 表示項目 from 項目名) もFROM句に持って行ってしまえば
 
select
    [result].[rdate],
    [result].[smpno],
    [hik].[sname],
    [result].[iname] & " " & [hik].[tikind] as iiname,
    [result].[result]
from
    (
    result
        inner join
            項目名
        on
            [result].[iname] = [項目名].[表示項目]
    )
        left join
            hik
        on
            [result].[smpno] = [hik].[smpno]
            and
            [result].[rdate] = [hik].[rdate]
where
    clng([result].[rdate]) > Val(UserForm1.ComboBox1.Value & "01")
    and
    ([hik].[tikind] = 'A' or [hik].[tikind] = 'B')
 
で実行してみてください。
 
 
いきなりSQLを作ろうとせず、デザインビューで意図する条件となるクエリを作成し、
実行してみて、意図通りの結果が得られる事を確認し、
SQLビューに変更し SQLを得る様にしましょう。

投稿日時: 18/04/10 17:30:30
投稿者: mmiwa

SK様 Suzu様
 
ありがとうございました
非常に勉強になりました
 
SK様のおかげで
unionを使ってSQLの記述のみで集計が出来るようになりました
無闇に()で括ってはダメであること
サブクエリには可能な限り別名をつけること
を肝に銘じたいと思います
 
Suzu様のおかげで
unionを使用しなくてもレコードソースを作ることができることが分かりました
特にinner joinの使い方を教えて頂いたような気がします
これまでデザインビューを毛嫌いしていたので
これからは活用したいと思います
 
本当にありがとうございました