Query Help

[Rhino373]

I'm having a time getting what I want from a query. I have a table that looks like this:



Table1ID Table2ID Matched
1111 2222 1
1111 3333 2
1111 4444 3
1111 5555 1
2222 3333 6
3333 2222 1
3333 8888 4

I want each Table1ID and the associated Table2ID where Matched is the greatest. So in the example above I only want to return 3 records. They would be:

1111 4444 3
2222 3333 6
3333 8888 4

I'm comparing 2 tables over 6 different criteria. The matched column tells over how many criteria they matched. So it's impossible for these records to match exactly over all criteria but if they match enough times its good enough.

Can someone point me in the right direction for this query?

Thanks

[pbaldy]

Sounds like this concept will work:

http://www.baldyweb.com/LastValue.htm

[Rhino373]

Thanks, exactly what I need

[pbaldy]

Happy to help!