下記の事を行うSQLがわかりません。

質問

下記の事を行うSQLがわかりません。
テーブルＡに名前の列と評価の列があります。
データは過去10年間の月毎の評価（１～５）です。
名前列には150人の登録者の名前が10年間の月の数だけ入っています。抽出したいのは評価が5を取っていない者の氏名です。

例
---------------------
一朗　2
次郎 3
一朗　1
次郎 4

環境はwindows2000,Access2000です。
ＳＱＬ始めたばかりなのでご指導おねがいします。

回答No.3

galoon · ベストアンサー

ANo.1です。
かなり複雑になってしまいますが、条件は最後のwhereの部分に記述してください。
評価1～評価5はその評価をとった回数がカウントされていますので、とったことがあるは０より大( >0 ）、とったことがないは０（ =0 ）になります。
内容に関するご質問は、また補足などでいただければ回答します。

SELECT 名前
FROM (SELECT 名前,sum(価1) as 評価1,sum(価2) as 評価2,sum(価3) as 評価3,sum(価4) as 評価4,sum(価5) as 評価5
FROM (SELECT 名前,1 as 価1, 0 as 価2, 0 as 価3, 0 as 価4, 0 as 価5 FROM テーブル1 where 評価 = 1
UNION SELECT 名前,0 as 価1, 1 as 価2, 0 as 価3, 0 as 価4, 0 as 価5 FROM テーブル1 where 評価 = 2
UNION SELECT 名前,0 as 価1, 0 as 価2, 1 as 価3, 0 as 価4, 0 as 価5 FROM テーブル1 where 評価 = 3
UNION SELECT 名前,0 as 価1, 0 as 価2, 0 as 価3, 1 as 価4, 0 as 価5 FROM テーブル1 where 評価 = 4
UNION SELECT 名前,0 as 価1, 0 as 価2, 0 as 価3, 0 as 価4, 1 as 価5 FROM テーブル1 where 評価 = 5) as A
GROUP BY 名前) AS B
WHERE 評価5 > 0
and 評価4 = 0;

お礼

ありがとうございました。
成功しました。
２日考えていたので大変助かりました。
内容についてはいまから勉強します。

補足

なんどもすみません。
名前列と評価列以外にも複数列があるのですが名前と一緒にその列の項目も表示させたい場合最初のSELECTの後の名前のところを*にすればいいのでしょうか。

回答No.10

noname#15703

No.8の続きです。
　といっても私はVBAは判らないのですが（苦笑）。
　ダブルクオーテーションを２個づつにしてもだめですか？
WHERE (((DCount(""*"",""[テーブルA]"",""[名前]='"" & [名前] & ""' AND [評価]="" & 4))=0)) OR (((DCount(""*"",""[テーブルA]"",""[名前]='"" & [名前] & ""' AND [評価]="" & 5))=0));
てな具合に。
　すみません、ちょっと判りません。

お礼

いろんなパターン試してみます。
ありがとうございました。

回答No.9

galoon

ANo.1,3,4,5です。

SQL文ひとつですむように修正してみました。
条件は文の後ろの方のMAX(A.E5)の部分です。
MAX(A.E1)、MAX(A.E2)、MAX(A.E3)、MAX(A.E4)、MAX(A.E5) は、0か1という値を返し、0は該当する評価を受けたことが「ない」、1は該当する評価を受けたことが「ある」ということをしめします。
後ろの数字が評価の数値を示しています。
下の文では、評価５を取得したことはないが、評価４を取得したことがあることを示します。

SELECT
DISTINCT
TABLEA.名前
, TABLEA.住所
FROM
TABLEA INNER JOIN (
SELECT
A.名前
FROM
(
SELECT
名前
, 1 AS E1
, 0 AS E2
, 0 AS E3
, 0 AS E4
, 0 AS E5
FROM
TABLEA
WHERE
評価 = 1
UNION
SELECT
名前
, 0
, 1
, 0
, 0
, 0
FROM
TABLEA
WHERE
評価 = 2
UNION
SELECT
名前
, 0
, 0
, 1
, 0
, 0
FROM
TABLEA
WHERE
評価 = 3
UNION
SELECT
名前
, 0
, 0
, 0
, 1
, 0
FROM
TABLEA
WHERE
評価 = 4
UNION
SELECT
名前
, 0
, 0
, 0
, 0
, 1
FROM
TABLEA
WHERE
評価 = 5 ) AS A
GROUP BY
A.名前
HAVING
MAX ( A.E5 ) = 0
AND MAX ( A.E4 ) = 1 ) AS B
ON TABLEA.名前 = B.名前 ;

これなら、そのままＶＢＡでも使えると思うのですが、いかがでしょうか。
でも、ANo.8のkitasueさんの回答の方がスリムでいいですね。(^^;

回答No.8

noname#15703

パフォーマンスがかなり落ちますので実用になるか判りませんが、次のクエリならば一発です。

SELECT テーブルＡ.*
FROM テーブルＡ
WHERE (((DCount("*","[テーブルA]","[名前]='" & [名前] & "' AND [評価]=" & 4))=0)) OR (((DCount("*","[テーブルA]","[名前]='" & [名前] & "' AND [評価]=" & 5))=0));

補足

VBAでSELECT分を文字変数に入れてから発行しているのですが"[テーブルA]"のダブルコーテーションで文法エラーになるのですが。

回答No.7

noname#15703

No.6の続きです。
　すみません、私はVBAのことは、ほとんど解りません。
　ただ、素人考えですが、クロス集計の部分はVBAで書かなくてもよいのではないでしょうか。
　そのままクエリとして保存しておき、VBAで書くのなら２つめの集計クエリの箇所を書けばよいのでは？

お礼

クロス集計はたしかに結果がでました。
ただＶＢＡでQ_名前_評価を指定するとエラーになるので
クエリの指定のしかたがあるのかもしれません。
調べてみます。

回答No.6

noname#15703

横レス失礼します。
　Access2000をお使いでしたらクロス集計クエリを利用されてはいかがでしょうか。
　galoonさんのアイデアをお借りして、次のようなクロス集計クエリを考えました。クエリのＳＱＬビューでペーストしたら、デザインビューに戻して構いません。

TRANSFORM Count(テーブルＡ.評価) AS 評価のカウント
SELECT テーブルＡ.名前
FROM テーブルＡ
GROUP BY テーブルＡ.名前
PIVOT テーブルＡ.評価;

　これを「Q_名前_評価」という名前で保存します。
　次に、以下の内容を、また新たなクエリとして登録します。これもＳＱＬビューでペーストしたら、デザインビューに戻して構いません。

SELECT テーブルＡ.*
FROM テーブルＡ INNER JOIN Q_名前_評価 ON テーブルＡ.名前 = Q_名前_評価.名前
WHERE (((Q_名前_評価.[4]) Is Null)) OR (((Q_名前_評価.[5]) Is Null));

　このクエリを開くと、ご所望のリストになりませんか？

補足

ご回答ありがとうございます。
説明不足だったのですが開発をすべてＶＢＡで行っているのですが

TRANSFORM Count(テーブルＡ.評価) AS 評価のカウント
SELECT テーブルＡ.名前
FROM テーブルＡ
GROUP BY テーブルＡ.名前
PIVOT テーブルＡ.評価;

の部分はどう書いたらいいのでしょうか。

回答No.5

galoon

ANo.1,3,4です。

ざっと内容を見てみました。
これは、SQL文を入力したと考えていいのでしょうか？
そうであれば、次の点を確認してみてください。
・後半部分にSTATUS=3 という部分がありますね。これは、ST=3のミスでは？
・「ON TABLE.NAME = SELECT」の部分を「ON TABLE.NAME = (SELECT」とし、最後の「価2 = 0.NAME」を「価2 = 0).NAME」としてみてください。

もしかして、クエリとかを組み合わせない方がよかったんでしょうか・・・。

ちょっと複雑なクエリ文になってしまってわかりにくかったですね、申し訳ないです。

回答No.4

galoon

ANo.1,3です。
ちょっとクエリの数が増えますが次の方法が簡単だとおもいます。

(1)先のSQLをクエリとして保存する。（「クエリ１」とする。）
(2)新しいクエリを次の内容で作成する。（「クエリ２」とする。）

SELECT テーブルＡ.名前, {表示したい列名を列挙} FROM テーブルＡ INNER JOIN クエリ１ ON テーブルＡ.名前 = クエリ１.名前

(3)クエリ２を開く。

いかがでしょうか。

お礼

やはりFORM句のエラーになります。
あまりお時間とらせるのは悪いので
とりあえずテーブル２を追加しテーブル２にクエリー１の結果を保存しテーブル１とテーブル２でマッチングさせました。
ありがとうございました。
何か良い参考書、ＵＲＬ等ありましたらお教えください．

補足

FORM句のエラーになります。
デバッグすると
?cmd.CommandText
SELECT TABLE.NAME,TABLE.ADRESS FROM TABLE INNER JOIN SELECT NAME
FROM (SELECT NAME,sum(価1) as 評価1,sum(価2) as 評価2,sum(価3) as 評価3,sum(価4)
as 評価4 FROM (SELECT NAME,1 as 価1, 0 as 価2, 0 as 価3, 0 as 価4 FROM TABLE
where ST = 1 UNION SELECT NAME,0 as 価1, 1 as 価2, 0 as 価3, 0 as 価4 FROM
TABLE where ST = 2 UNION SELECT NAME,0 as 価1, 0 as 価2, 1 as 価3, 0 as
価4 FROM TABLE where ST = 3 UNION SELECT NAME,0 as 価1, 0 as 価2, 0 as
価3, 1 as 価4 FROM TABLE where ST = 4) as A GROUP BY NAME) AS B WHERE
評価2 = 0 ON TABLE.NAME = SELECT NAME FROM (SELECT NAME,sum(価1)
as 評価1,sum(価2) as 評価2,sum(価3) as 評価3,sum(価4) as 評価4 FROM (SELECT NAME,1
as 価1, 0 as 価2, 0 as 価3, 0 as 価4 FROM TABLE where ST = 1 UNION SELECT NAME,0
as 価1, 1 as 価2, 0 as 価3, 0 as 価4 FROM TABLE where ST = 2 UNION SELECT NAME,0
as 価1, 0 as 価2, 1 as 価3, 0 as 価4 FROM TABLE where STATU
S = 3 UNION SELECT NAME,0 as 価1, 0 as 価2, 0 as 価3, 1 as 価4 FROM TABLE where ST = 4)
as A GROUP BY NAME) AS B WHERE 評価2 = 0.NAME

私の理解の範囲を超えているのでなにがおかしいのかわかりません。

回答No.2

minato_air

select 名前 , 評価 from テーブルA where (テーブルA.評価 ＜ 5)

でいけませんか？

回答No.1

galoon

SELECT 名前 FROM テーブルＡ GROUP BY 名前 HAVING MAX(評価) < 5

でいかがでしょう？

補足

早々の回答ありがとうございました。
私の説明がたりませんでしたが５を取っていない人はこれでいいのですが、５はあるけど４を取っていない人も検索しないといけないのですがその場合
テーブルA.評価 ＜ 4 AND テーブルA.評価 >4 とかできるのでしょうか