Find highest value

[brback via AccessMonster.com]

Say i have query with these entries

Identficator | Value |
-------------------------------
1 66
1 90
1 85


What i want is another field to display the highest Value when Identficator
is set as 1
Meaning i want a field to display the number 90

 

[fredg]

Say i have query with these entries

Identficator | Value |
-------------------------------
1 66
1 90
1 85

What i want is another field to display the highest Value when Identficator
is set as 1
Meaning i want a field to display the number 90

SELECT TableName.Identifier, Max(TableName.AValue) AS MaxOfAValue
FROM TableName
GROUP BY TableName.Identifier;

I hope you do not have a field named "Value" in your database.
Value is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[brback via AccessMonster.com]

Say i have query with these entries

[quoted text clipped - 7 lines]

is set as 1
Meaning i want a field to display the number 90

SELECT TableName.Identifier, Max(TableName.AValue) AS MaxOfAValue
FROM TableName
GROUP BY TableName.Identifier;

I hope you do not have a field named "Value" in your database.
Value is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Hi, and thank you for your answer. I can't get this to work, probably cause i
simplified my problem too much so i'll try give it another go.

I got 2 tables, "Contract" and "Car"

Contract
--------------------------------------
ContractID (Primary Key)
CarNR
Km_out
Km_in

Car
--------------------------------------
CarNR (Primary Key)
Model
Year

Having these tables I make a new query named "Rental"

Rental
--------------------------------------
ContractID (From Contract)
CarNR (From Contract)
Model (From Car)
Year (From Car)
Km_out (From Contract)
Km_In (From Contract)

With this query i make a new form "Rent". Km_out represents the distance
meter on my car when i rent it out, "Km_In" represents the display on my
distance meter when i get the car back. Meaning when ive chosen "CarNR", i
want "Km_out" to display the largest value of "Km_in" from the same "CarNR"

Thanks alot in advance for any help

 

[John Spencer]

SELECT C.ContractID, C.CarNr, Car.Model, Car.[year],Km_out, Km_In
FROM Contract as C INNER JOIN Car
ON C.CarNo = Car.CarNr
WHERE C.KM_OUT = (
SELECT MAX(Km_Out)
FROM Contract
WHERE Contract.ContractID = C.ContractID)
AND C.CarNr = [Some Car number goes here]

You can also

Say i have query with these entries

[quoted text clipped - 7 lines]

is set as 1
Meaning i want a field to display the number 90

SELECT TableName.Identifier, Max(TableName.AValue) AS MaxOfAValue
FROM TableName
GROUP BY TableName.Identifier;

I hope you do not have a field named "Value" in your database.
Value is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Hi, and thank you for your answer. I can't get this to work, probably cause i
simplified my problem too much so i'll try give it another go.

I got 2 tables, "Contract" and "Car"

Contract
--------------------------------------
ContractID (Primary Key)
CarNR
Km_out
Km_in

Car
--------------------------------------
CarNR (Primary Key)
Model
Year

Having these tables I make a new query named "Rental"

Rental
--------------------------------------
ContractID (From Contract)
CarNR (From Contract)
Model (From Car)
Year (From Car)
Km_out (From Contract)
Km_In (From Contract)

With this query i make a new form "Rent". Km_out represents the distance
meter on my car when i rent it out, "Km_In" represents the display on my
distance meter when i get the car back. Meaning when ive chosen "CarNR", i
want "Km_out" to display the largest value of "Km_in" from the same "CarNR"

Thanks alot in advance for any help

 

[brback via AccessMonster.com]

SELECT C.ContractID, C.CarNr, Car.Model, Car.[year],Km_out, Km_In
FROM Contract as C INNER JOIN Car
ON C.CarNo = Car.CarNr
WHERE C.KM_OUT = (
SELECT MAX(Km_Out)
FROM Contract
WHERE Contract.ContractID = C.ContractID)
AND C.CarNr = [Some Car number goes here]

You can also
[quoted text clipped - 52 lines]

Thanks alot in advance for any help

This is a pretty silly question, but where am i suppose to store the SQL ?

 

[brback via AccessMonster.com]

SELECT C.ContractID, C.CarNr, Car.Model, Car.[year],Km_out, Km_In
FROM Contract as C INNER JOIN Car

[quoted text clipped - 12 lines]
This is a pretty silly question, but where am i suppose to store the SQL ?

Nevermind, thank you for all help