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 · ベストアンサー

#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から見に行く。

となっています。

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

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

なかなか難しいですね。

回答No.6

perorinrin

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

回答No.5

Gin_F

> サブクエリを利用する方法を試してみました。
> ただデータが多いと処理が重くなって止まってしまったりするようです。

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

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

回答No.4

chairwarmer

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

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から取得する事も可能だと思われます。

お礼

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

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

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

回答No.3

Gin_F

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

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

回答No.1

chairwarmer

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

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

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