Access2000のクエリ、教えてください。

データが

名前NO大学
--------------------------------
田中8北海道大学
すずき1大宮大学
田中3青森大学
田中2千葉大学
すずき3湘南大学
すずき4山梨大学
すずき2東京大学

で、検索条件、名前の最大NO　で　名前、 NO、大学　をリストアップ
したい。

●ほしい結果
すずき4山梨大学
田中8北海道大学

ですが、

クエリーのSQLです。
---------------------------
SELECT テーブル1.名前, Max(テーブル1.[No]) AS Noの最大, Max(テーブル1.大学) AS 大学の最大
FROM テーブル1
GROUP BY テーブル1.名前;

●現在の結果
すずき4東京大学
田中8北海道大学


となってしまいます。
SQLをどのように変えればいいのでしょうか？

アドバイスをお待ちしています。

No.2 ベストアンサー 回答者： chairwarmer 回答日時： 2004/09/29 15:33

#1です。

ごめんなさい、さっきのＳＱＬでは正しく取得できませんね（＾＾；

SELECT テーブル1.名前, Max(テーブル1.[No]) AS No の最大,
DLookup("大学", "テーブル1", "[No] = " & CStr(Max(テーブル1.[No])) & " AND 名前 = '" & テーブル1.名前 & "'") AS 大学の最大
FROM テーブル1
GROUP BY テーブル1.名前

でやってみてください。

この回答への補足

ありがとうございます。
Dlookup を使って上手くいきました。

フィールド、テーブル数、が多いデータで実行するとかなりSQLが複雑になってしまいますね。

テストデータ
Access

実際のデータ
オラクルをAccessから見に行く。

となっています。

テーブル５つ、データ数約７０００だと構文が複雑になりすぎてエラーになってしまいます。

データベースの構造を工夫したほうがいいのかなと
思ってます。

なかなか難しいですね。

補足日時：2004/09/29 17:36

No.6 回答者： perorinrin 回答日時： 2004/10/02 00:40

SELECT 名前,No,大学 FROM テーブル1 WHERE No IN (SELECT Max([No]) AS Noの最大

FROM テーブル1 GROUP BY 名前)

No.5 回答者： Gin_F 回答日時： 2004/09/30 13:36

> サブクエリを利用する方法を試してみました。

> ただデータが多いと処理が重くなって止まってしまったりするようです。

たいして、速度的に差はないような気がしますが、以下では？

SELECT 名前, [NO], 大学 FROM FROM テーブル1
WHERE ((テーブル1.[NO])=DMax("[NO]","テーブル1","[名前]='" & [名前] & "'")

No.4 回答者： chairwarmer 回答日時： 2004/09/29 19:10

クエリを複数作っても構わないようでしたら

SELECT テーブル1.名前, Max(テーブル1.[No]) AS MaxNo
FROM テーブル1
GROUP BY テーブル1.名前

まずこのように名前とMaxのNoだけを取得するSQLを作って
「クエリ1」の名前で保存し

SELECT テーブル1.*
FROM テーブル1 INNER JOIN クエリ1
ON (テーブル1.名前 = クエリ1.名前)
AND (テーブル1.[No] = クエリ1.MaxNo)

このようにテーブル1と等結合して
クエリ1で取得された名前とMaxNoに一致するレコードのみを
テーブル1から取得する事も可能だと思われます。

この回答へのお礼

アドバイスありがとうございました。

サブクエリを利用する方法を試してみました。
上手く検索処理が出来ました。

ただデータが多いと処理が重くなって止まってしまったりするようです。
まあしょうがないですかね。

お礼日時：2004/09/30 12:34

No.3 回答者： Gin_F 回答日時： 2004/09/29 16:06

SELECT テーブル1.*

FROM テーブル1
WHERE (((テーブル1.[NO])=(select max([NO]) from テーブル1 as T where テーブル1.[名前]=T.[名前] group by [名前])));

こちらもお試しください。

No.1 回答者： chairwarmer 回答日時： 2004/09/29 15:26

Max(テーブル1.大学) の記述では

「最大のNoに対応した大学名」ではなく
「大学名の中で最大のもの」を取得する形になってしまいます。
（おそらくは、大学名を音読み・50音順でならべた場合に最後尾となるものが取得されます）

SELECT テーブル1.名前, Max(テーブル1.[No]) AS No の最大,
DLookup("大学", "テーブル1", "[No] = " & CStr(Max(テーブル1.[No]))) AS 大学の最大
FROM テーブル1
GROUP BY テーブル1.名前

としてみてはいかがでしょうか。