query help - retrieve only those with highest value

[Guest]

I have a query and I wish to restrict the returned values to only those
where, for a particular field, the value is the highest.

For example, a property can have had a number of rent agreements but, in the
following example, I don't want ALL the records, just the one where VarNo is
the highest

PropertyNo TenantNo VarNo
1 1 1
1 1 2
1 2 3

I *only* want the last line not all three lines.

TIA
James

 

[Guest]

Also, just to clarify this is an *additional* constraint to one I already
have in the query. That is, I want only those results where *both* conditions
are met.
Firstly, where ContractEndDate>=Date()
Secondly, where (having met the above condition), VarNo is the highest

TIA

 

[Guest]

Hi.

Assuming the name of your table is "TableName", try this:

SELECT TableName.PropertyNo, TableName.TenantNo, TableName.VarNo
FROM TableName
WHERE (TableName.VarNo)=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
(TableName.ContractEndDate)>=Date());

-Michael

 

[Guest]

In your query you could try putting VarNo field in Descending order and then
right click - properties and where it says 'All' type in '1'. This will
return the top value (one record) in the VarNo field.

 

[MNewsGroup]

123456

Also, just to clarify this is an *additional* constraint to one I already
have in the query. That is, I want only those results where *both*
conditions
are met.
Firstly, where ContractEndDate>=Date()
Secondly, where (having met the above condition), VarNo is the highest

TIA

 

[Guest]

Thanks - not sure where to put the 1. Bringing up field properties does not
give me any such option.
James

 

[Guest]

Thanks - I get a syntax error on the following bit:

=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE

missing ) , ] etc...

James

 

[Guest]

Did you paste the entire bit of SQL I posted? If your SQL ends at the word
"WHERE", then you did not. Please ensure thay you copy and paste everything
from the first SELECT statement to the semicolon at the end. I have tested
it and it works just as you had specified it should.

If you have done the above and are still having problems, please post the
exact SQL you are using, as something may have gotten changed in the
transition.

-Michael

Thanks - I get a syntax error on the following bit:

=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE

missing ) , ] etc...

James

Hi.

Assuming the name of your table is "TableName", try this:

SELECT TableName.PropertyNo, TableName.TenantNo, TableName.VarNo
FROM TableName
WHERE (TableName.VarNo)=(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
(TableName.ContractEndDate)>=Date());

-Michael