query - restrict results to max

[access user]

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.

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
James

 

[Gary Walter]

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.

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

Hi James,

Maybe this is what you want?
(replace "yurtable" w/ real name of table)

SELECT *
FROM yurtable As t
WHERE
t.ContractEndDate >= Date()
AND
t.VarNo =
(SELECT Max(q.VarNo)
FROM yurtable As q
WHERE q.PropertyNo = t.PropertNo)

 

[Jerry Whittle]

SELECT TblJames.PropertyNo,
TblJames.TenantNo,
TblJames.VarNo
FROM TblJames
WHERE (((TblJames.VarNo) In (
SELECT TOP 1 T2.VarNo
FROM tblJames AS T2
WHERE T2.PropertyNo=TblJames.PropertyNo
ORDER BY T2.VarNo DESC))
AND ((TblJames.ContractEndDate)>=Date()))
ORDER BY TblJames.PropertyNo;

Just replace all the tblJames with the correct name.

 

[Marshall Barton]

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.

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

Add criteria to the varno field that uses a subquery like:

(SELECT Max(varno) FROM table As X
WHERE X.propID = table.prodID)