select query statement (group by) function

[uji_amira]

I have several records to search for. I need to select just 1 record for each Tkt_no group using Tkt_id (max no).
Example, I only want to select

Tkt_No Tkt_ID Ticket_Problem Ticket_Status Ticket_Finish_Time
1 14 problem 1 Done
2 10 problem 2 Done 00/01/1900 20:00
3 2 problem 3 Ongoing 00/01/1900 20:00
4 3 problem 4 Done 00/01/1900 20:00
7 11 problem 5 Done 21/05/2007 9:15
9 13 problem 6 Done 22/05/2007 0:00

from below data

Tkt_No Tkt_ID Ticket_Problem Ticket_Status Ticket_Finish_Time
1 4 problem 1 Ongoing 00/01/1900 20:00
1 14 problem 1 Done
2 5 problem 2 Ongoing 00/01/1900 14:30
2 6 problem 2 Ongoing 00/01/1900 11:30
2 7 problem 2 Ongoing 00/01/1900 10:15
2 8 problem 2 Ongoing 00/01/1900 15:00
2 9 problem 2 Ongoing 00/01/1900 8:00
2 10 problem 2 Done 00/01/1900 20:00
3 1 problem 3 Ongoing 00/01/1900 20:00
3 2 problem 3 Ongoing 00/01/1900 20:00
4 3 problem 4 Done 00/01/1900 20:00
7 11 problem 5 Done 21/05/2007 9:15
9 13 problem 6 Done 22/05/2007 0:00

Any ideas?
Appreciate the feedbacks.

 

[chergh]

Try this

SELECT table_name.Tkt_No, Max(table_name.Tkt_ID) AS MaxOfTkt_ID, table_name.Ticket_Problem, table_name.Ticket_Status, table_name.Ticket_Finish_Time
FROM table_name
GROUP BY table_name.Tkt_No, table_name.Ticket_Problem, table_name.Ticket_Status, table_name.Ticket_Finish_Time;

 

[Dennisk]

Turn your query into a totals query (View|Totals) then in your query grid you can select group by, first last, min, max etc. What you require is the max of your Ticket Number grouped by problem.

 

[uji_amira]

query - group by

thanks for the feedback. I hv already tried the statement above before I posted, but it will still gives duplicate records for same tkt_no (problem) if there are any difference in any of the above fields - status, ticket_finish_time.

Any ideas how to just select unique record for each tkn_no value with max tkt_id ? And the duplicate records are combination with 2 tables with each table having primary key of tkt_no and tkt_id.

 

[neileg]

If any of the fields are different, then the records aren't true duplicates. Leave those fields out of your query.