SELECT, FROM, WHERE using Max() ?!?

[Steff_DK]

Table: Adresseliste:
adres prefix
1 one..
2 two..
3 three..
4 ...

Table: Tabel1:
adres status
1 45
1 34
1 3
1 16
2 4
2 78
..

I use this query:


SELECT Adresseliste.adres, Adresseliste.prefix, Tabel1.status
FROM Adresseliste INNER JOIN Tabel1 ON Adresseliste.adres=Tabel1.adres
WHERE [status] = (SELECT Max([status]) FROM Tabel1 WHERE [status] Is Not Null);

I get:

adres prefix status
1 one.. 45

I Want:

adres prefix status
1 one.. 45
2 two.. 78
..

I want each item in the table "adresseliste" presented once with only the highest "status" from the table "Tabel1"

Help!

 

[Jon K]

In some versions of Access, you can do it with one query:

SELECT Adresseliste.adres, Adresseliste.prefix, S.Status
FROM Adresseliste INNER JOIN (SELECT Tabel1.adres, Max(Tabel1.status) AS Status
FROM Tabel1
GROUP BY Tabel1.adres) AS S ON Adresseliste.adres = S.adres;


In other versions, you will need two:

qryOne:-
SELECT Tabel1.adres, Max(Tabel1.status) AS Status
FROM Tabel1
GROUP BY Tabel1.adres;

qryTwo:-
SELECT Adresseliste.adres, Adresseliste.prefix, qryOne.status
FROM Adresseliste INNER JOIN qryOne ON Adresseliste.adres=qryOne.adres;


Run the second query.

This two-query approach should work in all versions of Access.
.

 

[Louie]

I shortened your table names to a1, and t1 just to save typing but try the following:

SELECT a1.adres, a1.prefix, t1.status
FROM a1 INNER JOIN t1 ON a1.adres=t1.adres
WHERE [status] = (SELECT Max(Status) FROM t1 Where (t1.adres=a1.adres) GROUP BY t1.adres

Lou ...

 

[EMP]

Interesting variation of solutions

In some versions of Access, you can do it with one query:

SELECT Adresseliste.adres, Adresseliste.prefix, s.Status
FROM Adresseliste INNER JOIN (SELECT Tabel1.adres, Max(Tabel1.status) AS Status
FROM Tabel1
GROUP BY Tabel1.adres) AS S ON Adresseliste.adres = S.adres;

I can run it in A2K and A2003, but not in A97. When the query is saved, it becomes:

SELECT Adresseliste.adres, Adresseliste.prefix, s.Status
FROM Adresseliste INNER JOIN [SELECT Tabel1.adres, Max(Tabel1.status) AS Status
FROM Tabel1
GROUP BY Tabel1.adres]. AS S ON Adresseliste.adres = S.adres;

.

SELECT a1.adres, a1.prefix, t1.status
FROM a1 INNER JOIN t1 ON a1.adres=t1.adres
WHERE [status] = (SELECT Max(Status) FROM t1 Where (t1.adres=a1.adres) GROUP BY t1.adres);

Works so long as there is no tie in the Max(Status) of any adres. For example,
adres status
1 45
1 34
1 45
1 16

will return more than one record for adres 1.