Removing duplicates from query, but not from table

[tyrfboard]

I've been searching for awhile now on how to remove duplicates from a
table within an Access db and have found plenty of articles on finding
or deleting duplicates. All I want to do is remove them from within an
SQL query - leaving one of the records behind of course.

I have a mailing list comprised of a union query that gets records from
two separate tables.
I want to be able to run a query that removes one (or more) of the
duplicated addresses within the query but doesn't make any
modifications to the table.

Please help!

 

[Guest]

Define "duplicate". Does that mean identical entries in all fields, some
fields, key field?

 

[Tom Ellison]

Dear Don:

Removing exact, total duplicates from a UNION is automatic as long as it's
not UNION ALL. If there's an ALL after UNION, try removing that.

Tom Ellison

 

[tyrfboard]

Tom - thank you for your reply.
It would probably help if I explain what I'm doing --

For example: Lets say I have two tables with names, addresses, etc:
Personal table
Work table

I've got a Union query that merges the fields from Personal and Work
together into one mailing list.

However, there are people who belong on both the personal and work
lists - but I don't want to send mail to those individuals twice.

If each field in these records were exactly the same, then select
distinct would work beautifully. Problem is, the dups might be like
this:

FirstName | LastName | HomeNum | HomeStreet
---------------------------------------------
Jonah | Smith | 1983 | Dovecrest Lane (from work)
Jon | Smith | 1983 | Dovecrest (from personal)

So I would like to have a query that would deselect one of those lines
based on the LastName and HomeNum fields matching up. AND, if
possible, I'd like for the line that is kept to come from the Work
table as that is updated more often.

 

[Randy Harris]

Note to all: this message has been multiposted to at least one other
newsgroup.

 

[Tom Ellison]

Dear Tyrf:

So, you want the computer to recognize that Jon is Jonah, that Dovecrest is
Dovecrest Lane. It will do that if you tell it how. You'll need a table of
equivalent FirstName values, and a table of values to ignore ("Street",
"Avenue", "Road", etc.) The first table could map all equivalent to a
single equivalent value, in effect replacing Jonah and Jon (and Jonathan)
with a single value, perhaps that would be Jon. This doesn't have to
replace all the values in the table, but it would replace the values in the
query, so the equivalence would be recognized, and also so the query would
know which of all the alternatives to report in the results of this query,
that is, which "spelling" to use when producint the list of addresses to use
in addressing this person. You also would have to choose what to show for
the HomeStreet. Potentially, you'd be dropping the "Street", "Avenue",
"Road", or "Lane" from every address you have. And there would no longer be
a distinction between 132nd St and 132nd Ave which are different addresses
where I live. You could, of course, create a replacement function that is
more discriminating.

All this you would do with your own labor. I'm sorry if you have imagined
that a computer, or the software it runs, would be intuitive, or well
informed about the equivalence of "Jon" with "Jonah", but the state of the
art isn't close to being able to do this for you. But, there's nothing
keeping you from doing it for yourself.

Don't think I'm not sympathetic. It's just that there's a gulf between
human intelligence and computer operations. Intuition, estimation, and
associations are not the strong points of the computer. Speed and capacity
are. You have to take what you can get. The rest is up to you!

Tom Ellison