Same ID different Groups and milestone

[DaRTHY]

hello my friends,

i have a query and there is ID Groups and Milestone. I need to somehow compare them.
(please Check the picture.)

On this example i have 2 different ID (1 and 2) and these IDs have many Groups.

PS: Milestone : C > B > A

i need to compare with same ID Data Records but different Groups :

For ID-1 i wanna see Groups 12,22,32,31,312,62 because Milestone C is bigger than B and A

And For ID-2 I wanna see Group 24,25,78 and All because there is not Milestone C and second biggest is B

How can i do these Compare :

Logical i shall write this :

if ID is same
compare milestones and find there biggest one
(i need to describe milestones.)
and write Group and ID and biggest Milestone....

but i dont know how can i write it with code....:banghead:

many thanks for your helps.

 

[JHB]

Use 2 queries, the first find the max milestone for each ID's, the second one find all the groups.
First query, call it "QueryMaxMilestone", remember to change the table name to what you call it.

SELECT ID, Max(Milestone) AS MaxOfMilestone
FROM [B][COLOR=Red]tblMilestone[/COLOR][/B]
GROUP BY ID;

Second query:

SELECT [B][COLOR=Red]tblMilestone[/COLOR][/B].*
FROM QueryMaxMilestone INNER JOIN [B][COLOR=Red]tblMilestone[/COLOR][/B] ON (QueryMaxMilestone.MaxOfMilestone = [B][COLOR=Red]tblMilestone.[/COLOR][/B]Milestone) AND (QueryMaxMilestone.ID =[B][COLOR=Red] tblMilestone[/COLOR][/B].ID);

 

[arnelgp]

do you want only to group by id and milestone:

SELECT Table1.ID, Table1.GROUPS, Table1.MILESTONE
FROM Table1
ORDER BY [ID] & IIf(Trim([MILESTONE] & "")="","99",IIf([MILESTONE]="A","33",IIf([MILESTONE]="B","22","11")));

replace table1 on the above statement with proper table name.

 

[DaRTHY]

Thank you very much gentlemen
It was more sophisticated but i did it.
Just need to create 1 more table and 2 extra query