Maximum values

[JonyBravo]

Hi there

If i've got the following fields and values:

Cars - colour - performance
Ford - Red - 100%
Ford - green - 90%
Ford - blue - 95%
Rover - Red - 90%
Rover - green - 97%
Rover - blue - 95%

It's possible to write a query to give me the best performance by each car?

ex
Ford - Red - 100%
Rover - green - 97%

Thanks

 

[DCrake]

Simple Software Solutions

I am assuming that the percentage value is a value and not a string then all you need to do is to create a new query with the following SQL

SELECT fldModelName, Max(fldPercentage) AS Rank
FROM tblModels
GROUP BY fldModelName
ORDER BY Max(fldPercentage) DESC;

Naming conventions do not apply, brevity only

CodeMaster:

 

[JonyBravo]

Thanks to your help DCrake.
And if I have another field called date and I want it also displaying the date with the max(fldPercentage)?

How do I do?

 

[DCrake]

Simple Software Solutions

When grouping records in queries you can select which element to view or which operation to perform. Drag down your date field into the query and look at the options offered to you.

David

 

[JonyBravo]

I want to group only one field and If I try to display the date without grouping it wont run the query.

I need something like:
SELECT fldModelName, Max(fldPercentage) AS Rank, fldDate
FROM tblModels
GROUP BY fldModelName
ORDER BY Max(fldPercentage) DESC;

but it only runs if is like that:
SELECT fldModelName, Max(fldPercentage) AS Rank, fldDate
FROM tblModels, fldDate
GROUP BY fldModelName
ORDER BY Max(fldPercentage) DESC;

but like this it groups the fldDateass well.

 

[DCrake]

Simple Software Solutions

Whilst you are grouping by model name and showing the max rank which date do you want to display along side it?

If you go back to the source table you will have more than one record for each model, a date that coressponds to the model and also a rank % for each record. Do you want to show the last date? the first date? the min date? the max date? Decide that first. However if you want to show the rank % everytime the date changes than you can group my model by date and max the rank %.

David

 

[JonyBravo]

Let supose I have the following records:

Car - color - performance - date

Ford - Red - 100% - 01/01/2008
Ford - green - 90% - 02/01/2008
Ford - blue - 95% - 03/01/2008
Rover - Red - 90% - 04/01/2008
Rover - green - 97% - 05/01/2008
Rover - blue - 95% - 06/01/2008

I need a query to group by car and then see when was the max performance and display the the color and the date related with the max performace.
Example:
Group ford, then find the best performance was 100% and display ford - red - 100% - 01/01/2008
Next go to the next group of the cars and does the same,
Group rover, then find the best performance was 97% and display rover - green - 100% - 05/01/2008.

I know is a bit confused but I hope you understand me.

Thanks to your help

 

[DCrake]

Simple Software Solutions

Gotcha.

You actually need two queries

1. Grouped by Model, Max(Rank)
2. Select Query1 - Table1

See below

SELECT Query1.Model, Table1.Color, Table1.fldDate, Query1.MaxOfRank
FROM Query1 INNER JOIN Table1 ON (Query1.MaxOfRank = Table1.Rank) AND (Query1.Model = Table1.Model);

The syntax is simplified for brevity.

David

 

[JonyBravo]

DCrake,

Perfect soluction. Thanks

 

[DCrake]

Simple Software Solutions

Glad to be of help Thanks for rating the response, not may people bother to do so.

CodeMaster: