get maximum values from querie

[bregci]

Hello
I have a problem. Can anybody help me please?

I have a querie from where I like to get only a latest value from individual id_machin.
It is look like that.

id_field | id_machin | date | counter |
-------------------------------------
.....1.............1.....18.3.04....1000
.....2.............1.....15.4.04....3000
.....3.............2.....18.4.04....1500
.....4.............3.....18.4.04....1200
.....5.............1.....30.5.04....5500
.....6.............3.....14.6.04....3400
.....7.............2.....16.6.04....3100

and I like to be like that

id_field | id_machin | date | counter |
-------------------------------------
.....5.............1.....30.5.04....5500
.....7.............2.....16.6.04....3100
.....6.............3.....14.6.04....3400


Best regards

 

[Pat Hartman]

Use two queries.

query1:
Select id_machin, Max(counter) as MaxCount
From YourTable
Group by id_machin;

query2:
Select * from YourTable
Inner Join query1 on YourTable.id_machin = query1.id_machin AND YourTable.Count = query1.MaxCount;

BTW, date is inappropriate as a column name since it is the name of a function and its use can cause problems in VBA.

 

[bregci]

It's working.

Thank you.