query help - retrieve only those with highest value

I have a query and I wish to restrict the returned values to only those
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9869-query-retrieve-only-those-highest-value.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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()
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
Secondly, where (having met the above condition), VarNo is the highest

TIA

"access user" wrote:

> I have a query and I wish to restrict the returned values to only those
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> 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

123456
"access user" wrote in message
news:FBCDD485-AAFA-4E68-9E52-11919E664093@microsoft.com...
> 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
>
> "access user" wrote:
>
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
>> 1 2 3
>>
>> I *only* want the last line not all three lines.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
>>
>> TIA
>> 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


"access user" wrote:

> 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
>
> "access user" wrote:
>
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> > 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

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.
--
Gina


"access user" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> 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

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

"Gina" wrote:

> In your query you could try putting VarNo field in Descending order and then
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> right click - properties and where it says 'All' type in '1'. This will
> return the top value (one record) in the VarNo field.
> --
> Gina
>
>
> "access user" wrote:
>
> > I have a query and I wish to restrict the returned values to only those
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> > 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

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

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

missing ) , ] etc...

James


Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
"Michael H" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
> (TableName.ContractEndDate)>=Date());
>
> -Michael
>
>
> "access user" wrote:
>
> > 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
> >
> > "access user" wrote:
> >
> > > 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

Did you paste the entire bit of SQL I posted? If your SQL ends at the word
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869
"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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9869

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


"access user" wrote:

> Thanks - I get a syntax error on the following bit:
>
> =(SELECT Max(VarNo) AS MaxVarNo FROM TableName WHERE
>
> missing ) , ] etc...
>
> James
>
>
> "Michael H" wrote:
>
> > 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
> >
> >
> > "access user" wrote:
> >
> > > 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
> > >
> > > "access user" wrote:
> > >
> > > > 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