Dupe & distinct (Tom Ellison)

[Razor]

Hi,

I want to filter ONE table for records that have dupes on
one field AND distincts on another field (in the same
table).

Can someone suggest the SQL?

Tom Ellison had made a suggestion in my previous post but
that assumed I was querying two tables.

Thanks!
Razor

 

[Michel Walsh]

Hi,


With Jet, bring all the fields in the grid of a Total query. Keep the
GROUP BY for those fields you want no duplicated values (all the "grouped
fields considered"), and use LAST for the other fields, rather than the
default GROUP BY that is proposed.


ex.:


FirstName, LastName, ProblemReported, DateOfTheReport ' fields name
Joe Blow PC is not working 2001-01-01
Joe Blow Speaker not working 2002-01-01
Mary Blow PC is not working 2002-01-01
Mary Blow Speaker not working 2001-01-01


and you want Each pair of FirstName and LastName (GROUP BY on those two) and
also a problem associated with that group, and the date it occurs (use LAST
on the last two fields). That would pick one of the first two records (and
not a mixture of the two first records) and one of the last two records, as
the end result...



Hoping it may help,
Vanderghast, Access MVP